<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
Hi Marco<br>
<br>
<div class="moz-cite-prefix">Le 22/10/2021 à 13:31, Marco Boeringa a
écrit :
</div>
<blockquote type="cite"
cite="mid:55a58c5e-d29b-aedc-ded8-ff2477925426@boeringa.demon.nl">
<pre class="moz-quote-pre" wrap="">I currently use the following two queries. This first query creates a
table with all unique 'pairs' of polygons as identified by their ID
using 'ST_DWithin with a 0 range from the <MY_TABLE1> original polygon
table. The second one deduplicates the two columns of the first table to
create a unique list of IDs.
</pre>
</blockquote>
I think I would write this in a single query, using an EXISTS
subquery instead of a join step and a dedup step :<br>
<br>
SELECT tbl.id <br>
FROM <MYTABLE1> AS tbl<br>
WHERE EXISTS (<br>
SELECT 1 <br>
FROM <MYTABLE1> AS subq <br>
WHERE subq.id != tbl.id AND
ST_Intersects(tbl.<GEOMETRY_COLUMN>
, subq.<GEOMETRY_COLUMN>)<br>
);<br>
<br>
No need to DISTINCT or anything here, the result should already be
correct.<br>
<br>
<blockquote type="cite"
cite="mid:55a58c5e-d29b-aedc-ded8-ff2477925426@boeringa.demon.nl">
<pre class="moz-quote-pre" wrap="">From the documentation of
ST_DWithin, it is clear it is already optimized to use the spatial index
and bounding boxes to speed up the search, so I guess there is nothing
more I can do. It is properly using multiple parallel workers in
PostgreSQL as well for creating the table from a look in PgAdmin.
</pre>
</blockquote>
<br>
ST_DWithin(a,b,0) is logically equivalent to ST_Intersects(a,b), but
as per this post on SE, ST_Intersects might be faster due to
internal optimizations :<br>
<a class="moz-txt-link-freetext" href="https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgiseo.org/mailman/listinfo/postgis-users">https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgiseo.org/mailman/listinfo/postgis-users</a><br>
<br>
<br>
Regards<br>
-- <br>
Arnaud<br>
<blockquote type="cite"
cite="mid:55a58c5e-d29b-aedc-ded8-ff2477925426@boeringa.demon.nl">
</blockquote>
<div id="grammalecte_menu_main_button_shadow_host" style="width:
0px; height: 0px;"></div>
</body>
</html>