<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi,</p>
<p>As I understand it now, Darafei's suggestion essentially puts the
geometries on a globe by transforming to EPSG:4978, and then doing
straight line distance calculations between geometries. Since the
cluster distance, depending on the data of course, is usually very
small compared to the earth, the error of not accounting for the
sphere/spheroid should be low. E.g., if you wanted to cluster
buildings like in the example page of the PostGIS documentation,
than 10m max eps already creates clusters of > 1k records in
some areas. In addition, the whole principal of clustering does
not care much about exact geodesic distances, or not at all, just
that with Darafei's suggestion, the data can more easily span
large surface areas without clustering results negatively being
influenced by a projection's distance distortions (Correct me if I
am wrong, Darafei).<br>
</p>
<p>An example query for creating a table with clusters, discarding
IDs that do not form part of a cluster:<br>
</p>
<p>CREATE TABLE <MY_TABLE>_clustered AS SELECT
<UNIQUE_ID_COLUMN>, cluster_id FROM (SELECT
<UNIQUE_ID_COLUMN>,
ST_ClusterDBSCAN(ST_Force3D(ST_Transform(<GEOMETRY_COLUMN>,4978)),
eps := 10 , minpoints := 5) over () AS cluster_id FROM
<MY_TABLE>) sq WHERE cluster_id IS NOT NULL;</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 21-12-2020 om 15:06 schreef Marco
Boeringa:<br>
</div>
<blockquote type="cite"
cite="mid:d0ae5c86-a065-33a9-c2dd-c4afd6ae2352@boeringa.demon.nl">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p>Hi Darafei,</p>
<p>Thanks for the suggestion. <br>
</p>
<p>I must admit I have a bit of difficulty visualizing what this
exactly does, and had to look up that EPSG:4978 projection you
mention, but do I understand it right that the solution you
suggest should work for global data sets, and isn't influenced
or limited by the usual projection distortions?</p>
<p>I also understand this is not something I could easily run
myself, because this is experimental changes in underlying
PostGIS code? Or is there a set of PostGIS commands I need to
run in specific order to get such result, e.g. is it really as
simple as maybe:</p>
<p>"ST_ClusterDBSCAN(ST_Force3D(ST_Transform(<GEOMETRY_COLUMN>,4978)))"
??<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 21-12-2020 om 12:24 schreef
Darafei "Komяpa" Praliaskouski:<br>
</div>
<blockquote type="cite"
cite="mid:CAC8Q8tLRFvK5beM5PYKKya2=VwqVJhfa5jgr8K-pddNoUi1n3g@mail.gmail.com">
<meta http-equiv="content-type" content="text/html;
charset=UTF-8">
<div dir="ltr">Hi,
<div><br>
</div>
<div>My last exercise in KMeans showed that it's enough to add
support for 3D distances instead of 2D distances in the code
and transform your geometry into EPSG:4978 (after Force3D).
That will cluster in a 3D XYZ coordinate system using
straight lines in 3D, which is usually good enough.</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Mon, Dec 21, 2020 at 2:15
PM Giuseppe Broccolo <<a
href="mailto:g.broccolo.7@gmail.com"
moz-do-not-send="true">g.broccolo.7@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<div dir="ltr">
<div dir="ltr">Hi Marco,<br>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">Il giorno dom 20 dic
2020 alle ore 10:03 Marco Boeringa <<a
href="mailto:marco@boeringa.demon.nl"
target="_blank" moz-do-not-send="true">marco@boeringa.demon.nl</a>>
ha scritto:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px
0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
Reading through the PostGIS documentation, I noticed
the <br>
"ST_ClusterDBSCAN" function takes a distance as one of
the inputs. Now <br>
the docs suggest the current algorithm only takes in
"geometry" type <br>
data. Is that true? Based on the distance input
variable, it would seem <br>
logical to have a "geography" variant as well, even if
that is a <br>
considerably slower variant considering the more
complex distance <br>
calculation.<br>
</blockquote>
<div><br>
</div>
<div>Yes, ST_ClusterDBSCAN takes just the geometry type
in input. This is because most of the algorithm</div>
<div>is implemented using utilities already existing in
GEOS for planar geometries.</div>
<div><br>
</div>
<div>Adding the support for the geography type would
mean to re-implement the algorithm specifically for</div>
<div>spherical objects, as you mentioned.</div>
<div><br>
</div>
<div>Regards,</div>
<div>Giuseppe.<br>
</div>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org"
target="_blank" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br clear="all">
<div><br>
</div>
-- <br>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">Darafei "Komяpa" Praliaskouski<br>
OSM BY Team - <a href="http://openstreetmap.by/"
target="_blank" moz-do-not-send="true">http://openstreetmap.by/</a><br>
</div>
</div>
<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" moz-do-not-send="true">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</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>