Running clustering queries

[Gary Turner]

On 28/03/2024 12:32 pm, Max Pyziur wrote:
> On Wed, 27 Mar 2024, Regina Obe wrote:
>
>> That's the function I would have suggested.
>>
>> What query are you trying to run and what is the spatial reference 
>> system id
>> for your data.
>>
>> I'm guessing the issue might be your units of measure.  If you want 
>> to do
>> 500 ft you'd be best using a meter or mile based spatial reference 
>> system.
>
> Here are two examples that I tried:
> SELECT city, state, ST_ClusterDBScan(geom, eps := 1000, minpoints := 
> 1) OVER () AS cluster
> FROM shapefiles.atp_filling_stations aa
> WHERE aa.amenity = 'fuel'
> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;
>
>
> SELECT ST_ClusterKMeans(geom, 1000) OVER () as cluster_id
> FROM shapefiles.atp_filling_stations
> GROUP BY cluster_id;

But what's the SRS of 'geom'?

If it's say WGS84, which is a degree based SRS, 1000 will be 1000 degrees!

I don't use archaic units, sorry, so I don' know if there are any foot 
based spatial reference systems.

> Neither offers something that seems useful.
>
> Thank you,
>
> Max
>
>
>
>>> -----Original Message-----
>>> From: Max Pyziur <pyz at brama.com>
>>> Sent: Wednesday, March 27, 2024 5:33 PM
>>> To: postgis-users at lists.osgeo.org
>>> Subject: Running clustering queries
>>>
>>>
>>> Greetings,
>>>
>>> I have a dataset of filling stations with point geometries (about 
>>> 80k U.S.
>> filling
>>> stations)
>>>
>>> I'm trying to find/make clusters of this data (with a criteria like say
>> minimum
>>> of 3 stations w/in 500 feet of each other.
>>>
>>> I've found these webpages; but as yet I can't produce any sort of
>> meaningful
>>> results.
>>>
>>> I would appreciate any and all guidence. Thank you.
>>>
>>> Max Pyziur
>>> pyz at brama.com
>>>
>>>
>>> ##########
>>> Webpages:
>>> Examples Of Spatial Clustering With PostGIS - February 28, 2024
>>> https://mapscaping.com/examples-of-spatial-clustering-with-postgis/
>>>
>>> ST_ClusterDBSCAN
>>> https://postgis.net/docs/ST_ClusterDBSCAN.html
>>>
>>> PostGIS Clustering with DBSCAN | Crunchy Data Blog
>>> https://www.crunchydata.com/blog/postgis-clustering-with-dbscan
>>>
>>> clustering - PostGIS nest ST_ClusterKMeans on results of 
>>> ST_ClusterKMeans
>>> - Geographic Information Systems Stack Exchange
>>> https://gis.stackexchange.com/questions/396029/postgis-nest-st-
>>> clusterkmeans-on-results-of-st-clusterkmeans
>>