Maintain SRID with st_write to postgis db

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

chris english-2
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 <[hidden email]> 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
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <
[hidden email]> 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 <[hidden email]>
> 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
>> [hidden email]
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
>
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

edzer
In reply to this post by Michael Treglia
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/d620f3b748d487bae910e3884c554fa9e3ce7090)
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-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
> [hidden email]
> 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/


_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

signature.asc (484 bytes) Download Attachment
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

Michael Treglia
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 <
[hidden email]> 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/d620f3b748d487bae910e3884c554f
> a9e3ce7090)
> 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-
> 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
> > [hidden email]
> > 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/
>
>
> _______________________________________________
> R-sig-Geo mailing list
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

Michael Treglia
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@master
from URL 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 <[hidden email]> 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 <
> [hidden email]> 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
>> 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
>> 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
>> > [hidden email]
>> > 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/
>>
>>
>> _______________________________________________
>> R-sig-Geo mailing list
>> [hidden email]
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>
>
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

chris english-2
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 <[hidden email]> 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@master
> from URL 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 <[hidden email]>
> 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 <
> > [hidden email]> 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
> >> 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
> >> 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
> >> > [hidden email]
> >> > 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/
> >>
> >>
> >> _______________________________________________
> >> R-sig-Geo mailing list
> >> [hidden email]
> >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >>
> >
> >
>
>         [[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-Geo mailing list
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

edzer
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 <[hidden email]
> <mailto:[hidden email]>> 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@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 <[hidden email]
>     <mailto:[hidden email]>> 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 <
>     > [hidden email]
>     <mailto:[hidden email]>> 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
>     >> > [hidden email] <mailto:[hidden email]>
>     >> > 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
>     >> [hidden email] <mailto:[hidden email]>
>     >> 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
>     [hidden email] <mailto:[hidden email]>
>     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/


_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

signature.asc (484 bytes) Download Attachment
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

chris english-2
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 <
[hidden email]> 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 <[hidden email]
> > <mailto:[hidden email]>> 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@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 <[hidden email]
> >     <mailto:[hidden email]>> 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 <
> >     > [hidden email]
> >     <mailto:[hidden email]>> 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
> >     >> > [hidden email] <mailto:[hidden email]>
> >     >> > 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
> >     >> [hidden email] <mailto:[hidden email]>
> >     >> 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
> >     [hidden email] <mailto:[hidden email]>
> >     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]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

edzer


On 16/03/17 14:53, chris english wrote:

> 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.
You can safely ignore it.

If `sf' would import `sp' it would go away, but there is no need to do
so. Not importing packages makes it easier to understand where they are
being used, and so limits the search for where things can go wrong.

>
> Thanks again,
>
> Chris
>
> On Thu, Mar 16, 2017 at 5:06 AM, Edzer Pebesma
> <[hidden email] <mailto:[hidden email]>>
> 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
>     <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
>     <[hidden email] <mailto:[hidden email]>
>     > <mailto:[hidden email] <mailto:[hidden email]>>> 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@master
>     >     from URL https://api.github.com/repos/edzer/sfr/zipball/master
>     <https://api.github.com/repos/edzer/sfr/zipball/master>
>     >     <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
>     <[hidden email] <mailto:[hidden email]>
>     >     <mailto:[hidden email] <mailto:[hidden email]>>> 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 <
>     >     > [hidden email] <mailto:[hidden email]>
>     >     <mailto:[hidden email]
>     <mailto:[hidden email]>>> 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>
>     >     <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>
>     >     <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
>     >     >> > [hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>
>     >     >> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>     >     <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>
>     >     <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/>
>     >     <http://elsevier.com/locate/cageo/ <http://elsevier.com/locate/cageo/>>
>     >     >>
>     >     >>
>     >     >> _______________________________________________
>     >     >> R-sig-Geo mailing list
>     >     >> [hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>
>     >     >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>     >     <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
>     >     [hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email] <mailto:[hidden email]>>
>     >     https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>     >     <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/>
>
>
--
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/


_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

signature.asc (484 bytes) Download Attachment
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

Michael Treglia
Thanks again Edzer - I've got the dev version installed sucessfully
now, but new issue popped up: my st_write statement now throws an
error:


>st_write(nypd_crime_current.sf, dsn="PG:dbname=dbname user=user host=xx.xx.xx.xx", layer='health_safety.crime_current')

Writing layer `health_safety.crime_current' to data source
`PG:dbname=dbname user=user host=xx.xx.xx.xx' using driver
`PostgreSQL'
Dataset PG:dbname=dbname user=user host=xx.xx.xx.xx already exists;
remove first, or use update=TRUE to append.
Error in CPL_write_ogr(obj, dsn, layer, driver,
as.character(dataset_options),  :
  Dataset already exists.

> 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] RPostgreSQL_0.4-1 DBI_0.6           sf_0.4-0

loaded via a namespace (and not attached):
[1] magrittr_1.5  tools_3.3.1   units_0.4-2   Rcpp_0.12.9
udunits2_0.13 grid_3.3.1






The command works with the CRAN version of sf

> 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] sf_0.3-4

loaded via a namespace (and not attached):
[1] DBI_0.6       tools_3.3.1   units_0.4-2   Rcpp_0.12.9
udunits2_0.13 grid_3.3.1


(I realize st_write_db is an option, but not finding myself able to
specify a schema (it just gets written to public - if I name the table
'schema.tableName')

Sorry to keep bugging about this, and thanks again for these quick fixes!
mike

On Thu, Mar 16, 2017 at 11:15 AM, Edzer Pebesma
<[hidden email]> wrote:

>
>
> On 16/03/17 14:53, chris english wrote:
>> 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.
>
> You can safely ignore it.
>
> If `sf' would import `sp' it would go away, but there is no need to do
> so. Not importing packages makes it easier to understand where they are
> being used, and so limits the search for where things can go wrong.
>
>>
>> Thanks again,
>>
>> Chris
>>
>> On Thu, Mar 16, 2017 at 5:06 AM, Edzer Pebesma
>> <[hidden email] <mailto:[hidden email]>>
>> 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
>>     <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
>>     <[hidden email] <mailto:[hidden email]>
>>     > <mailto:[hidden email] <mailto:[hidden email]>>> 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@master
>>     >     from URL https://api.github.com/repos/edzer/sfr/zipball/master
>>     <https://api.github.com/repos/edzer/sfr/zipball/master>
>>     >     <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
>>     <[hidden email] <mailto:[hidden email]>
>>     >     <mailto:[hidden email] <mailto:[hidden email]>>> 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 <
>>     >     > [hidden email] <mailto:[hidden email]>
>>     >     <mailto:[hidden email]
>>     <mailto:[hidden email]>>> 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>
>>     >     <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>
>>     >     <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
>>     >     >> > [hidden email] <mailto:[hidden email]>
>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>     >     >> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>     >     <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>
>>     >     <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/>
>>     >     <http://elsevier.com/locate/cageo/ <http://elsevier.com/locate/cageo/>>
>>     >     >>
>>     >     >>
>>     >     >> _______________________________________________
>>     >     >> R-sig-Geo mailing list
>>     >     >> [hidden email] <mailto:[hidden email]>
>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>     >     >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>     >     <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
>>     >     [hidden email] <mailto:[hidden email]>
>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>     >     https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>     >     <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/>
>>
>>
>
> --
> 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/
>

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintain SRID with st_write to postgis db

edzer


On 16/03/17 20:01, Michael Treglia wrote:

> Thanks again Edzer - I've got the dev version installed sucessfully
> now, but new issue popped up: my st_write statement now throws an
> error:
>
>
>> st_write(nypd_crime_current.sf, dsn="PG:dbname=dbname user=user host=xx.xx.xx.xx", layer='health_safety.crime_current')
>
> Writing layer `health_safety.crime_current' to data source
> `PG:dbname=dbname user=user host=xx.xx.xx.xx' using driver
> `PostgreSQL'
> Dataset PG:dbname=dbname user=user host=xx.xx.xx.xx already exists;
> remove first, or use update=TRUE to append.
with update=TRUE it will work.

So far, sf would simply overwrite existing files on st_write(), which is
not exactly a good idea, always - rgdal::writeOGR is much more careful.
I changed it this way now, but agree that it is not very satisfactory if
dsn is a database, which is expected to exist, so I expect the
requirement to add `update=TRUE' to disappear in a future release for
database dsn's.

more further down:

> Error in CPL_write_ogr(obj, dsn, layer, driver,
> as.character(dataset_options),  :
>   Dataset already exists.
>
>> 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] RPostgreSQL_0.4-1 DBI_0.6           sf_0.4-0
>
> loaded via a namespace (and not attached):
> [1] magrittr_1.5  tools_3.3.1   units_0.4-2   Rcpp_0.12.9
> udunits2_0.13 grid_3.3.1
>
>
>
>
>
>
> The command works with the CRAN version of sf
>
>> 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] sf_0.3-4
>
> loaded via a namespace (and not attached):
> [1] DBI_0.6       tools_3.3.1   units_0.4-2   Rcpp_0.12.9
> udunits2_0.13 grid_3.3.1
>
>
> (I realize st_write_db is an option, but not finding myself able to
> specify a schema (it just gets written to public - if I name the table
> 'schema.tableName')
try table = c("schema", "table")

>
> Sorry to keep bugging about this, and thanks again for these quick fixes!
> mike
>
> On Thu, Mar 16, 2017 at 11:15 AM, Edzer Pebesma
> <[hidden email]> wrote:
>>
>>
>> On 16/03/17 14:53, chris english wrote:
>>> 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.
>>
>> You can safely ignore it.
>>
>> If `sf' would import `sp' it would go away, but there is no need to do
>> so. Not importing packages makes it easier to understand where they are
>> being used, and so limits the search for where things can go wrong.
>>
>>>
>>> Thanks again,
>>>
>>> Chris
>>>
>>> On Thu, Mar 16, 2017 at 5:06 AM, Edzer Pebesma
>>> <[hidden email] <mailto:[hidden email]>>
>>> 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
>>>     <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
>>>     <[hidden email] <mailto:[hidden email]>
>>>     > <mailto:[hidden email] <mailto:[hidden email]>>> 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@master
>>>     >     from URL https://api.github.com/repos/edzer/sfr/zipball/master
>>>     <https://api.github.com/repos/edzer/sfr/zipball/master>
>>>     >     <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
>>>     <[hidden email] <mailto:[hidden email]>
>>>     >     <mailto:[hidden email] <mailto:[hidden email]>>> 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 <
>>>     >     > [hidden email] <mailto:[hidden email]>
>>>     >     <mailto:[hidden email]
>>>     <mailto:[hidden email]>>> 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>
>>>     >     <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>
>>>     >     <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
>>>     >     >> > [hidden email] <mailto:[hidden email]>
>>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>>     >     >> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>>     >     <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>
>>>     >     <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/>
>>>     >     <http://elsevier.com/locate/cageo/ <http://elsevier.com/locate/cageo/>>
>>>     >     >>
>>>     >     >>
>>>     >     >> _______________________________________________
>>>     >     >> R-sig-Geo mailing list
>>>     >     >> [hidden email] <mailto:[hidden email]>
>>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>>     >     >> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>>     >     <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
>>>     >     [hidden email] <mailto:[hidden email]>
>>>     <mailto:[hidden email] <mailto:[hidden email]>>
>>>     >     https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>>>     <https://stat.ethz.ch/mailman/listinfo/r-sig-geo>
>>>     >     <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/>
>>>
>>>
>>
>> --
>> 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/
>>
--
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/


_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

signature.asc (484 bytes) Download Attachment
Loading...