[gdal-dev] ogr2ogr intersect very slow at command line with large sqlite input
sault.don at gmail.com
Mon Apr 10 16:52:13 PDT 2017
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,
>> 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
>> Jukka suggests, and perused the forum but these seem like very complex
>> programming based solutions to elaborate problems. Surely there is a
>> way to get more speed out of gdal - especially now that the heavy
>> 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.
> This email has been checked for viruses by Avast antivirus software.
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
More information about the gdal-dev