Running clustering queries
Max Pyziur
pyz at brama.com
Thu Mar 28 07:09:26 PDT 2024
On Thu, 28 Mar 2024, Regina Obe 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.
Thank you to everyone for your reply and recommendations.
My work in GIS is occasional beginning sometime in 2008. When I come
across a dataset of interest that solely has latitude/longitude
coordinates for a point, I store it in 4326.
Looking through the queries that I have built and use in QGIS, for the
U.S./North America, I issue a query that transforms the geometry to 2163,
effectively
SELECT st_transform(bb.geom, 2163)
(for Europe the SRS is 3035; do tell if there is a more appropriate
SRS).
My source for the current dataset that I am trying to assemble is
https://www.alltheplaces.xyz/. The archive is a number of *.geojson files.
I ran a few transformations in python of the ones that I assumed had
filling stations. I then combined and am wrangling those results into one
dataset.
I'll work with your recommendations, and possibly follow-up.
Thank you again,
Max
More information about the postgis-users
mailing list