[postgis-users] Improvement suggestion

Jonathan McCormack Jonathan.McCormack at fcc.gov
Mon Dec 3 13:31:55 PST 2018


In our use of PostGIS on nationwide datasets, we too struggled with similar performance issues.  In the end, we largely fell back upon the tricks mentioned by Paul (along with some pre-processing using the ArcGIS Dice tool to limit the number of vertices for any polygon).

Perhaps there's a really obvious explanation, but is there a reason that functions like ST_Intersection are not optimized on the backend to, e.g., test the relation and return geometry A without further processing when geometry A falls entirely within geometry B?  It seems like it would be pretty easy and would tend to speed up most real-world queries.

Jonathan McCormack
Attorney Advisor & Information Systems Specialist
Auctions and Spectrum Access Division, WTB
Federal Communications Commission

From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Nicolas Ribot <nicolas.ribot at gmail.com>
Reply-To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Date: Monday, December 3, 2018 at 2:51 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Improvement suggestion

Hi,

Did you cut your big polygons with st_subdivide before intersecting them ?
It usually speeds up queries by several orders of magnitude.

Nicolas

On Mon, 3 Dec 2018 at 18:41, Paul van der Linden <paul.doskabouter at gmail.com<mailto:paul.doskabouter at gmail.com>> wrote:
I indeed know that trick to eliminate an st_intersection, but that comes at the cost of a st_within.
In the test-case I'm examing now, the st_intersects and st_within both take about 40 seconds and the st_intersection takes about a minute.
So to eliminate one of the 40 seconds (and in this case even the intersection because st_within is true), a function returning the relation could speed up things
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users<https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers&d=DwMFaQ&c=y0h0omCe0jAUGr4gAQ02Fw&r=ttSzwKHPIaN3Ue2Op6mH8FkPyFpiuuXAybXGNkPA8Zw&m=1FyqHI-zvQdeWurRnP6ebNq1GOYr3W03RbRg9a1tAxc&s=5Odg0h7eiL4iybklq5xzLe801SZYbxMgl62YD8Ph0_8&e=>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181203/9616a1b6/attachment.html>


More information about the postgis-users mailing list