Opportunity area

First Join the 311vacant report with city zoning table.  So we can see which vacant building located in the business area.

–SELECT z.gid, z.zone_class, z.the_geom, rpt.gid, rpt.request_ty, rpt.vacant_or_, rpt.geom
–From rpt_311vacantlotreports_20082013 AS rpt
–LEFT JOIN cty_zoning2 AS z
–ON st_within(rpt.geom,st_setsrid(z.the_geom,3435))
–ORDER BY rpt.gid

QQ截图20140221135103

Then I choose those vacant buildings that locate in the place which zoning type is B,C,D,M.

CREATE OR REPLACE VIEW selectzone AS(
SELECT * FROM chenxingzone
WHERE zone_class LIKE ‘B_-_’ OR zone_class LIKE ‘C_-_’ OR zone_class LIKE ‘D_-_’ OR zone_class LIKE ‘M_-_’)

QQ截图1

Then using this table join the table of footprint. We can know details of those vacant buildings, like building area, building shape and how many floors those building have.

SELECT b.bldg_id, b.stories_number, b.bldg_sq_fo,  b.shape_area b.geom zo.zone_class, zo. geom

FROM cty_buildingoutlines AS b

LEFT JOIN selectzone as zo

ON ST_WITHIN(zo.geom, b.geom)

ORDER BY b.bldg_id

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