search

rwinlib / gdal2

Modern GDAL/PROJ/GEOS stack with lots of extra drivers
12 stars 9 forks source link

[Feature request] add PostgreSQL driver #1

Closed jguelat closed 7 years ago

jguelat commented 7 years ago

Hi!

It would be amazing to be able to access data in a PostGIS database using the R packages rgdal or sf on Windows. I know this requires adding a new dependency but I think it's definitely worth it since PostGIS is now a standard.

Thanks for your great work!

jeroen commented 7 years ago

The package already includes odbc support. Can you use this to connect to PG?

jguelat commented 7 years ago

I'm able to access my database using ogrInfo() with an odbc connection but unfortunately the geometry columns aren't recognized. Reading data using readOGR() doesn't work at all (error message: "ogrInfo: all features NULL").

This post from Roger Bivand makes me feel a little bit hopeless : https://stat.ethz.ch/pipermail/r-sig-geo/2012-December/017066.html

jeroen commented 7 years ago

OK I'll have a look net week when I'm back from traveling.

jguelat commented 7 years ago

Great! Thanks a lot!

mdsumner commented 7 years ago

You should be able to specify the dsn in a way that interprets the geometry, as per here:

http://www.gdal.org/drv_odbc.html

something like

dsn <- "ODBC:datasource,tablename(geometrycolname)"
st_read(dsn, "tablename")

but it is tricky to get the syntax right, you may need credentials and so on, and tis is just one of those areas where you tend to be on your own. It's easiest to test this from the command line more directly with ogrinfo. Also the st_read_db example could be followed to write an odbc version, I think.

I have examples somewhere but it's just not something I do routinely, feel free to ping me if you want I think I have installed data sources on a personal machine.

jeroen commented 7 years ago

Version 2.2.0 now contains support for libcurl and postgres.

jguelat commented 7 years ago

Awesome! Thanks a lot!