<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Arnaud,</p>
<p>I have now done some preliminary testing with a > 10M records
dataset, but my first impression is that 'ST_Intersects' is not
faster than 'ST_DWithin' with range 0. <br>
</p>
<p>In fact, I have the feeling it may be about 20-25% slower (e.g.
2m48s versus for 'ST_Intersects' versus 2m07s for 'ST_DWithin' was
one result). At least, that is what I am currently seeing when I
replace the 'ST_Intersects' call with 'ST_DWithin' in your
optimized query, and this difference seems quite consistent when I
re-run this multiple times.<br>
</p>
<p>So the query below appears to be the most efficient:<br>
</p>
<p>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_DWithin(tbl.<GEOMETRY_COLUMN> ,
subq.<GEOMETRY_COLUMN>, 0)<br>
);</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 22-10-2021 om 14:07 schreef Arnaud
L.:<br>
</div>
<blockquote type="cite"
cite="mid:07138ed9-6236-7719-6597-18ed16697f5b@codata.eu">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
Le 22/10/2021 à 14:04, Marco Boeringa a écrit :<br>
<blockquote type="cite"
cite="mid:b55cca28-9cc2-6334-ad9f-49eae26753a4@boeringa.demon.nl">
<meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<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"
moz-do-not-send="true">https://gis.stackexchange.com/questions/400700/st-intersects-vs-st-dwithin-behavior-on-polygons-using-postgis</a></p>
</blockquote>
<br>
Thanks, I don't know what happened here...<br>
Also, in the EXISTS (subquery) version you don't have to rejoin to
your original table, since you are already querying your original
table.<br>
<br>
Regards<br>
--<br>
Arnaud<br>
<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>