DATA-DRIVEN ARCHITECTURAL PROGRAM

337984036

Untitled-1

STEP 1

join car registration information with census tract

CREATE TABLE haidong_carquantityT AS (

WITH car_quantity AS (
SELECT dmo_censustract_2010.geoid10, st_geometryn(dmo_censustract_2010.geom,1) as geom, trn_vehicleregistration.”HD01_VD01″
FROM dmo_censustract_2010
JOIN trn_vehicleregistration ON dmo_censustract_2010.geoid10::double precision = trn_vehicleregistration.”GEO.id2″
ORDER BY dmo_censustract_2010.geoid10
)

SELECT car_quantity.geoid10, st_multi(st_setsrid(car_quantity.geom,3435))::Geometry(MultiPolygon,3435) AS geom2, car_quantity.”HD01_VD01″
FROM car_quantity

);

STEP 2

estimate street parking space( I assume all streets are two-directional)

CREATE OR REPLACE VIEW haidong_streetcapacity AS (
SELECT cty_streetcenterlines.street_nam,
2*cty_streetcenterlines.length/19 as capacity,
cty_streetcenterlines.geom
FROM
cty_streetcenterlines)

STEP 3

buffer and building’s centroid

WITH center AS

( SELECT geom AS g FROM

cty_buildingoutlines

WHERE gid=51)

SELECT cty_buildingoutlines.bldg_id,

cty_buildingoutlines.shape_area, cty_buildingoutlines.stories,

cty_buildingoutlines.geom,

st_centroid(cty_buildingoutlines.geom)

AS centroid

FROM cty_buildingoutlines,center

WHERE st_within(st_centroid(cty_buildingoutlines.geom),st_buffer(center.g,500))

ORDER BY  cty_buildingoutlines.bldg_id

SELECT * FROM  centroid;

 

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