This is a working example of how it would be possible to set up code that would automatically allocate for example housing sites to School Catchment areas. It could also be used to list constraints relevant to particular housing sites. This is more complicated in that it will indicate what percentage of the housing site is within which catchment areas and therefore allows for a single housing site being in multiple catchment areas. I am planning on coming back and expanding on this post. In this respect it represents a refinement of this Post
So we need two tables
t001asites which has a geometry field called geom
and another table which will be the catchments table called
t002bcatchments which has a geometry field called geom.
Both tables must have a serial primary key of pkid and both tables must be polygon data and the geom field MUST be defined as polygon and NOT multipolygon.
Air code is as follows.
-
1. Create table containing digitised polygons of housing sites.
2. Create table containing digitised polygons of catchments.
3. Measure the area of the housing sites and place that value in an area column within the housing sites table t001asites.
4. Split the housing sites by the catchment boundaries ensuing that each split polygon inherits the catchment it was split by.
5. Re-measure the areas of these split sites and add an area column to store the new calculations.
6. Divide figure obtained in 5. by figure obtained in 3 which will indicate the proportion of the housing site is in which catchment.
7. Perform a least remainder method on the individual sites grouped by their original housing sites to ensure the proportions sum to 1.
So to the code
BEGIN; SET LOCAL check_function_bodies TO FALSE; CREATE OR REPLACE FUNCTION part01catchjunctionmaker() returns void as $$ Alter table t001asites add column area integer; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part02catchjunctionmaker() returns void as $$ Update t001asites set area=ST_Area(geom); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part022catchjunctionmaker() RETURNS void AS $$ DROP TABLE IF EXISTS t200; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part03catchjunctionmaker() RETURNS void AS $$ CREATE TABLE t200 AS select a.pkid as t001pkid, b.pkid as t002pkid, a.area as t001area, ST_intersection(a.geom, b.geom) as geom FROM t001asites a, t002bcatchments b; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part04catchjunctionmaker() RETURNS void AS $$ ALTER TABLE t200 add column pkid serial primary key, add column area integer,add column proportion decimal (10,9); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part06catchjunctionmaker() RETURNS void AS $$ UPDATE t200 SET area=ST_Area(geom); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part07catchjunctionmaker() RETURNS void AS $$ DELETE from t200 where area=0 or null; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part08catchjunctionmaker() RETURNS void AS $$ UPDATE t200 SET proportion= cast(area as decimal)/cast(t001area as decimal) WHERE area > 0; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part088catchjunctionmaker() RETURNS void AS $$ DROP table IF EXISTS t201; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part09catchjunctionmaker() RETURNS void AS $$ Create table t201 as Select pkid,t001pkid,t002pkid, t001area, area, proportion, sum(proportion) OVER (PARTITION BY t001pkid ORDER BY t001pkid, proportion) as cum_proportion FROM t200 ORDER BY t001pkid, proportion; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part10catchjunctionmaker() RETURNS void AS $$ Alter table t201 add column value decimal (14,9), Add column valuerounded integer, Add column cumulvaluerounded integer, Add column prevbaseline integer, Add column roundproportion integer; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part11catchjunctionmaker() RETURNS void AS $$ UPDATE t201 set value = proportion * 100; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part12catchjunctionmaker() RETURNS void AS $$ UPDATE t201 set valuerounded = round(value,0); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part13catchjunctionmaker() RETURNS void AS $$ update t201 set cumulvaluerounded = round((cum_proportion*100),0); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part14catchjunctionmaker() RETURNS void AS $$ update t201 set cumulvaluerounded=100 where cumulvaluerounded = 101; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part15catchjunctionmaker() RETURNS void AS $$ update t201 set prevbaseline = round((cum_proportion - proportion)*100); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part16catchjunctionmaker() RETURNS void AS $$ update t201 set roundproportion = (cumulvaluerounded-prevbaseline); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part17catchjunctionmaker() RETURNS void AS $$ DELETE from t201 where roundproportion=0 or null; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part18catchjunctionmaker() RETURNS void AS $$ alter table t201 add column proppercent decimal(3,2); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION part19catchjunctionmaker() RETURNS void AS $$ update t201 set proppercent = cast(roundproportion as decimal)/100; $$ LANGUAGE SQL; COMMIT;
and now a function to pull it all together;
CREATE OR REPLACE FUNCTION createcjt() RETURNS TEXT AS $BODY$ BEGIN PERFORM part01catchjunctionmaker(); PERFORM part02catchjunctionmaker(); PERFORM part022catchjunctionmaker(); PERFORM part03catchjunctionmaker(); PERFORM part04catchjunctionmaker(); PERFORM part06catchjunctionmaker(); PERFORM part07catchjunctionmaker(); PERFORM part08catchjunctionmaker(); PERFORM part088catchjunctionmaker(); PERFORM part09catchjunctionmaker(); PERFORM part10catchjunctionmaker(); PERFORM part11catchjunctionmaker(); PERFORM part12catchjunctionmaker(); PERFORM part13catchjunctionmaker(); PERFORM part14catchjunctionmaker(); PERFORM part15catchjunctionmaker(); PERFORM part16catchjunctionmaker(); PERFORM part17catchjunctionmaker(); PERFORM part18catchjunctionmaker(); PERFORM part19catchjunctionmaker(); RETURN 'process end'; END; $BODY$ LANGUAGE plpgsql;