Running clustering queries

[Regina Obe]

> Depends where you got the data, but generally if they give you longitude
> latitude Columns, then 4326 is a safe assumption and looks like you created
> the geometry right too.
> 
> To Gary's point, 4326 is degree based, so no wonder you aren't getting any
> meaningful answers since 1000 would cover the whole world.
> 
> 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;
> 
> For US data, I have pretty good luck with US National Atlas Equal Area --
> SRID=2163 which is a meter based coordinate system generally good enough
> for accurate measurement.
> Web Mercator is another but that is really bad for measure preserving
> 
> But SRID=2163 is not common for display on maps etc, so I usually only use it
> for good enough measurement preserving or as a functional index, not for
> storage.
> 
> I think your dataset is small enough that you can get away with on the fly
> projection like below
> 
> 
> SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps :=
> 0.3048*500, 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;
> 

Slight amendment.  I guess 2163 is deprecated these days https://spatialreference.org/ref/epsg/2163/ , so the replacement is  9311

https://spatialreference.org/ref/epsg/9311/

Which I can't find in our spatial_ref_sys table (guess our table needs an update), but you can add with the below
as long as you are using PostGIS 2.5 or higher and PROJ 7 or higher, it's going to relegate all the details to PROJ anyway
So the other columns will not be needed except these 3.

INSERT INTO spatial_ref_sys(srid, auth_name, auth_srid)
VALUES(9311, 'EPSG', 9311);


But anyway either should work I think with 2163 there was some issue with one of the proj settings which wouldn't impact
your measurement but would affect proper display.