Hi Chris, Thanks for that insight - I hadn't caught that part of the vignette (it had admittedly been a bit since I last looked at it), and I appreciate your interpretation. What you say makes sense to me, reading through that, and I can naturally add code to set the SRID in PostGIS pretty easily.
Best, Mike On Wed, Mar 15, 2017 at 5:21 PM, chris english < englishchristoph...@gmail.com> wrote: > Michael, > > this from the sf vignettes on read write to databases: > > The dsn and layer arguments to st_read and st_write denote a data source > name and optionally a layer name. Their exact interpretation as well as the > options they support vary per driver, the GDAL driver documentation is best > consulted for this. For instance, a PostGIS table in database postgis might > be read by > > meuse <- st_read("PG:dbname=postgis", "meuse") > > where the PG: string indicates this concerns the PostGIS driver, followed > by database name, and possibly port and user credentials. > > st_read typically reads the coordinate reference system as proj4string, > but not the EPSG (SRID). GDAL cannot retrieve SRID (EPSG code) from > proj4string strings, and, when needed, it has to be set by the user. > > I just re-read this this morning for my own understanding, and the > statements regarding st_read would appear to apply as explicitly to > st_write as both or mediated by GDAL, that processes proj4string(s) but not > epsg. So it looks like manually setting or resetting epsg is part of the > workflow. Further down in the crs section is discussion about how within a > layer both proj4string and epsg must be the same, or NA. This may localize > where the 900914 is being applied, i.e. in PostGIS to settle the table > parameters. > > HTH, > > Chris > > > > > > On Wed, Mar 15, 2017 at 3:50 PM, Michael Treglia <mtreg...@gmail.com> > 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@r-project.org >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo >> > > [[alternative HTML version deleted]] _______________________________________________ R-sig-Geo mailing list R-sig-Geo@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-geo