Merging layers via Python OGR ExecuteSQL

Question

I have two tables with a common ID field that I would like to merge.

Is it possible to do this through python OGR? I am attempting to use the ExecuteSQL() command but am getting an error that table2 doesn't exist.

Here is my code:

connection = ogr.Open(connString)
connection2 = ogr.Open(connString2)
layer1 = connection.GetLayer()
layer2 = connection2.GetLayer()
newlayer = connection.ExecuteSQL("SELECT table1.field1, table1.field2, table2.field2 FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1", dialect = "SQLITE")

Is this something that can be done through OGR? Or should I be looking into something else?

Answer 1

You have two separate datasources and your join does not work across them. There may be other solutions but a simple one is to combine the datasources into one with OGR VRT http://www.gdal.org/drv_vrt.html.

Lets take shapefile a with attributes "id" and "a", and shapefile b with attributes "id" and "b". Wrap them with VRT file ab.vrt

<OGRVRTDataSource>
 <OGRVRTLayer name="a">
 <SrcDataSource>a.shp</SrcDataSource>
 </OGRVRTLayer>
 <OGRVRTLayer name="b">
 <SrcDataSource>b.shp</SrcDataSource>
 </OGRVRTLayer>
</OGRVRTDataSource>

Test with ogrinfo

ogrinfo ab.vrt
INFO: Open of `ab.vrt'
 using driver `OGR_VRT' successful.
1: a (Point)
2: b (Point)

Good, we have two layers in one source. Now use the SQL that you provided for ogrinfo

ogrinfo -dialect sqlite -sql "SELECT a.a, b.b FROM a INNER JOIN b ON a.id=b.id" ab.vrt
INFO: Open of `ab.vrt'
 using driver `OGR_VRT' successful.
Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
a: String (0.0)
b: String (0.0)
OGRFeature(SELECT):0
 a (String) = value from a.a
 b (String) = value from b.b

We have one feature with attributes "a" and "b".

Now just use the SQL part in your Python code and you should get the desired result.

• JamieTasker: Excellent. Hadn't thought of using a VRT file. Thank you for your help.