NYC Parks DataDive 2012
NYC DataDive with DOITT and NYC Parks Department
Friday, September 7, 2012 at 7:00 PM - Sunday, September 13, 2012 at 12:00 PM
School of Visual Arts
132 W. 21st St.
New York, NY
- Hashtag: #NYCParksDD
- Wiki: To edit this wiki, create an account above!
- Github: https://github.com/datanoborders/Datadives
- MySQL details
Block Pruning Data
Records of every street pruned, including year of pruning and [x,y] from - to. Includes GIS shapefiles and raw CSV of data. Also functions as a citywide line segment file showing streets and highways citywide as well as a variety of "imaginary" lines such as political boundaries.
- Data exists only for Brooklyn but the data set extent is citywide***
Metadata and field definitions for this file can be found here: http://www.nyc.gov/html/dcp/html/bytes/meta_lion.shtml
Clean CSV dataset (just brooklyn, remove irrelevant observations and variables): https://www.dropbox.com/sh/vxmr3wssp4yp41g/nlCgRgym6h
Street Tree Inventory
A census of all of the trees in New York from 2005 plus all the street trees that have been planted since then, through fall 2011, including species, State Plane [x,y] coordinates and lat/lon.
Attribute information for street tree inventory https://www.dropbox.com/s/zzxz3m47fps5zn1/AllTrees_attribute_descriptions.xlsx
Dataset with Brooklyn for "All Trees" variables: https://www.dropbox.com/s/jy8ocpcpsg07ms7/AllTrees_Brooklyn2.csv
Species codes and common names: this is a list for codes not included in the table above https://www.dropbox.com/s/qyj2qgd5ddy9f1r/ForMSGDB_User_LK_SPECIES.xlsx
Forestry Management System (ForMS)
Includes [x,y] State Plane coordinates information Contains work order, inspection and request records for all forestry work starting 2008 to current Most, but not all, requests originate from the 311 Call Center, and will be found in both data sets
Metadata and field definitions for these files can be found here: https://www.dropbox.com/s/lu8lc59iado544u/SR_INSP_WO_FieldDefinitions.xlsx
Table relationships are here https://www.dropbox.com/s/q8qdir1r7xnb4d8/MetaData.pdf
Tree Manager data Contains request, work order and tree inventory information from 1998 to 2008. https://www.dropbox.com/s/mbaby1txx4t467a/Tree_Manager_Data_wGeocodes_Cleaned.zip
Table relationships from original TreeManager application is here: https://www.dropbox.com/s/63rxbzxmrq3qiwu/TreeManagerDataModel.pdf
Work History Tables ForMSGDB_User_T_H - work orders ForMSGDB_User_T_H_legacy - deleted work orders - in order to map Work History records the records in these tables must be linked back up to the Tree-In-Site tables which contains location information
Tree-In-Site Table (holds tree inventory information as well as address/location information) ForMSGDB_User_T_S ForMSGDB_User_T_S_Legacy - these are trees that were removed from the inventory (possibly because of a completed tree removal or fallen tree Work History) - these tables do not have X coordinates or Y coordinates but contain tree attribute information
FOR X coordinates and Y coordinates, SEGMENT IDs, 2010 Census Tracts and Blocks, etc for the above Tree-In-Site Tables: 1) Join "Geocoded_T_S_legacy" to "ForMSGDB_User_T_S_Legacy" 2) Combine the tables "Geocoded_T_S_part1" and "Geocoded_T_S_part2", and join it back to "ForMSGDB_User_T_S"
Request Tables: ForMSGDB_User_T_R
311 Request Data
All forestry requests since 2004 received through the 311 Call Center Includes [x,y] State Plane coordinates
Also includes a spreadsheet mapping 311 request type values to ForMS request type values
Species recipe future plantings https://www.dropbox.com/s/ruuyw19ynily810/Recipes_Data%20Dive.xlsx
Street tree species compatibility info https://www.dropbox.com/s/cbcttn04wdgajwo/Street%20Tree%20List-Species%20Compatibility.xlsx
Bonus Data 2010 Land Cover raster derived from LiDAR for those intrepid DataDivers https://www.dropbox.com/sh/noflaxldxiw3phd/W6b4mB96Ie
Tree canopy metrics derived from the land cover dataset summarized by major administrative, physical and political boundaries https://nycopendata.socrata.com/Environmental-Sustainability/NYC-Urban-Tree-Canopy-Assessment-Metrics-2010/hnxz-kkn5
An open source GIS tool for converting shapefiles: http://qgis.org
- Intro to D3: http://dl.dropbox.com/u/53163928/Intro%20to%20D3.pdf
"group account" parks-datadive.cartodb.com Access CartoDB Examples
- "embedded map:" https://parks-datadive.cartodb.com/tables/alltrees_master/embed_map
- "add sql map:" https://parks-datadive.cartodb.com/tables/alltrees_master/embed_map?sql=select%20*%20from%20alltrees_master%20where%20species2%20=%20'PLAC'
- "Code example using Cartodb-Backbone and CartoDB-Leaflet libraries" http://dl.dropbox.com/u/1307405/CartoDB/parks-datadive/profile.html
- "Example JSON API from All trees data" https://parks-datadive.cartodb.com/api/v2/sql?q=select%20*%20from%20alltrees_master%20where%20community_name%20ilike%20'upper%20east%20side'%20limit%2010
Urbien aims to create a social editable shared tree inventory ready for developers to layer the apps on top, for researchers to use as the data commons based on interlinked data, not just individual datasets, matching and exceeding the http://phillytreemap.org
- Explore the Tree, Tree species, 311, work orders, etc. menu at http://urbien.com
- Open data presentation: http://urbien.com/v/Article/32711
- Example JSON API from All trees data http://urbien.com/v/Article/32716 Urbien Web API is a simple generic CRUD api for any type of data.
Python scripts for extracting lat/lon from shapefiles:
- Convert the Street Tree Inventory from X/Y to lon/lat: https://www.dropbox.com/s/iek9jxg8j59hv8j/convert_lonlat.py
- To convert ForMS you have to load the .csv's into some kind of GIS program and then save the shapefiles. Then you can run the convert_lonlat.py script above.
- Convert the Block Pruning data from XFrom/YFrom, XTo/YTo to LonFrom/LatFrom, LonTo/LatTo: https://www.dropbox.com/s/kl1iatt58z0uupr/convert_blockprune.py
- By matching addresses in the outer boroughs:
- ForMS.Request_All_NamesCleaned.SRX = 21468535 + 276815 * Alltrees_20120606.lon
- ForMS.Request_All_NamesCleaned.SRY = -14578574 + 362970 * Alltrees_20120606.lat
- X,Y origin is at 40.16949, -77.48719 (in Pennsylvania)
This go-round we're partnering with DOITT to work with the Parks Department. The three main projects we'll be working with are:
NYC Parks presentation from Friday evening is available as a PDF https://www.dropbox.com/s/6q243b481hrhk39/DataDive_FINAL.pdf