Continuing on clustering queries

[Max Pyziur]

Thank you again; results are starting to come in.

Apologies for starting a new thread; but the other one was getting messy.

Here are the queries that I am testing:
SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps := 
0.3048*5000, minpoints :=1) OVER () AS cluster_id
INTO TEMP aa_zz
FROM shapefiles.atp_filling_stations aa
WHERE aa.amenity = 'fuel'
AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;

SELECT cluster_id, COUNT(*) FROM aa_zz GROUP BY 1 ORDER BY 2 DESC LIMIT 
10;

SELECT state,  cluster_id, COUNT(*) FROM aa_zz GROUP BY 2, 1 ORDER BY 3 
DESC LIMIT 20;

SELECT city, state,  cluster_id, COUNT(*) FROM aa_zz GROUP BY 3, 2, 1 
ORDER BY 4 DESC LIMIT 20;


I receive the following results:
SELECT 76023
  cluster_id | count
------------+-------
        8467 |   422
        3888 |   371
        5701 |   249
        3351 |   177
        1111 |   141
        8352 |   120
        1610 |   107
        1812 |    90
        1824 |    86
       14232 |    81
(10 rows)

  state | cluster_id | count
-------+------------+-------
  MI    |       8467 |   422
  NY    |       3888 |   371
  IL    |       5701 |   245
  NY    |       3351 |   177
  CO    |       8352 |   110
  TX    |       1610 |   101
  NY    |       1824 |    86
  DC    |       1111 |    82
  IN    |      14748 |    78
  TX    |      14297 |    75
  MO    |       3661 |    74
  CA    |      11272 |    74
  MI    |        462 |    71
  TX    |      14232 |    70
  TX    |      19326 |    69
  TX    |       1812 |    68
  FL    |      13693 |    68
  TX    |      16824 |    68
  CA    |      14006 |    66
  IL    |       2727 |    64
(20 rows)

      city     | state | cluster_id | count
--------------+-------+------------+-------
  DETROIT      | MI    |       8467 |   147
  CHICAGO      | IL    |       5701 |   104
  Austin       | TX    |       1610 |   101
  Detroit      | MI    |       8467 |    85
  BROOKLYN     | NY    |       3888 |    77
  Chicago      | IL    |       5701 |    75
  Fresno       | CA    |      11272 |    73
  Brooklyn     | NY    |       3888 |    71
  WASHINGTON   | DC    |       1111 |    66
  HOUSTON      | TX    |      14297 |    64
  St. Louis    | MO    |       3661 |    63
  HOUSTON      | TX    |      14232 |    63
  DENVER       | CO    |       8352 |    54
  BRONX        | NY    |       3351 |    53
  LOS ANGELES  | CA    |      14006 |    50
  Bakersfield  | CA    |       1777 |    49
  INDIANAPOLIS | IN    |      14748 |    44
  Bronx        | NY    |       3351 |    42
  Amarillo     | TX    |        622 |    41
  Austin       | TX    |       1601 |    38
(20 rows)


Obviously, the case-based aspect of the city name can be cancelled with a 
function such as UPPER(city).

I also am generous in the radius dimension setting it at 5k feet.

But my next question is how do position the cluster_id on a map w/o 
referencing the city, and then tying that out to a geometry that I have 
stored for the city?

Thank you again,

Max