[R-sig-Geo] Maintain SRID with st_write to postgis db
chris english
englishchristophera at gmail.com
Thu Mar 16 05:46:30 CET 2017
Michael,
I have the very same failure,
> library(sf)
Linking to GEOS 3.4.2, GDAL 2.1.3, proj.4 4.9.1
(pre upgrade attempt, which still works just fine)
checking for geos_c.h... yes
checking geos: linking with -lgeos_c... yes
checking for lwgeom_version in -llwgeom... no
configure: error: in `/tmp/RtmpsCmf6B/devtoolsd68e68107a/edzer-sfr-d620f3b':
configure: error: lwgeom test failed (--without-readline to disable)
I could nearly copy your install output though on 16.04, and the same
tangle of it depends on x but it won't be installed & etc.
But, if you have PostGIS, you have liblwgeom as it is fairly specific to
PostGIS. Perhaps, rather than a devtools::github_install,
a clone github lo a local directory and config, make, install might work,
but I'm just imagining.
then testing:
config.log --snip --
configure:3993: checking for lwgeom_version in -llwgeom
configure:4018: gcc -std=gnu99 -o conftest -g -O2 -fstack-protector
--param=ssp-
buffer-size=4 -Wformat -Werror=format-security -D_FORTIFY_SOURCE=2 -g
-I/usr/lo
cal/include -I/usr/local/include -I/usr/local/include
-Wl,-Bsymbolic-functions
-Wl,-z,relro conftest.c -llwgeom -lproj -L/usr/local/lib -lproj
-L/usr/loca
l/lib -lgdal -L/usr/local/lib -lgeos_c -lproj >&5
/tmp/ccDBUh1h.o: In function `main':
/home/chris/sfr/sfr/conftest.c:34: undefined reference to `lwgeom_version'
collect2: error: ld returned 1 exit status
configure:4018: $? = 1
configure: failed program was:
| /* confdefs.h */
| #define PACKAGE_NAME ""
| #define PACKAGE_TARNAME ""
| #define PACKAGE_VERSION ""
| #define PACKAGE_STRING ""
| #define PACKAGE_BUGREPORT ""
| #define PACKAGE_URL ""
| #define STDC_HEADERS 1
| #define HAVE_SYS_TYPES_H 1
| #define HAVE_SYS_STAT_H 1
| #define HAVE_STDLIB_H 1
| #define HAVE_STRING_H 1
| #define HAVE_MEMORY_H 1
| #define HAVE_STRINGS_H 1
| #define HAVE_INTTYPES_H 1
| #define HAVE_STDINT_H 1
| #define HAVE_UNISTD_H 1
| #define HAVE_GDAL_H 1
| #define HAVE_PROJ_API_H 1
| #define HAVE_LIBPROJ 1
| #define HAVE_GEOS_C_H 1
| /* end confdefs.h. */
|
| /* Override any GCC internal prototype to avoid an error.
| Use char because int might match the return type of a GCC
| builtin and then its argument prototype would still apply. */
| #ifdef __cplusplus
| extern "C"
| #endif
| char lwgeom_version ();
| int
| main ()
| {
| return lwgeom_version ();
| ;
| return 0;
| }
configure:4027: result: no
configure:4036: error: in `/home/chris/sfr/sfr':
configure:4038: error: lwgeom test failed (--without-readline to disable)
See `config.log' for more details
so, it may or may not be the presence or absence of liblwgeom but simply
an undefined reference as suggested above:
/home/chris/sfr/sfr/conftest.c:34: undefined reference to `lwgeom_version' ,
but I'm unclear how the version was being requested (well, I can kind of
guess)
but failing on undefined reference suggests it is not the version per se
that may
or may not be present (though is because you are using PostGIS), but how
the version was requested. I intuit.
Ah, and reading /* confdefs.h */, that it indeed ends with #define
HAVE_GEOS_C_H 1,
and indeed, conftest.c:34: would have an undefined reference to
`lwgeom_version'.
And I've said as much as I understand.
Chris
On Wed, Mar 15, 2017 at 7:53 PM, Michael Treglia <mtreglia at gmail.com> wrote:
> 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]]
>
> _______________________________________________
> 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