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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s