[R-sig-Geo] Error reading spatialite table with sf::st_read

[Barry Rowlingson]

Looks like that error might be generated about here:
https://github.com/r-spatial/sf/blob/5dde39c8261dc6b202e6cde9d41ad3bf0f46aa3a/R/db.R#L134

where the code does:

try(sfc <- st_as_sfc(tbl[[i]]), silent = TRUE)
if (!inherits(sfc, "try-error")) {
tbl[[i]] = sfc
success = TRUE
}

If the first "try" fails then `sfc` doesn't get created and so the error
happens on `if(!inherits(sfc,....)`.

The code is looping over columns trying to find a valid geometry column,
but not trapping this error properly. A bug, I think. Feel free to report
as an issue.

However I don't think that gets us out of the woods - that code only talks
about PostGIS databases, but it gets called for any object of class
DBIObject, which your SQLite connection object is:

 > inherits(con,"DBIObject")
 [1] TRUE

Perhaps that method should be something like st_read.PGConnection if it
only works on PostGIS databases? Experimenting with bits of that function
don't get me the geometry from my test spatialite DB anyway, and I think
its due to it being PostGIS-specific. I kept getting empty geometries.

But why not read the Spatialite directly without going through DBI?  Here's
a test I made of some points:

> d = st_read("/tmp/olinda1.sqlite","pts")
Reading layer `pts' from data source `/tmp/olinda1.sqlite' using driver
`SQLite'
Simple feature collection with 1000 features and 1 field
Geometry type: POINT
Dimension:     XY
Bounding box:  xmin: 234250 ymin: -198750 xmax: 909750 ymax: 502750
CRS:           unknown

THe CRS is printed as unknown but the WKT is there. Maybe this is a funny
CRS I was using for testing or something:

> st_crs(d)
Coordinate Reference System:
  User input: unknown
  wkt:
PROJCRS["unknown",
    BASEGEOGCRS["GCS_unknown",
        DATUM["D_Unknown_based_on_Australian_Natl_S_Amer_1969_ellipsoid",
            ELLIPSOID["Australian_Natl_S_Amer_1969",6378160,298.25,
                LENGTHUNIT["metre",1,

and you can add SQL queries to `st_read` if that;s part of your motivation
for using a spatial file database:

> d = st_read("/tmp/olinda1_no.sqlite", query="select * from pts where
pampa_srtm>300")
Reading query `select * from pts where pampa_srtm>300' from data source
`/tmp/olinda1_no.sqlite' using driver `SQLite'
Simple feature collection with 189 features and 1 field

Barry

On Thu, Mar 3, 2022 at 4:48 PM DUTRIEUX Loic <Loic.DUTRIEUX using ec.europa.eu>
wrote:

> Hi everyone,
>
> When trying to read data contained in a sqlite table with the full
> spatialite options (see what FORMAT=SPATIALITE implies here -->
> https://gdal.org/drivers/vector/sqlite.html), I get a
> wkbType: 30078980 Error in inherits(sfc, "try-error") : object 'sfc' not
> found.
>
> See the example below:
>
> # In bash
> ogr2ogr -F SQLITE -dsco SPATIALITE=YES /tmp/olinda1.sqlite
> ~/R/x86_64-pc-linux-gnu-library/4.0/sf/shape/olinda1.shp
>
> # In R
> library(sf)
> library(DBI)
>
> con <- dbConnect(RSQLite::SQLite(), '/tmp/olinda1.sqlite')
> st_read(con, query='select * from olinda1;')
>
>
> If I create the database without the SPATIALITE=YES creation option
> (geometries encoded as WKB), reading with sf partly works (no CRS) but then
> I lose the spatialite functionalities. Any suggestion?
>
> Kind regards,
> Loïc
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

	[[alternative HTML version deleted]]