[GIS] How to load a Spatialite layer into R

Question

I have a polygon layer (>600000 features) in a Spatialite database and I want to overcome to run out of RAM by selecting only the features I need for subsequent workflow.

I can connect to my Spatialite database using RSQLite with the following code:

dbfile="Path/To/field.sqlite"
sqlite=dbDriver("SQLite")
con=dbConnect(sqlite,dbfile, loadable.extensions=TRUE )

I can also query my table and get an tabular output:

above1600<-dbGetQuery(con,"SELECT * from height_aspect where height >= 1600")

But I do not understand how to get a SpatialPolygon layer.

Do I have to convert the output or do I need to initialise the database as a spatial one?

I researched the web for a while but could not find anything that helps me.

Answer 1

You might consider using rgdal to read in the features from a Spatialite database based on the following example.

From your terminal, check the layer names in your Spatialite database:

>ogrinfo  NUTS_BN_03M_2013.sqlite 
INFO: Open of `NUTS_BN_03M_2013.sqlite'
      using driver `SQLite' successful.
1: nuts_bn_03m_2013 (Line String)

Then within R, do as follows:

#load rgdal library
library(rgdal)

Check that your version of rgdal supports Spatialite (you will see SQLite/Spatialite TRUE)

ogrDrivers()

...
64         SQLite                                  SQLite / Spatialite  TRUE
...

Use readOGR to read in your vector layer, e.g.:

vectorImport <- readOGR(dsn="NUTS_BN_03M_2013.sqlite", layer="nuts_bn_03m_2013")

Check that everything has been correctly imported:

summary(vectorImport)
Object of class SpatialLinesDataFrame
Coordinates:
        min      max
x -63.15345 55.83663
y -21.38731 71.18532
Is projected: FALSE 
proj4string : [+proj=longlat +ellps=GRS80 +no_defs]
Data attributes:
 eu_flag  efta_flag cc_flag  othr_cntr_ coas_flag   nuts_bn_id          stat_levl_ 
 F: 869   F:5224    F:5551   F:5820     F:3988    Min.   :    1   Min.   :0.0  
 T:5007   T: 652    T: 325   T:  56     T:1888    1st Qu.: 1939   1st Qu.:0.0  
                                                  Median : 3885   Median :1.0  
                                                  Mean   : 3833   Mean   :1.4  
                                                  3rd Qu.: 5785   3rd Qu.:3.0  
                                                  Max.   :49892   Max.   :3.0  
   shape_leng      
 Min.   : 0.00026  
 1st Qu.: 0.17739  
 Median : 0.38681  
 Mean   : 0.72098  
 3rd Qu.: 0.80827  
 Max.   :73.35436  

Check the output visually:

plot(vectorImport)