PostGIS HAP Aggregate Queries

postgis_aggregate_queries.md

PostGIS HAP Aggregate Queries

Per Robert's request, here are some queries that take many rows of data, aggregate them based on the location (unique counts) or sector (unique sectors), then join the aggregates to the geometries at different levels.

ADMIN 3 QUERY: PROJECT COUNTS

WITH hap_counts AS (
	SELECT 
	hap_data.loc_admin3 AS name,
	hap_data.p_code AS pcode,
	COUNT(hap_data.project_name) AS numberofprojects
	FROM hap_data
	GROUP BY hap_data.loc_admin3, hap_data.p_code
	)
SELECT
row_number() OVER() AS gid,
haiti_geom3.geom AS geom,
hap_counts.name,
hap_counts.pcode,
hap_counts.numberofprojects
FROM hap_counts
RIGHT JOIN haiti_geom3
ON hap_counts.pcode = haiti_geom3.id_adm3

ADMIN 3 QUERY: SECTOR COUNTS

WITH hap_counts AS (
	SELECT 
	hap_data2.p_code AS pcode,
	COUNT(DISTINCT hap_data2.sector_primary) AS numberofsectors
	FROM hap_data2
	GROUP BY hap_data2.p_code
	)
SELECT
row_number() OVER() AS gid,
haiti_geom3.geom AS geom,
hap_counts.pcode,
hap_counts.numberofsectors
FROM hap_counts
RIGHT JOIN haiti_geom3
ON hap_counts.pcode = haiti_geom3.id_adm3

AGGREGATE SECTOR BUDGETS (non-GIS PostGres Query)

Sector_Primary,
SUM(TOTAL_LOP_BUDGET) AS sector_sum
FROM hap_data2
GROUP BY Sector_Primary;

ADMIN 2 QUERY: PROJECT COUNTS

WITH hap_counts AS (
	SELECT 
	hap_data.loc_admin2 AS name,
	CAST(CAST(hap_data.p_code_admin1 AS varchar(10)) || CAST(hap_data.p_code_admin2 AS varchar(10)) AS int) AS pcode,
	COUNT(hap_data.project_name) AS numberofprojects
	FROM hap_data
	GROUP BY name, pcode
	)
SELECT
row_number() OVER() AS gid,
haiti_geom2.geom AS geom,
hap_counts.name,
hap_counts.pcode,
hap_counts.numberofprojects
FROM hap_counts
RIGHT JOIN haiti_geom2
ON hap_counts.pcode = haiti_geom2.id_adm2

GNPD GEOMETRY QUERY FROM PROJECT DATA

WITH gnpd_shp AS (
	SELECT DISTINCT
	hap_data.loc_admin3 AS name,
	hap_data.p_code AS pcode
	FROM hap_data
	WHERE hap_data.pledge_master = 'GNPD'
	),
	pre_union AS (
	SELECT
	row_number() OVER() AS gid,
	haiti_geom3.geom AS geom,
	gnpd_shp.name,
	gnpd_shp.pcode
	FROM gnpd_shp
	INNER JOIN haiti_geom3
	ON gnpd_shp.pcode = haiti_geom3.id_adm3
	GROUP BY gnpd_shp.name, gnpd_shp.pcode, geom, gid
	)
SELECT
row_number() OVER() AS gid,
ST_Union(pre_union.geom) AS geom
FROM pre_union