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:

enter image description here

The summary table I am trying to generate:

note MAP 'italy' has different PROTOCOLS

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?


  • 1
    There doesn't seem to be any spatial component to this query, so I don't understand why a simple GROUP BY can't be used.
    – Vince
    Aug 14 '14 at 15:32

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
  • FYI - Frequency tool requires Esri license higher than View. So, I'm not able to test that approach specifically. Will test the GROUP_BY answer and follow up.
    – bjmarra
    Aug 14 '14 at 23:46
  • Tested the order by Frequency query in Postgresql and it works as expected. Saved a huge amount of time. Grazie!
    – bjmarra
    Aug 15 '14 at 18:57
  • How to summarise with "FIRST" Method as statistics field in Dissolve option. Jan 14 '15 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.