Routing the water path

593 Yikai_4weeks

— 2. create topology
drop table street_vertices_pgr;
alter table street drop column source;
alter table street drop column target;
alter table street add column source integer;
alter table street add column target integer;
select pgr_createTopology(‘street’, 0.0001, ‘geom’, ‘id’); — function create table ‘street_vertices_pgr’
— 1.join building information
WITH building_updated
AS (
SELECT b.id, b.geom, b.bldg_id, b.shape_area, n.pri_neigh, st_centroid(b.geom) AS cen_pt, z.zone_type, b.shape_area*z.zone_type AS water FROM building AS b
LEFT JOIN neighboorhood AS n
ON st_within(b.geom, n.geom) — result table will be called ‘building_center’ or update ‘builidng’
LEFT JOIN zoning AS z
ON st_within(b.geom, z.geom)
),

— 3. finding nearest pt
building_final
AS (
SELECT DISTINCT ON(g1.id) g1.*,
g2.id ::INT4 As node_id,
g2.the_geom
FROM building_updated As g1, street_vertices_pgr As g2 — g1 = source table ; g2 = target table
WHERE g1.id <> g2.id
AND ST_DWithin(g1.geom, g2.the_geom, 1000)
ORDER BY g1.id, ST_Distance(g1.geom,g2.the_geom)
),

— 4. routing
route
AS (
SELECT seq,id1, id2 as node, id3 as gid, route.cost, street.geom FROM pgr_kdijkstraPath(‘
SELECT id as id,
source,
target,
st_length(geom) as cost
FROM street’, 001, array(SELECT “node_id” from building_final), false, false )
AS route
LEFT JOIN street
ON route.id3 = street.id
)

— 5. Join water usage to route
SELECT SUM(bf.water), r.gid, r.geom
FROM building_final AS bf
LEFT JOIN route AS r
ON bf.”node_id” = r.id1
GROUP by r.gid, r.geom;

Nearest point ref: http://gis.stackexchange.com/questions/3249/postgis-assign-id-of-point-in-layer-a-to-closest-point-in-layer-b

Zoning Reference: http://secondcityzoning.org/

Screen Shot 2014-02-21 at 12.27.42 PM

Screen Shot 2014-02-21 at 1.35.38 PM

Water Infrastructure System Modeling

non-spatial_final

Background
The water infrastructure of Chicago has been an important issue for this metropolis for a long time. Though close to Lake Michigan and Chicagoan has completed several amazing construction to serve its citizen, the combined sewer system left problematic situation when facing storm rain.

Objective
Aim for researching this giant infrastructure in order to improve it, we first has to built a simulation model of this system. With the technology of GIS and Sensor, we expect there will be a responsive system built to deal with the unpredictable rainfall, aid current TARP system to work better and reduce the sewer overflow.

Process
1. Analyze current infrastructure, using UML diagram to design a digital model to simulate the weather changing and accordingly what situation will the system be facing and how will it fail. Using routing to test the flow of waste water.

2. Brainstorm and exploring new solution for that.

3. Connect Design solution with simulation data, create responsive intelligent system. For instance, movable facility to leading water path; using changeable infiltration material in large open space to adjust flow of sewer in real time.

ClassDiagram

park

Regional_Wastes

WATERLINES_PhysicalInfrasture

Eisenhower