1

I have a geopackage containing millions of traffic points as well as a highway zone layer. I would like to extract points inside the highway zones within a R-script.

It is possible to query the attribute data using the library(RSQLite) and dbGetQuery but it seems like the spatial extensions are not available. I'm probably attempting something that is better in another way. Below is a query retrieving data that works and a simple query to replicate the error I receive with any geometry functions. 

    library(RSQLite)
    library(dbplyr)
    library(sqldf)

    #Connect to geopackage
    con <- dbConnect(RSQLite::SQLite(), dbname = "Traffic_data.gpkg", 
    loadable.extensions = TRUE)

    #Select friday traffic
    Traffic_Friday <- dbGetQuery(con, "SELECT*
    FROM Traffic_data_points
    WHERE Day_ = 'Friday' ;")

    #But if I include ST_Within:
    Traffic_Friday <- dbGetQuery(con, "SELECT*
    FROM Traffic_data_points as tp, highway_buf as hb 
    WHERE tp.Day_ = 'Friday' and ST_Within(tp.geom, hb.geom) ;")

I get this error: Error in result_create(conn@ptr, statement) : no such function: ST_Within. It is the same with all the geometry functions

Is it possible to do something like this?

Kempie
  • 263
  • 2
  • 10
  • Is this `ST_Within()` an R function? – Shawn Jul 09 '19 at 14:20
  • SQLite is a very basic, non-server, non-enterprise RDBMS (hence *lite* in the name). Extended features such as GIS, XML, JSON, etc. are for the enterprise RDBMS's (e.g., Oracle, Postgres, DB2, SQL Server). Consider open source PostgreSQL's [PostGIS](https://postgis.net/) extension. – Parfait Jul 09 '19 at 14:25
  • @Shawn. The "ST_Within" in this example is a spatial sql function and not R. I think in R there is a function st_within under the SF library that does the same but I was hoping to do this in a database environment because it is supposed to be a lot faster. – Kempie Jul 09 '19 at 16:19
  • @Parfait. Thank you for your reply. I thought this was going to be the answer although I was hoping it was not or that there will be an extension or something. I receive the data in a geopackage but Postgis would do fine for now. Thanks. – Kempie Jul 09 '19 at 16:27
  • "spatial sql function"? Are you using spatialite and not stock sqlite then? – Shawn Jul 09 '19 at 16:49
  • I use the RSQlite library in R, so it seems like I need to add the spatialite extensions. Following the GitHub conversation : [link to conversation](http://github.com/r-dbi/RSQLite/issues/84), I found that there is a extension in devtools: [link to extension](http://github.com/pschmied/RSQLite.spatialite). However, I run into this message when installing the package: 'fatal error: geos_c.h: No such file or directory'. I think this is a different question, so will stay with postgis for now. – Kempie Jul 10 '19 at 12:57

1 Answers1

1

The answer was straightforward in the end. Instead of using SQLite to connect to a geopackage, one can use the st_read function found in the SF library Link to sf_read. It also also allow for sql query in which the spatial functions are included. 

    library(sf)

    Traffic_Friday <- st_read("Traffic_data.gpkg", "SELECT*
    FROM Traffic_data_points as tp, highway_buf as hb 
    WHERE tp.Day_ = 'Friday' and ST_Within(tp.geom, hb.geom) ;")
Kempie
  • 263
  • 2
  • 10