[R-sig-Geo] Maintain SRID with st_write to postgis db
Michael Treglia
mtreglia at gmail.com
Thu Mar 16 00:53:58 CET 2017
Sorry - this follow-up isn't entirely an R question, so if best to take
this off list, let me know:
Installing the dev version from github fails for me (installing on ubuntu
14.04.5) - I've included the output from the install process below - seems
to fail due to failed check for liblwgeom version.
Looks like I don't have liblwgeom installed - and when I try (via sudo
apt-get install liblwgeom-2.3-0), it indicates it requires libgeos-c1.
Installing libgeos-c1, however, requires removal of a newer version of
libgeos (libgeos-c1v5), which other FOSS GIS tools depend on (at least if
my understanding is correct). Is there a way around this? Sorry if I'm
just missing something, and thanks again for input.
mike
#Output of install from github
> install_github("edzer/sfr")
Downloading GitHub repo edzer/sfr at master
from URL https://api.github.com/repos/edzer/sfr/zipball/master
Installing sf
'/usr/lib/R/bin/R' --no-site-file --no-environ --no-save --no-restore
--quiet \
CMD INSTALL '/tmp/RtmpKLmhyt/devtools9fd11eb9c3/edzer-sfr-d620f3b' \
--library='/home/mlt244/R/x86_64-pc-linux-gnu-library/3.3'
--install-tests
* installing *source* package ‘sf’ ...
configure: CC: gcc -std=gnu99
configure: CXX: g++
configure: :
checking for gdal-config... /usr/bin/gdal-config
checking gdal-config usability... yes
configure: GDAL: 2.1.0
checking GDAL version >= 2.0.0... yes
checking for gcc... gcc -std=gnu99
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc -std=gnu99 accepts -g... yes
checking for gcc -std=gnu99 option to accept ISO C89... none needed
checking how to run the C preprocessor... gcc -std=gnu99 -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking gdal.h usability... yes
checking gdal.h presence... yes
checking for gdal.h... yes
checking GDAL: linking with --libs only... yes
checking GDAL: /usr/share/gdal/2.1/pcs.csv readable... yes
checking GDAL: checking whether PROJ.4 is available for linking:... yes
checking GDAL: checking whether PROJ.4 is available fur running:... yes
checking proj_api.h usability... yes
checking proj_api.h presence... yes
checking for proj_api.h... yes
checking for pj_init_plus in -lproj... yes
checking PROJ.4: epsg found and readable... yes
proj_conf_test.c: In function 'main':
proj_conf_test.c:5:5: error: unknown type name 'PAFile'
PAFile fp;
^
proj_conf_test.c:8:5: warning: implicit declaration of function
'pj_open_lib' [-Wimplicit-function-declaration]
fp = pj_open_lib(ctx, "conus", "rb");
^
proj_conf_test.c:9:12: warning: comparison between pointer and integer
[enabled by default]
if (fp == NULL) exit(1);
^
proj_conf_test.c:10:5: warning: implicit declaration of function
'pj_ctx_fclose' [-Wimplicit-function-declaration]
pj_ctx_fclose(ctx, fp);
^
./configure: line 3834: ./proj_conf_test: No such file or directory
checking PROJ.4: conus found and readable... yes
checking geos-config usability... yes
configure: GEOS: 3.5.0
checking GEOS version >= 3.3.0... yes
checking geos_c.h usability... yes
checking geos_c.h presence... yes
checking for geos_c.h... yes
checking geos: linking with -lgeos_c... yes
checking for lwgeom_version in -llwgeom... no
configure: error: in `/tmp/RtmpKLmhyt/devtools9fd11eb9c3/edzer-sfr-d620f3b':
configure: error: lwgeom test failed (--without-readline to disable)
See `config.log' for more details
ERROR: configuration failed for package ‘sf’
* removing ‘/home/mlt244/R/x86_64-pc-linux-gnu-library/3.3/sf’
* restoring previous ‘/home/mlt244/R/x86_64-pc-linux-gnu-library/3.3/sf’
Error: Command failed (1)
On Wed, Mar 15, 2017 at 6:14 PM, Michael Treglia <mtreglia at gmail.com> wrote:
> Wow - thanks so much for this quick fix, Edzer! I like your solution to
> having syntatically different but semantically identical proj4string. Will
> try this in a bit, and write back if I have any issues.
> Best,
> mike
>
> On Wed, Mar 15, 2017 at 5:58 PM, Edzer Pebesma <
> edzer.pebesma at uni-muenster.de> wrote:
>
>> Great question! Short answer: now solved in the dev version on github;
>> data are written directly to postgis having epsg 2263.
>>
>>
>> Long answer:
>>
>> I believe this was caused by gdal and proj.4 doing different things when
>> resolving epsg codes to proj4strings. sf uses proj.4 for this. When
>> writing a proj4string either gdal or postgis don't recognize the
>> proj4string that proj.4 returns on the epsg code. Neither gdal nor
>> postgis understand that syntactically different proj4strings may be
>> semantically identical.
>>
>>
>> After running your example, in postgis
>>
>> # select proj4text from spatial_ref_sys where SRID = 900914;
>>
>> gives:
>>
>> +proj=lcc +lat_1=41.03333333333333 +lat_2=40.66666666666666
>> +lat_0=40.16666666666666 +lon_0=-74 +x_0=300000 +y_0=0 +ellps=GRS80
>> +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs
>>
>> # select proj4text from spatial_ref_sys where SRID = 2263;
>>
>> gives:
>>
>> +proj=lcc +lat_1=41.03333333333333 +lat_2=40.66666666666666
>> +lat_0=40.16666666666666 +lon_0=-74 +x_0=300000.0000000001 +y_0=0
>> +datum=NAD83 +units=us-ft +no_defs
>>
>> sf causes this:
>>
>> > st_crs(2263)
>> $epsg
>> [1] 2263
>>
>> $proj4string
>> [1] "+proj=lcc +lat_1=41.03333333333333 +lat_2=40.66666666666666
>> +lat_0=40.16666666666666 +lon_0=-74 +x_0=300000.0000000001 +y_0=0
>> +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs"
>>
>> attr(,"class")
>> [1] "crs"
>>
>> because it uses proj.4 directly to resolve SRID strings:
>>
>> /usr/share/proj/epsg has:
>>
>> # NAD83 / New York Long Island (ftUS)
>> <2263> +proj=lcc +lat_1=41.03333333333333 +lat_2=40.66666666666666
>> +lat_0=40.16666666666666 +lon_0=-74 +x_0=300000.0000000001 +y_0=0
>> +datum=NAD83 +units=us-ft +no_defs <>
>>
>>
>> The change I made to sf (
>> https://github.com/edzer/sfr/commit/d620f3b748d487bae910e388
>> 4c554fa9e3ce7090)
>> now first asks gdal to resolve the epsg (in case it is not NA), and
>> otherwise resolve the proj4string (if not NA), instead of ONLY trying to
>> resolve a non-NA proj4string.
>>
>> On 15/03/17 20:50, Michael Treglia wrote:
>> > Hi All,
>> >
>> > Been working to import and manipulate a CSV file with point data
>> > (lat/long), and then export to a PostGIS db.
>> >
>> > Overall, successful, but one thing I'd like to fix - when I write out
>> the
>> > layer to postgis, the SRID is not maintained. The final EPSG/SRID
>> should be
>> > 2263, but when I check in PostGIS, it comes up as 900914.
>> >
>> > Below is my code and sessionInfo, and the data are from here:
>> > https://data.cityofnewyork.us/Public-Safety/NYPD-Complaint-D
>> ata-Current-YTD/5uac-w243
>> > (downloaded as plain old CSV)
>> >
>> > Anything I might be missing? Thanks in advance for giving a quick look!
>> > Mike
>> >
>> >
>> > ##Start Code
>> >
>> > #load packages
>> > library(sf)
>> > library(RPostgreSQL)
>> >
>> > #read data
>> > crime_current <- read.csv("NYPD_Complaint_Data_Current_YTD.csv",
>> > stringsAsFactors = FALSE)
>> >
>> > #format time columns for easier reading in postgres (I think), as
>> keeping
>> > as date format caused problems in export
>> > crime_current$CMPLNT_FR_TIME <-
>> > as.character(as.POSIXct(paste(crime_current$CMPLNT_FR_DT,
>> > crime_current$CMPLNT_FR_TM), format="%m/%d/%Y\ %H:%M", tz=""))
>> > crime_current$CMPLNT_TO_TIME <-
>> > as.character(as.POSIXct(paste(crime_current$CMPLNT_TO_DT,
>> > crime_current$CMPLNT_TO_TM), format="%m/%d/%Y\ %H:%M", tz=""))
>> > crime_current$RPT_DT <- as.character(as.POSIXct(crime_current$RPT_DT,
>> > format="%m/%d/%Y", tz=""))
>> >
>> > #convert to sf object
>> > crime_current.sf <- st_as_sf(crime_current, coords = c("Longitude",
>> > "Latitude"), crs = 4326)
>> > #reproject to EPSG 2263
>> > crime_current.sf <- st_transform(crime_current.sf, crs=2263)
>> >
>> > #write to postgres
>> > st_write(crime_current.sf, "PG:dbname=mydb user=user host=xx.xx.xx.xx",
>> > 'health_safety.crime_current')
>> > ###End Code
>> >
>> >
>> >
>> >> sessionInfo()
>> > R version 3.3.1 (2016-06-21)
>> > Platform: x86_64-pc-linux-gnu (64-bit)
>> > Running under: Ubuntu 14.04.5 LTS
>> >
>> > locale:
>> > [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
>> > LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
>> > [5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
>> > LC_PAPER=en_US.UTF-8 LC_NAME=C
>> > [9] LC_ADDRESS=C LC_TELEPHONE=C
>> > LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
>> >
>> > attached base packages:
>> > [1] stats graphics grDevices utils datasets methods base
>> >
>> > other attached packages:
>> > [1] sp_1.2-3 RPostgreSQL_0.4-1 DBI_0.6 sf_0.3-4
>> >
>> > loaded via a namespace (and not attached):
>> > [1] tools_3.3.1 units_0.4-2 Rcpp_0.12.9 udunits2_0.13
>> > grid_3.3.1 lattice_0.20-33
>> >
>> > [[alternative HTML version deleted]]
>> >
>> > _______________________________________________
>> > R-sig-Geo mailing list
>> > R-sig-Geo at r-project.org
>> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>> >
>>
>> --
>> Edzer Pebesma
>> Institute for Geoinformatics (ifgi), University of Münster
>> Heisenbergstraße 2, 48149 Münster, Germany; +49 251 83 33081
>> Journal of Statistical Software: http://www.jstatsoft.org/
>> Computers & Geosciences: http://elsevier.com/locate/cageo/
>>
>>
>> _______________________________________________
>> R-sig-Geo mailing list
>> R-sig-Geo at r-project.org
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
>
>
[[alternative HTML version deleted]]
More information about the R-sig-Geo
mailing list