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

[Donovan Cameron]

I've also noticed for some intersections, especially where I want to 
extract points that intersect polygons, it can be quicker to do a 
ST_Within test - given that points will usually either be within or 
outside of a polygon, or touching the boundary of a polygon.


On 2017-04-10 03:12 PM, Stephen Woodbridge wrote:
> 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
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev

-- 
Kind regards,

Donovan