[GIS] Multiple input shapefiles for ogr2ogr SQLite spatial operations

ogr2ogrshapefilespatialitesqlsqlite

I'm running up against an issue doing a spatial operation requiring multiple input shapefiles using ogr2ogr and SQLite.

I have:

ogr2ogr -f "ESRI Shapefile" output.shp input.shp -dialect sqlite -sql "CREATE VIRTUAL TABLE 'overlap' USING VirtualOGR('overlap.shp'); SELECT ST_SymDifference('input','overlap')"

Where 'output' is my output shape, 'input' is one input shape and 'overlap' is the other input shape. I can't seem to get any output, or if I do, it's just a DBF file? Any assistance?

I suspect it's to do with the Virtual Table not having any geometry but I can't seem to do a "RecoveryGeometryColumn" that actually works.

Both 'input' and 'overlap' have the same projection and overlap nicely in QGIS.

I'm on version "GDAL 1.10.0, released 2013/04/24"

Thanks!

Edit: Fixed typos in my sqlite command

Edit2:

ogr2ogr -f "ESRI Shapefile" output.shp input.shp -dialect sqlite -sql "SELECT ST_SymDifference(GEOMETRY,'overlap.sqlite.GEOMETRY'),* FROM input"

Just returns the input shape. I think I've completely missed something obvious…

Best Answer

Firstly, wrap your input.shp and overlap.shp into an OGR VRT file (e.g. test.vrt) :

<OGRVRTDataSource>
    <OGRVRTLayer name="input">
        <SrcDataSource>input.shp</SrcDataSource>
    </OGRVRTLayer>
    <OGRVRTLayer name="overlap">
        <SrcDataSource>overlap.shp</SrcDataSource>
    </OGRVRTLayer>
</OGRVRTDataSource>

Then you can execute:

ogr2ogr output.shp test.vrt -dialect sqlite -sql "SELECT ST_SymDifference(g1.geometry,g2.geometry) AS geometry FROM input AS g1, overlap AS g2"
Related Question