[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