<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Thanks Arnaud,</p>
<p>This is a very useful answer. I will try the ST_Intersects option
you propose.<br>
</p>
<p>Minor correction to the link provided, which should read:</p>
<p><a class="moz-txt-link-freetext" href="https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgis">https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgis</a></p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 22-10-2021 om 13:52 schreef Arnaud
L.:<br>
</div>
<blockquote type="cite"
cite="mid:34358ed2-4c1d-7f2f-8f9e-37b96fae3828@codata.eu">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
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"
moz-do-not-send="true">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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>