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.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
AS (
SELECT DISTINCT ON( g1.*, ::INT4 As node_id,
FROM building_updated As g1, street_vertices_pgr As g2 — g1 = source table ; g2 = target table
AND ST_DWithin(g1.geom, g2.the_geom, 1000)
ORDER BY, ST_Distance(g1.geom,g2.the_geom)

— 4. routing
AS (
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 )
AS route
LEFT JOIN street
ON route.id3 =

— 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:

Zoning Reference:

Screen Shot 2014-02-21 at 12.27.42 PM

Screen Shot 2014-02-21 at 1.35.38 PM


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s