You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 (
SELECThap_data.loc_admin3AS name,
hap_data.p_codeAS pcode,
COUNT(hap_data.project_name) AS numberofprojects
FROM hap_data
GROUP BYhap_data.loc_admin3, hap_data.p_code
)
SELECT
row_number() OVER() AS gid,
haiti_geom3.geomAS geom,
hap_counts.name,
hap_counts.pcode,
hap_counts.numberofprojectsFROM hap_counts
RIGHT JOIN haiti_geom3
ONhap_counts.pcode=haiti_geom3.id_adm3
ADMIN 3 QUERY: SECTOR COUNTS
WITH hap_counts AS (
SELECThap_data2.p_codeAS pcode,
COUNT(DISTINCT hap_data2.sector_primary) AS numberofsectors
FROM hap_data2
GROUP BYhap_data2.p_code
)
SELECT
row_number() OVER() AS gid,
haiti_geom3.geomAS geom,
hap_counts.pcode,
hap_counts.numberofsectorsFROM hap_counts
RIGHT JOIN haiti_geom3
ONhap_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 (
SELECThap_data.loc_admin2AS name,
CAST(CAST(hap_data.p_code_admin1ASvarchar(10)) || CAST(hap_data.p_code_admin2ASvarchar(10)) ASint) AS pcode,
COUNT(hap_data.project_name) AS numberofprojects
FROM hap_data
GROUP BY name, pcode
)
SELECT
row_number() OVER() AS gid,
haiti_geom2.geomAS geom,
hap_counts.name,
hap_counts.pcode,
hap_counts.numberofprojectsFROM hap_counts
RIGHT JOIN haiti_geom2
ONhap_counts.pcode=haiti_geom2.id_adm2
GNPD GEOMETRY QUERY FROM PROJECT DATA
WITH gnpd_shp AS (
SELECT DISTINCThap_data.loc_admin3AS name,
hap_data.p_codeAS pcode
FROM hap_data
WHEREhap_data.pledge_master='GNPD'
),
pre_union AS (
SELECT
row_number() OVER() AS gid,
haiti_geom3.geomAS geom,
gnpd_shp.name,
gnpd_shp.pcodeFROM gnpd_shp
INNER JOIN haiti_geom3
ONgnpd_shp.pcode=haiti_geom3.id_adm3GROUP BYgnpd_shp.name, gnpd_shp.pcode, geom, gid
)
SELECT
row_number() OVER() AS gid,
ST_Union(pre_union.geom) AS geom
FROM pre_union