Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL queries to compile a single table for Street Score exploration
## 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
You can’t perform that action at this time.