Skip to content

Instantly share code, notes, and snippets.

@rbanick

rbanick/generic_sql_gis_aggregation.sql

Last active August 29, 2015 14:24
Show Gist options
  • Save rbanick/569885234746900929b7 to your computer and use it in GitHub Desktop.
Save rbanick/569885234746900929b7 to your computer and use it in GitHub Desktop.
Download ZIP
Generic SQL aggregation
Raw
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment