just a bookmark of random psql queries.

queries.sql

(select buildings.*, neighborhoods.wkb_geometry as bounding_area from buildings INNER JOIN neighborhoods ON ST_Intersects(neighborhoods.wkb_geometry, buildings.wkb_geometry) WHERE neighborhoods.name IN('LAURELHURST')) as "active-neighborhood"

--
(select or_blocks.geom, or_places.geom from or_blocks INNER JOIN or_places ON ST_Intersects(or_places.geom, or_blocks.geom) WHERE or_places.name = 'Portland') as blocks

-- gets all blocks in portland joined with race data
(select or_block_data.*, or_blocks.geom, or_places.geom from or_blocks INNER JOIN or_places ON ST_Intersects(or_places.geom, or_blocks.geom) LEFT JOIN or_block_data on or_blocks.gisjoin = or_block_data.gisjoin  WHERE or_places.name = 'Portland') as blocks


-- gets all blocks in portland joined with race data percentages
(select or_block_data.*, h7x002::float/h7x001::float * 100 as percent_white, h7x003::float/h7x001::float * 100 as percent_black, or_blocks.geom, or_places.geom from or_blocks INNER JOIN or_places ON ST_Intersects(or_places.geom, or_blocks.geom) LEFT JOIN or_block_data on or_blocks.gisjoin = or_block_data.gisjoin  WHERE or_places.name = 'Portland' AND h7x001 > 0 ORDER BY percent_black DESC) as blocks

(select or_block_data.*, h7x002::float/h7x001::float * 100 as percent_white, h7x003::float/h7x001::float * 100 as percent_black, or_blocks.geom from or_blocks INNER JOIN or_places ON ST_Intersects(or_places.geom, or_blocks.geom) LEFT JOIN or_block_data on or_blocks.gisjoin = or_block_data.gisjoin  WHERE or_places.name = 'Portland' AND h7x001 > 0) as blocks

-- (select or_block_data.*, or_blocks.geom, or_places.geom, (h7x002::float/h7x001::float * 100) as percent_white, (h7x003::float/h7x001::float * 100) as percent_black from or_blocks INNER JOIN or_places ON ST_Intersects(or_places.geom, or_blocks.geom) LEFT JOIN or_block_data on or_blocks.gisjoin = or_block_data.gisjoin  WHERE or_places.name = 'Portland' AND h7x001 > 0) as blocks

-- (select acs_11_5yr_bg_41_oregon.*, B08301e18::float/B08301e1::float * 100 as percent_cycle, ugb.gid from acs_11_5yr_bg_41_oregon INNER JOIN ugb ON ST_Intersects(ugb.geom, acs_11_5yr_bg_41_oregon.wkb_geometry)) as blockgroups

-- (select * from or_blocks LEFT JOIN or_block_data on or_blocks.gisjoin = or_block_data.gisjoin) as blocks