Running clustering queries

[Gary Turner]

On 28/03/2024 1:41 pm, Max Pyziur wrote:
> 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

Yes, (well, SRID, but who's being picky ;) which is the id of WGS84, 
which is based on degrees.

My (limited) understanding is that you would need to have reprojected 
your points to a projected SRS with a suitable unit, rather than WGS84.

As Regina said,

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

Sorry, I'm not familiar with a suitable one to suggest for your country.

Given she suggested miles, there might not be one in feet, so you will 
need to scale your 1000 feet to miles or metres, depending on the base 
unit of the SRS you use

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