I am having an issue with OGR's ExecuteSQL, but not with the actual SQL query.
What I am intending to do is to take a large number of polygons ~500k and find out if they intersect with a multipolygon.
The SQL query takes approximately 4 minutes (Which some may say is slow, please point out if I'm doing it wrong).
The critical issue here is, when I try to work with the results of the query, it is incredibly slow. It takes approximately 2hrs30min for it to loop through all of the results and add it to a new file.
This issue persists even when I replace the contents of the loop with a simple "pass" statement. To further the issue, trying to "print len(Intersection_Results)" takes so long I stopped the code after 5 minutes.
Please see my code below:
Multipolygon_file= ogr.Open(r"multipolygon.sqlite",1)#This file contains both the polygons and the multipolygon in two different layers SQL_COMMAND = "SELECT polygons.* FROM polygons, multipolygon WHERE ST_Intersects(polygons.geometry, multipolygon .geometry)" Intersection_Results = Multipolygon_file.ExecuteSQL(SQL_COMMAND, dialect = "sqlite") Results_File= ogr.Open( r"Results_Table.sqlite",1) Results_File_layer = Results_File.CreateLayer("Intersect1000yr_Results",projection,geom_type=ogr.wkbPolygon) laydefn = Results_File_layer.GetLayerDefn() Results_File_layer.StartTransaction() for feature in Intersection_Results : outFeature = ogr.Feature(laydefn) outFeature.SetGeometry(feature.GetGeometryRef()) Results_File_layer.CreateFeature(outFeature) Results_File_layer.CommitTransaction() Multipolygon_file.ReleaseResultSet(Intersection_Results) Results_File.FlushCache()
Just to further add to the list of other things I have tried to fix this issue: I have limited the results in the SQL query to only give me the first 1000 results so that I can check the outputs to make sure they are what I expect: a layer containing only the polygons that intersect with the multipolygon. It was exactly what I expected
I also run this EXACT query before running it with the multipolygon, I use a bounding box with the same extent of the multipolygon to cut down the data from approx 5.8m polygons to 800k. This takes approimately 1min to execute the SQL query and a further ~1min to write the results to the multipolygon.sqlite file.