{"action":"created","issue":{"url":"https://api.github.com/repos/rstats-db/RPostgres/issues/86","repository_url":"https://api.github.com/repos/rstats-db/RPostgres","labels_url":"https://api.github.com/repos/rstats-db/RPostgres/issues/86/labels{/name}","comments_url":"https://api.github.com/repos/rstats-db/RPostgres/issues/86/comments","events_url":"https://api.github.com/repos/rstats-db/RPostgres/issues/86/events","html_url":"https://github.com/rstats-db/RPostgres/issues/86","id":153235406,"number":86,"title":"Request to add PostGIS support","user":{"login":"zross","id":1395177,"avatar_url":"https://avatars.githubusercontent.com/u/1395177?v=3","gravatar_id":"","url":"https://api.github.com/users/zross","html_url":"https://github.com/zross","followers_url":"https://api.github.com/users/zross/followers","following_url":"https://api.github.com/users/zross/following{/other_user}","gists_url":"https://api.github.com/users/zross/gists{/gist_id}","starred_url":"https://api.github.com/users/zross/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zross/subscriptions","organizations_url":"https://api.github.com/users/zross/orgs","repos_url":"https://api.github.com/users/zross/repos","events_url":"https://api.github.com/users/zross/events{/privacy}","received_events_url":"https://api.github.com/users/zross/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2016-05-05T13:47:07Z","updated_at":"2016-05-06T12:37:20Z","closed_at":null,"body":"I'm following up on a [tweet exchange](https://twitter.com/hadleywickham/status/723280840352886784) with Hadley. It would be spectacular RPostgres offered PostGIS support (support for PostgreSQL tables that have a geom).\r\n\r\nI can't give a perfectly reproducible example since it will depend on your versions of PostGIS etc, but here is an attempt\r\n\r\n### Create a spatial DB from the command line\r\n\r\n```batch\r\ncreatedb --host localhost --port 5433 --username postgres --template postgis_21_sample  mypostgis \r\n```\r\n\r\n### Create a new table, add a geometry column and fill with one polygon (use psql or pgAdmin)\r\n\r\n```sql\r\nDROP TABLE IF EXISTS buildings;\r\nCREATE TABLE buildings ( ID int4, name varchar(25) );\r\nSELECT AddGeometryColumn('public', 'buildings', 'geom', 4326, 'MULTIPOLYGON', 2);\r\nINSERT INTO buildings VALUES(1, 'some name', ST_GeomFromText('MULTIPOLYGON(((-76.5088 42.454, -76.4577 42.45921,  -76.448 42.415889, \r\n-76.5157 42.4199, -76.5088 42.454)))', 4326));\r\nCREATE INDEX bldg_gist ON buildings USING gist(geom);\r\n```\r\n\r\n### In R show what happens with `RPostgres`\r\n\r\nYou can read in the non-spatial fields just fine, but you get a warning and you get the raw geom data when your query includes the spatial variable (geom). Ideally, if you query against a spatial file it would return some kind of `sp` object like, in this case, a `SpatialPolygonsDataFrame`.\r\n\r\n```r\r\nlibrary(RPostgres)\r\ncon <- dbConnect(RPostgres::Postgres(),dbname = 'mypostgis', \r\n                 host = 'localhost', \r\n                 port = 5433,\r\n                 user = 'postgres',\r\n                 password = 'xxx')\r\n\r\nres <- dbSendQuery(con, \"SELECT id, name FROM buildings\")\r\ndbFetch(res)\r\n#   id      name\r\n# 1  1 some name\r\n\r\nres <- dbSendQuery(con, \"SELECT * FROM buildings\")\r\n# Warning: Unknown field type (16400) in column geom\r\ndat <- dbFetch(res)\r\ndat\r\n#   id      name\r\n# 1  1 some name\r\n#                                                                                                                                                                                                                   geom\r\n# 1 0106000020E610000001000000010300000001000000050000001B0DE02D902053C0\r\n# 273108AC1C3A454045D8F0F44A1D53C082FFAD64C73A4540E9263108AC1C53C011E2CAD93B\r\n# 35454005A3923A012153C092CB7F48BF3545401B0DE02D902053C0273108AC1C3A4540\r\nclass(dat)\r\n# data.frame\r\n```\r\n\r\n### The `postGIStools` package offers some support\r\n\r\nWith the postGIStools package you can read in PostGIS data\r\n\r\n```r\r\ncon <- dbConnect(RPostgreSQL::PostgreSQL(),dbname = 'mypostgis', \r\n                 host = 'localhost', \r\n                 port = 5433,\r\n                 user = 'postgres',\r\n                 password = 'spatial')\r\nlibrary(postGIStools)\r\n\r\n# SELECT * FROM buildings does not work for some reason\r\ndat <- get_postgis_query(con, \"SELECT id, name, geom FROM buildings\", \r\n                         geom_name = \"geom\")\r\nclass(dat)\r\n# [1] \"SpatialPolygonsDataFrame\"\r\n# attr(,\"package\")\r\n# [1] \"sp\"\r\n```\r\n\r\nThis is perfect except I found it to be slow on a medium size table. With 400k points, it took a pretty long time.\r\n\r\n### Speed\r\n\r\nThe image below shows that it takes almost 5 minutes to read in 400k points as a spatial file, but it can be read in faster if I write to shapefile first and then use `readOGR`. To me this suggests that there is likely a faster way to read this data directly into R.\r\n\r\n![image](https://cloud.githubusercontent.com/assets/1395177/15044739/9301e49c-12a4-11e6-9cf0-f24c10f84898.png)\r\n\r\n### Additional discussion on this\r\n\r\nI'm not sure what the speediest way to bring in spatial data from PostGIS but I think bringing in a table of just 400k points should take less than 5 minutes as it seems to with postGIStools. And, given the example above where I write to shapefile and read in in half the time, it's clear that there should be better alternatives. There is some discussion about this at the [postGIStools GitHub site](https://github.com/SESYNC-ci/postGIStools/issues/3).\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/rstats-db/RPostgres/issues/comments/217428212","html_url":"https://github.com/rstats-db/RPostgres/issues/86#issuecomment-217428212","issue_url":"https://api.github.com/repos/rstats-db/RPostgres/issues/86","id":217428212,"user":{"login":"zross","id":1395177,"avatar_url":"https://avatars.githubusercontent.com/u/1395177?v=3","gravatar_id":"","url":"https://api.github.com/users/zross","html_url":"https://github.com/zross","followers_url":"https://api.github.com/users/zross/followers","following_url":"https://api.github.com/users/zross/following{/other_user}","gists_url":"https://api.github.com/users/zross/gists{/gist_id}","starred_url":"https://api.github.com/users/zross/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zross/subscriptions","organizations_url":"https://api.github.com/users/zross/orgs","repos_url":"https://api.github.com/users/zross/repos","events_url":"https://api.github.com/users/zross/events{/privacy}","received_events_url":"https://api.github.com/users/zross/received_events","type":"User","site_admin":false},"created_at":"2016-05-06T12:37:20Z","updated_at":"2016-05-06T12:37:20Z","body":"For reference, here is what I use to get the proj4\r\n\r\n```r\r\nSELECT proj4text FROM spatial_ref_sys WHERE srid = (SELECT ST_SRID(geom) FROM the_table);\r\n```"}}