I would like to select everything from one shapefile based on matching ID's in another shapefile. But I am unsure about how to proceed with the syntax using Python.

Mainly because .ExecuteSQL('sqlstring') is applied to a single data source, after that is loaded with the driver.Open('filePath') command. So something like this below doesn't function:

inFile0 = r'C:\data\buildings\buildingPoints.shp'
inData0 = driver.Open(inFile0, 1)
inLayer0 = inData0.GetLayer()

inFile1 = r'C:\data\buildings\buildingPolygons.shp'
inData1 = driver.Open(inFile1, 1)
inLayer1 = inData1.GetLayer()

sql = "SELECT * FROM buildingPolygons WHERE FEAT_ID IN (SELECT FEAT_ID FROM buildingPoints WHERE NET = %s)" % sampleID

samplePolygons = inData1.ExecuteSQL(sql)

When I create two separate sql statements inside a script running sequentially, where the first selects the relevant points from the points dataset and the second selects the building polygons based on matching the IDs with the selected building points, the result is empty:

sql0 = "SELECT" * FROM buildingPoints WHERE NET = %s" % sampleID
samplePoints = inData0.ExecuteSQL(sql0)

sql1 = "SELECT" buildingPolygons.* FROM buildingPolygons, samplePoints, WHERE buildingPolygons.FEAT_ID = samplePoints.FEAT_ID"
samplePolygons = inData1.ExecuteSQL(sql1)

How could I make this work? Or should I look into using the subprocess module and run ogr2ogr using as input a .vrt of the two shapefiles and apply the sql there?

  • i haven't used ExecuteSQL -- but seems like it would return a list of values? - would the second sql statement then accept a list? seems like it would need to be a loop, or maybe something like WHERE buildingPolygons.FEAT_ID IN (samplePoints.FEAT_ID)? Jun 15, 2017 at 12:12


Your Answer

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

Browse other questions tagged or ask your own question.