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 <p...@brama.com>
Sent: Wednesday, March 27, 2024 5:33 PM
To: postgis-users@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
p...@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
