[R-sig-Geo] Maintain SRID with st_write to postgis db

chris english englishchristophera at gmail.com
Thu Mar 16 14:53:14 CET 2017


Edzer,

Installs fine now, though st_make_valid is useful when cleaning messy
inputs. I
just installed new ubuntu binaries for PostGIS last night but will attend
to rebuild
from source.

 The only thing to come up in an otherwise smooth install:
** preparing package for lazy loading
in method for ‘coerce’ with signature ‘"Spatial","sf"’: no definition for
class “Spatial”
in method for ‘coerce’ with signature ‘"Spatial","sfc"’: no definition for
class “Spatial”
in method for ‘coerce’ with signature ‘"sf","Spatial"’: no definition for
class “Spatial”
in method for ‘coerce’ with signature ‘"sfc","Spatial"’: no definition for
class “Spatial”
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded
* DONE (sf)
Reloading installed sf
Linking to GEOS 3.4.2, GDAL 2.1.3, proj.4 4.9.1

I'm guessing this refers to sp::Spatial-class as Spatial is capitalized. I
don't know
whether this will affect swapping in and out of sf <->sp, but include it
for completeness.

Thanks again,

Chris

On Thu, Mar 16, 2017 at 5:06 AM, Edzer Pebesma <
edzer.pebesma at uni-muenster.de> wrote:

> Mike, Chris, the configure step should now pass regardless whether
> liblwgeom is present, or not; the error message was my mistake.
>
> sf will link to liblwgeom when its development version is present (on
> ubuntu: liblwgeom-dev). If postgis is binary installed, liblwgeom may be
> present, but not its dev version (needed for header files); that case is
> now being caught too.
>
> liblwgeom provides st_make_valid, essentially ST_makeValid in postgis;
> see https://github.com/edzer/sfr/issues/67 . Since liblwgeom is not
> available on the CRAN build farms, it's presence is not required by sf.
>
> Best regards,
>
> On 16/03/17 05:46, chris english wrote:
> > 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
> > <mailto: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
> >     <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
> >     <mailto: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
> >     <mailto: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
> >     <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
> >     <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 <mailto:R-sig-Geo at r-project.org>
> >     >> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >     <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
> >     <tel:%2B49%20251%2083%2033081>
> >     >> Journal of Statistical Software:   http://www.jstatsoft.org/
> >     >> Computers & Geosciences:   http://elsevier.com/locate/cageo/
> >     <http://elsevier.com/locate/cageo/>
> >     >>
> >     >>
> >     >> _______________________________________________
> >     >> R-sig-Geo mailing list
> >     >> R-sig-Geo at r-project.org <mailto:R-sig-Geo at r-project.org>
> >     >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >     <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 <mailto:R-sig-Geo at r-project.org>
> >     https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >     <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/
>
>

	[[alternative HTML version deleted]]



More information about the R-sig-Geo mailing list