{"action":"created","issue":{"url":"https://api.github.com/repos/edzer/sfr/issues/216","repository_url":"https://api.github.com/repos/edzer/sfr","labels_url":"https://api.github.com/repos/edzer/sfr/issues/216/labels{/name}","comments_url":"https://api.github.com/repos/edzer/sfr/issues/216/comments","events_url":"https://api.github.com/repos/edzer/sfr/issues/216/events","html_url":"https://github.com/edzer/sfr/issues/216","id":207297155,"number":216,"title":"st_read_db and SQlite DB","user":{"login":"PauloEduardoCardoso","id":173906,"avatar_url":"https://avatars3.githubusercontent.com/u/173906?v=3","gravatar_id":"","url":"https://api.github.com/users/PauloEduardoCardoso","html_url":"https://github.com/PauloEduardoCardoso","followers_url":"https://api.github.com/users/PauloEduardoCardoso/followers","following_url":"https://api.github.com/users/PauloEduardoCardoso/following{/other_user}","gists_url":"https://api.github.com/users/PauloEduardoCardoso/gists{/gist_id}","starred_url":"https://api.github.com/users/PauloEduardoCardoso/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/PauloEduardoCardoso/subscriptions","organizations_url":"https://api.github.com/users/PauloEduardoCardoso/orgs","repos_url":"https://api.github.com/users/PauloEduardoCardoso/repos","events_url":"https://api.github.com/users/PauloEduardoCardoso/events{/privacy}","received_events_url":"https://api.github.com/users/PauloEduardoCardoso/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2017-02-13T18:14:18Z","updated_at":"2017-05-01T17:57:30Z","closed_at":null,"body":"Attempt to read from a sample sqlite db will fail while trying to read from an unexistent schema.\r\n\r\n```R\r\nlink <- \"https://www.gaia-gis.it/spatialite-2.3.1/test-2.3.zip\"\r\ntemp <- tempfile()\r\ndownload.file(link, temp)\r\ntdir <- tempdir()\r\nutils::unzip (temp, exdir = tdir)\r\nunlink(temp)\r\ndb <- file.path(tdir, \"test-2.3.sqlite\")\r\ndbcon <- dbConnect(dbDriver(\"SQLite\"), db)\r\ndbListTables(dbcon)\r\ndbListFields(dbcon, \"HighWays\")\r\ndbReadTable(dbcon, \"HighWays\", geom_column = 'Geometry' )\r\ndbReadTable(dbcon, \"geometry_columns\")\r\nlin = st_read_db(dbcon, \"HighWays\", geom_column = 'Geometry')\r\n```\r\n```R\r\n> lin = st_read_db(dbcon, \"HighWays\", geom_column = 'Geometry')\r\nError in rsqlite_send_query(conn@ptr, statement) : \r\n  no such table: information_schema.views\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/edzer/sfr/issues/comments/298387743","html_url":"https://github.com/edzer/sfr/issues/216#issuecomment-298387743","issue_url":"https://api.github.com/repos/edzer/sfr/issues/216","id":298387743,"user":{"login":"edzer","id":520851,"avatar_url":"https://avatars2.githubusercontent.com/u/520851?v=3","gravatar_id":"","url":"https://api.github.com/users/edzer","html_url":"https://github.com/edzer","followers_url":"https://api.github.com/users/edzer/followers","following_url":"https://api.github.com/users/edzer/following{/other_user}","gists_url":"https://api.github.com/users/edzer/gists{/gist_id}","starred_url":"https://api.github.com/users/edzer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/edzer/subscriptions","organizations_url":"https://api.github.com/users/edzer/orgs","repos_url":"https://api.github.com/users/edzer/repos","events_url":"https://api.github.com/users/edzer/events{/privacy}","received_events_url":"https://api.github.com/users/edzer/received_events","type":"User","site_admin":false},"created_at":"2017-05-01T17:57:30Z","updated_at":"2017-05-01T17:57:30Z","body":"This now works.\r\n\r\n<details>\r\n\r\n```r\r\n> library(sf)\r\nLinking to GEOS 3.5.1, GDAL 2.1.3, proj.4 4.9.2, lwgeom 2.3.2 r15302\r\n> library(RSQLite)\r\n> db <- \"test-2.3.sqlite\"\r\n> dbcon <- dbConnect(dbDriver(\"SQLite\"), db)\r\n> dbListTables(dbcon)\r\n[1] \"HighWays\"          \"Regions\"           \"Towns\"            \r\n[4] \"geom_cols_ref_sys\" \"geometry_columns\"  \"spatial_ref_sys\"  \r\n[7] \"sqlite_sequence\"  \r\n> dbListFields(dbcon, \"HighWays\")\r\n[1] \"PK_UID\"   \"Name\"     \"Geometry\"\r\n> hw = dbReadTable(dbcon, \"HighWays\")\r\n> st_as_sfc(hw$Geometry, spatialite = TRUE)\r\nGeometry set for 775 features \r\ngeometry type:  LINESTRING\r\ndimension:      XY\r\nbbox:           xmin: 495683.4 ymin: 4159367 xmax: 1305516 ymax: 5139898\r\nepsg (SRID):    32632\r\nproj4string:    +proj=utm +zone=32 +datum=WGS84 +units=m +no_defs\r\nFirst 5 geometries:\r\nLINESTRING(668540.706868592 4858267.85756248, 6...\r\nLINESTRING(671365.867441752 4854173.7708021, 67...\r\nLINESTRING(671417.575852571 4854175.10324393, 6...\r\nLINESTRING(683100.868826631 4849547.00816066, 6...\r\nLINESTRING(683428.354612234 4849327.04600966, 6...\r\n> \r\n> tn = dbReadTable(dbcon, \"Towns\")\r\n> st_as_sfc(tn$Geometry, spatialite = TRUE)\r\nGeometry set for 8101 features \r\ngeometry type:  POINT\r\ndimension:      XY\r\nbbox:           xmin: 319224 ymin: 3934674 xmax: 1308585 ymax: 5214373\r\nepsg (SRID):    32632\r\nproj4string:    +proj=utm +zone=32 +datum=WGS84 +units=m +no_defs\r\nFirst 5 geometries:\r\nPOINT(427002.77 4996361.33)\r\nPOINT(367470.48 4962414.5)\r\nPOINT(390084.12 5025551.73)\r\nPOINT(425246.99 5000248.3)\r\nPOINT(426418.89 4957737.37)\r\n> \r\n> re = dbReadTable(dbcon, \"Regions\")\r\n> st_as_sfc(re$Geometry, spatialite = TRUE)\r\nGeometry set for 109 features \r\ngeometry type:  MULTIPOLYGON\r\ndimension:      XY\r\nbbox:           xmin: 312380.6 ymin: 3933596 xmax: 1311407 ymax: 5220663\r\nepsg (SRID):    32632\r\nproj4string:    +proj=utm +zone=32 +datum=WGS84 +units=m +no_defs\r\nFirst 5 geometries:\r\nMULTIPOLYGON(((752912.250296859 5028764.9890508...\r\nMULTIPOLYGON(((751705.496715354 5029084.6555139...\r\nMULTIPOLYGON(((766247.780711283 5036335.2991564...\r\nMULTIPOLYGON(((750594.839945831 5026719.0243425...\r\nMULTIPOLYGON(((554955.581882933 4911801.6754486...\r\n```\r\n</details>\r\n\r\nnote that:\r\n* `st_as_sf` now understands a list with raw vectors as wkb (what else could it be?)\r\n* `srid` seems to be always present in the spatialite geom blobs, but might be not an EPSG and may have to be resolved from the `geometry_columns` table, however, @PauloEduardoCardoso, I don't see any `proj4string` field in this table - is it only there when needed?\r\n* the `spatialite=TRUE` is needed, because regrettably spatialite doesn't have a magic flag, like GPKG does\r\n* differences between WKB and spatialite geom blobs make the code pretty ugly\r\n* I have only tested for the geoms in the example above, not for the other 4, nor XYM/XYZ/XYZMs; other examples are welcome."}}
{"actor":{"display_login":"edzer"}}