1

I have around 5 million points that I want to cluster. I'm having trouble achieving both finding correct results and also doing so within a reasonable amount of time. I have described this problem recently in another question, but I don't think it's necessarily relevant to my question here, so I won't reiterate.

I have these points projected in 3857, which uses units of meters. I attempted to run DBSCAN with: select city_id, addresses.id, ST_AsText(geom), ST_Srid(geom), ST_ClusterDBSCAN(addresses.geom, eps:= 1000, minpoints := 5) over (partition by city_id) from addresses;

But results took many hours to compute, and didn't even seem correct. To test whether or not the distances in the clusters made sense, I tried running again with an impossible small eps of 1 (meter???). As I understand it, ST_ClusterDBSCAN uses the same units found in the projection, which in this case should be meters. Is that right?

As you can see below, many points which are much further than 1m apart are clustered together. Each partition formed an entirely distinct cluster, i.e., all points within a given city were clustered together. So every window was made to form just one giant cluster.

# select city_id, addresses.id, ST_AsText(geom), ST_Srid(geom), ST_ClusterDBSCAN(addresses.geom, eps:= 1, minpoints := 5) over (partition by city_id) from addresses limit 20;
 city_id |  id  |           st_astext            | st_srid | st_clusterdbscan
---------+------+--------------------------------+---------+------------------
       1 | 1808 | POINT(-113.9549203 43.3291207) |    3857 |                0
       1 | 1818 | POINT(-113.9464464 43.305985)  |    3857 |                0
       1 | 1823 | POINT(-113.9429071 43.2931382) |    3857 |                0
       1 | 1828 | POINT(-113.9549092 43.3235844) |    3857 |                0
       1 | 1833 | POINT(-113.9477753 43.2978559) |    3857 |                0
       1 | 1838 | POINT(-113.9449545 43.2867464) |    3857 |                0
       1 | 1843 | POINT(-113.9385923 43.2881673) |    3857 |                0
       1 | 1848 | POINT(-113.9446156 43.300826)  |    3857 |                0
       1 | 1853 | POINT(-113.9388979 43.3158177) |    3857 |                0
       1 | 1858 | POINT(-113.937484 43.3158191)  |    3857 |                0
       1 | 1863 | POINT(-113.9361588 43.3152742) |    3857 |                0
       1 | 1868 | POINT(-113.9361566 43.3142217) |    3857 |                0
       1 | 1873 | POINT(-113.93616 43.3131712)   |    3857 |                0
       1 | 1878 | POINT(-113.9374185 43.3132743) |    3857 |                0
       1 | 1883 | POINT(-113.9395853 43.3146622) |    3857 |                0
       1 | 1888 | POINT(-113.9395952 43.3135964) |    3857 |                0
       1 | 1893 | POINT(-113.936911 43.3150997)  |    3857 |                0
       1 | 1898 | POINT(-113.9369126 43.3139828) |    3857 |                0
       1 | 1903 | POINT(-113.938847 43.3139914)  |    3857 |                0

Why are these points being clustered together?

I'm a beginner in GIS so I struggle with jargon and articulating the right questions. My goal is to take ~5 million US locations and cluster them into groups of locations that are within 10km of each other. If my approach is totally wrong, what's a performant approach I can take instead?

9
  • 2
    I don't think those points are in 3857, looks more like 4326, but if they are correct then they are much less than a metre apart
    – Ian Turton
    Jan 5 at 20:08
  • How can you tell what projection it is just based on looking at the point? I think this may be related to the root of my misunderstanding
    – vaer-k
    Jan 5 at 20:38
  • Isn't that just (lon, lat) in the point? And isn't that an independent value from whatever projection is used?
    – vaer-k
    Jan 5 at 20:49
  • coordinates in 3857 look something like -9721337.5902, 4783008.0591. Since your coordinates are lat/lon, you're using a geographic coordinate system (not projected) - the most common is 4326, hence the comment from @IanTurton.
    – jbalk
    Jan 5 at 22:18
  • 2
    If you want to use meters, you have the right idea in using a different projection. You just did it incorrectly. You need to actually transform the geometry in order to use a different srid. So instead of st_setsrid(geom,3857), you need to use st_setsrid(st_transform(geom,3857),3857)
    – jbalk
    Jan 5 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.