— 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
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
SELECT DISTINCT ON(g1.id) g1.*,
g2.id ::INT4 As node_id,
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
SELECT seq,id1, id2 as node, id3 as gid, route.cost, street.geom FROM pgr_kdijkstraPath(‘
SELECT id as id,
st_length(geom) as cost
FROM street’, 001, array(SELECT “node_id” from building_final), false, false )
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;
Zoning Reference: http://secondcityzoning.org/
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.
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.
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.