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

[Michael Treglia]

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 <
englishchristophera at 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 <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-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
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
>
>

	[[alternative HTML version deleted]]