[postgis-users] Improvement suggestion

[Simon (SPDBA) Greener]

Did anyone else spot that the person created his target table, created an index on that (empty) table, then inserted all the data in it using St_intersection? He should create the index afterwards.


⁣Sent from BlueMail ​

On 3 Dec. 2018, 08:41, at 08:41, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>Try the solution outlined here:
>
>https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis
>
>
>On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
>paul.doskabouter at gmail.com> wrote:
>
>> As I am working with large polygons, I'm always struggling with
>> performance, and trying to find ways to improve them.
>> F.e. I have lots of queries like:
>> SELECT ST_Intersection(table1.geom,table2.geom)
>> FROM table1
>> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>>
>> In case of large polygons this is sometimes a bottleneck, and I have
>the
>> following suggestion:
>> Create a function which returns the relation between 2 polygons
>(within,
>> intersects or disjunct) so that I can do the following:
>>
>> SELECT
>>   CASE
>>      WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
>> ST_Intersection(table1.geom,table2.geom)
>>      ELSE table1.geom
>>   END
>> FROM table1
>> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN
>(intersects,within)
>>
>> or (because ST_Relate is calculated twice in previous query):
>>
>> SELECT
>>   CASE
>>      WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>>      ELSE t1geom
>>   END
>> FROM (
>>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
>> t1geom,table2.geom AS t2geom FROM table1
>>   JOIN table2 on table1.geom && table2.geom
>> ) AS allpolies
>> WHERE relate IN (intersects,within)
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181203/30626634/attachment.html>