More

Postgres/PostGIS: Dissolve multiple fields with COUNT (Point shape)


I have loaded point .shp feature classes into Postgres 9.3 db, and enabled postgis,plpgsql,postgis_topology extensions.

I'm looking to dissolve multiple fields within a single table 'TABLE1' and add a COUNT or similar field to the tabular output. The feature class has more than 12Million points, so Esri understandably chokes, and QGIS only allows me to dissolve based on one field at a time.

Start table:

The summary table I am trying to generate:

So, I'm looking to summarize across all 3 fields (ORG, PROTOCOL, MAP) and then also generate the 'Cnt_Map' field (or similar) which denotes the COUNT of each MAP.

Is there a specific SQL command or other query I can use to run this type of report?

Grazie


There shouldn't be any reason why you can't run a Frequency on the attributes of a 12m feature shapefile with ArcGIS, but the SQL query equivalent is a basic GROUP BY:

SELECT org, protocol, map, count(*) Cnt_map FROM table1 GROUP BY org, protocol, map ORDER BY org, protocol, map

You could even order by the frequency in the computed column so that most frequent occurrences were first:

SELECT org, protocol, map, count(*) Cnt_map FROM table1 GROUP BY org, protocol, map ORDER BY Cnt_map desc