3

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?

2

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.

  • Excellent. Hadn't thought of using a VRT file. Thank you for your help. – JamieTasker May 8 '17 at 11:27

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.