Skip to content

Instantly share code, notes, and snippets.

@milesgrimshaw

milesgrimshaw/streetscore_cartodb.sql

Created June 28, 2014 23:52
Show Gist options
  • Save milesgrimshaw/56d240e38cc766511ecd to your computer and use it in GitHub Desktop.
Save milesgrimshaw/56d240e38cc766511ecd to your computer and use it in GitHub Desktop.
Download ZIP
SQL queries to compile a single table for Street Score exploration
Raw
streetscore_cartodb.sql
## Instersection Commands For StreetScore and TreeCount
UPDATE cb_newyork_streetscore_compiled SET streetscore = (SELECT AVG(q_score) FROM streetscore_newyorkcity WHERE ST_Intersects(the_geom, cb_newyork_streetscore_compiled.the_geom) )
UPDATE cb_newyork_streetscore_compiled SET trees = (SELECT COUNT(*) FROM tree_census_simple WHERE ST_Intersects(the_geom, cb_newyork_streetscore_compiled.the_geom) )
## Collect PLUTO Data
UPDATE nyc_blocks_2010 SET assesstot = (SELECT sum(assesstot) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET avg_assesstot = (SELECT AVG(assesstot) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET unitstotal = (SELECT sum(unitstotal) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET unitsres = (SELECT sum(unitsres) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET comarea = (SELECT sum(comarea) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET resarea = (SELECT sum(resarea) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
UPDATE nyc_blocks_2010 SET officearea = (SELECT sum(officearea) FROM pluto WHERE ST_Intersects(the_geom, nyc_blocks_2010.the_geom) )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment