Scoring buildings based on proximity to neighborhood features

score-buildings.sql

drop table if exists target_homes;

with
supermarket_zones as (select st_expand(geom, 0.0045) as zone, 5 as score from osm_polygons where osm_polygons.shop='supermarket'),
rail_stop_zones as (select st_expand(geom, 0.0045) as zone, 5 as score from trimet_rail_stops),
park_zones as (select st_expand(geom, 0.0045) as zone, 2 as score from osm_polygons where osm_polygons.leisure='park'),
target_buildings as (
  select * from supermarket_zones inner join buildings on st_intersects(supermarket_zones.zone, buildings.geom) where buildings.subarea='City of Portland'
  union select * from rail_stop_zones inner join buildings on st_intersects(rail_stop_zones.zone, buildings.geom) where buildings.subarea='City of Portland'
)

select sum(score) as rank, gid, geom into target_homes from target_buildings group by 2, 3;
create index target_homes_gix on target_homes using gist (geom);