On Thu, Mar 28, 2024 at 9:05 AM Regina Obe <l...@pcorp.us> wrote: > > > 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.
I stumbled into this issue today. The lack of 9311 in spatial_ref_sys. Is a fix for this coming or is the spatial_ref_sys just known to be stale and not getting updated with updated postgis releases? thanks daryl
