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
–ORDER BY rpt.gid
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_-_’)
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