4

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.

  • Try sf::st_read – mdsumner Mar 15 '17 at 12:08
4

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)

enter image description here

  • Thank you, this works perfect! But how can i subset (select a subset of features in the layer) before writing it to vectorImport? – parallax Mar 15 '17 at 13:30
  • 2
    AFAIK there isn't an option to include an SQL query or spatial extent to readOGR. Why not create (spatial) subsets in Spatialite or ogr2ogr? – dmci Mar 15 '17 at 13:45
  • yes taht would also work! – parallax Mar 15 '17 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.