Last active
December 26, 2015 14:08
-
-
Save caged/7162938 to your computer and use it in GitHub Desktop.
just a bookmark of random psql queries.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment