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

[chris english]

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 <mtreglia at 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-
> Data-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
>

	[[alternative HTML version deleted]]