Generic SQL aggregation

generic_sql_gis_aggregation.sql

## create summary table

CREATE TABLE temp_table AS 
(select 
	 	count(id) AS features_per_ADMIN, 
		avg(master_table.thing_to_average) AS avg_thing_to_average, 
		sum(master_table.thing_to_sum) AS sum_thing_to_sum, 
		count(master_table.thing_to_count) AS count_thing_to_count, 
		COUNT(DISTINCT master_table.thing_to_count_distinct) AS cntd_thing_to_count_distinct, 	
		## ...add as many summary fields as you want here

		master_table.ADMIN AS ADMIN_alias
	from master_table JOIN gis.ADMIN on master_table.ADMIN_pcode = ADMIN.pcode group by master_table.district);

## join P-codes to summary table (can't put them in the aggregate functions above becuase they're VARCHAR)
## join on feature name -- but only where they've already successfully been joined by p-code

CREATE TABLE ADMIN_summary AS 
SELECT 
master_table.district,
master_table.ADMIN_pcode AS pcode,
temp_table.*
FROM master_table
RIGHT JOIN temp_table
ON temp_table.ADMIN_alias = master_table.ADMIN;

## add an id

ALTER TABLE ADMIN_summary ADD COLUMN id BIGSERIAL;

## delete duplicates

DELETE FROM ADMIN_summary
WHERE id in (SELECT id
		FROM (SELECT id,
				ROW_NUMBER() OVER (partition BY ADMIN, pcode ORDER BY ADMIN) AS rnum
			FROM ADMIN_summary) t
		WHERE t.rnum > 1);

## primary key

ALTER TABLE ADMIN_summary ADD PRIMARY KEY (pcode);

## add the geom. Assumes your basic geometries are under schema "gis" (a good best practice)

CREATE TABLE gis.ADMIN_summary_geom AS
SELECT 
ADMIN_summary.*,
gis.ADMIN.geom
FROM gis.ADMIN
RIGHT JOIN ADMIN_summary
ON gis.ADMIN.pcode = ADMIN_summary.pcode;

## set the projection

SELECT UpdateGeometrySRID('ADMIN_summary_geom', 'geom', new_epsg_number);

## transform to that projection

ALTER TABLE gis.ADMIN_summary_geom
	ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, old_epsg_number)
		USING ST_TRANSFORM(ADMIN_summary_geom.geom,old_epsg_number)::geometry(MULTIPOLYGON,new_epsg_number);