[gdal-dev] ogr2ogr intersect very slow at command line with large sqlite input

[Stephen Woodbridge]

On 4/10/2017 5:51 PM, CTL101 wrote:
> I've removed the intersection part after realising that I didn't need it as
> the intersection geometry is already calculated in the input  (slow hand
> clap me!)
> 
> So after investigating a bit more I have:
> ogr2ogr -gt unlimited -nlt PROMOTE_TO_MULTI25D --config OGR_SQLITE_CACHE
> 10240 -f SQLite -dsco SPATIALITE=YES output.sqlite input.sqlite -nln
> outputtable -dialect sqlite -sql "SELECT A.* FROM inputtable1 A, inputtable2
> B WHERE ST_Intersects(A.geometry, B.geometry) AND A.ogc_fid IN (SELECT
> A.ogc_fid FROM SpatialIndex WHERE f_table_name = 'inputtable1' AND
> search_frame = B.geometry)"
> 
> This is now running on the large tables, and still seems really slow. But it
> may actually finish this time so who knows. I have looked at the link that
> Jukka suggests, and perused the forum but these seem like very complex
> programming based solutions to elaborate problems. Surely there is a simple
> way to get more speed out of gdal - especially now that the heavy lifting
> has been removed almost altogether from my relatively simple query?

There are a lot of other factors on the speed, like:

how many interactions between the table A and B?
how many points are in each feature?

If you have postgresql installed you could load you two tables there and 
try similar queries to get a comparison of performance.

-Steve

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus