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

[Michael Treglia]

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]]