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 <[email protected]> on behalf of Nicolas Ribot <[email protected]> Reply-To: PostGIS Users Discussion <[email protected]> Date: Monday, December 3, 2018 at 2:51 PM To: PostGIS Users Discussion <[email protected]> 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 <[email protected]<mailto:[email protected]>> 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 [email protected]<mailto:[email protected]> 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=>
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
