Running clustering queries

[Max Pyziur]

On Thu, 28 Mar 2024, Gary Turner wrote:

>
> 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.

So, I create a table from this data that I scraped (about 80k U.S. filling 
stations). The lat and long fields are floats.

I then issue these two commands:
ALTER TABLE shapefiles.atp_filling_stations ADD COLUMN geom 
geometry(Point, 4326);

UPDATE shapefiles.atp_filling_stations SET geom = 
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

So the SRS is 4326, right?

Max


>> 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
>>> 
>