0

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.

  • For speeding up the SQL part you should add subquery that is utilizing the r-tree index. Read this blog erouault.blogspot.com/2017/03/…. Even it is about GeoPackage the r-tree index is the same. You just need to edit the query to use correct table and column names. Otherwise SQLite is slow with transactions. Does your code make an own transaction for each row? You can also compare how fast ogr2ogr does the job if you use your query as -sql parameter. – user30184 Sep 24 '18 at 9:01
  • I'm just testing out ogr2ogr now. As for the transaction, it should only make one big one at the end. – Yjelza Sep 24 '18 at 9:15

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.