Running clustering queries

[Max Pyziur]

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;

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
>