> For the complete documentation index, see [llms.txt](https://atd-dts.gitbook.io/atd-geospatial/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://atd-dts.gitbook.io/atd-geospatial/arterial-management/data-driven-phb-ranking/data-collection-processing.md). # Data Collection / Processing Data Sources and Processing required for Data Driven PHB Ranking Process. There are 18 different Categories within the 3 Criteria Sections (Demand, Risk, Safety) that require data to be collected and processed before conducting the analysis to create the output data. This list is broken down by the 3 Sections, where each Category data source and description of the required processing are included. ## ASMP Street Network ## Demand ### Large Retail :shopping\_cart: * Data collection is a manual process. At this time, this layer consists of mainly grocery stores, including large warehouse stores. * The working copy of Large Retail Locations Final is located in the PHB Pro Project FGDB. * Add any new locations by copy/pasting parcels into layer. * Modify the polygon so each record encompasses a complete shopping center. * When finished updating, copy Large Retail Locations Final into the Data Collection FGDB for processing. ### Schools :school: * The ATD ADMIN Schools Polygon feature class in MAINT is maintained by the Data and Technology Services GIS Team for use in multiple ATD processes. This layer is verified annually to ensure the data remains up-to-date. * Copy this feature class into the Data Collection FGDB for the analysis, call it Schools. ### Church/Multifam/Park :church: :homes: :park: * Run the Churches Multifamily Parks Script Tool located in the PHB Pro Project Toolbox to create Churches and Multi-family layers. These layers are created from a combination of the Land Use Inventory layer (GISDM) and tcad/wcad ownership tables (GISDM External). * Verification of Church Word Filters will need to be completed in case any new terms need to be added to script for selection based on ownership name. Excel file under Documents folder. * The Parks layer (GISDM) is dissolved by the same Script Tool. ### Large Office :office: * Contact Austin Chamber of Commerce for an updated copy of the Major Employers point shapefile. This data contains points for employers, which includes the number of employees that work at the location. Use this layer to update the working copy of Austin Major Employers Mapping 500. * The working copy of Austin Major Employers Mapping 500 is located in the PHB Pro Project FGDB. * The point layer may have multiple points for one location (building/complex) * Only add locations that have a total of 500+ employees for the building/complex * Use the parcel layer to copy new polygons into the working layer * When finished updating, copy Austin Major Employers Mapping 500 into the Data Collection FGDB for processing. #### Large Office Shapefile Processing * If a completely new layer needs to be created, below are steps that have been followed in the past: * The layer may need cleaning up as many addresses were in the wrong locations before. * Move all points with identical main addresses to a common parcel, where all employers' number of mapped employees can be added together. * Select by Location on tcad & wcad parcel polygon layers to find parcels that contain the points, export tcad & wcad polygon layers, and delete identical parcels to remove any stacked parcels. * Merge above tcad & wcad parcel polygons * Spatial Join between merged parcel polygons and imported point layer * Frequency on Spatial Join out * For tcad parcels - Frequency = PARCEL\_ID, Summary = Mapped\_Emp * For wcad parcels - Frequency = PIN, Summary = Mapped\_Emp * Add new field to the merged layer called Mapped\_Employees (Long). * Join tcad & wcad frequency tables to the merged layer and calculate the Mapped\_Employees field. * Select all polygons where Mapped\_Employees field is 500+ and create the final layer to be called Austin Major Employers Mapping 500. ### Social Services :canned\_food: :baby\_bottle: * Data collection is a manual process. At this time, this layer consists of recreation centers, social service locations, and food banks (Central TX Food Bank website). * The working copy of Social Services Locations Final is located in the PHB Pro Project FGDB. * Add any new locations by copy/pasting parcels into the layer. * When finished updating, copy Social Service Locations Final into the Data Collection FGDB for processing. ### Public Transportation :bus: * CapMetro bus route data (GISDM External) has been coded to a copy of the ASMP Street Network called ASMP Street Public Trans HIN located in the PHB Pro Project FGDB. * The Bus\_Routes field has been added and coded to Yes, No, Multiple. * This layer will need annual updating in case any bus routes change or new routes are created. * Definition query to remove Rail * When finished updating, copy ASMP Street Public Trans HIN into the Data Collection FGDB for processing. ### U.S. Census Data :flag\_us: * Go to the [U.S. Census Bureau's website](https://data.census.gov/cedsci) to download updated data. * Household Income Table - B19001 (most current ACS 5 year data) * Total Population Table - B01003 (most current ACS 5 year data) * Median Household Income in the past 12 months - B19013 (most current ACS 5 year data) * For the B19001 / B01003 tables, add the following geographies to the query: Block Group, TX, All Block Groups within these counties - Bastrop, Caldwell, Hays, Travis, Williamson. * For the B19013 table, add the following geography to the query: Place, Texas, Austin city, TX. * Download the tables, save to PHB data area under Data\Other\Source\Census\_Download\yyyy. * Census Block Group polygon layer located in GISDM * Check the spatial reference, if it is set to TX Central State Plane (meters), project to Feet * Select by Location on Census Block Group polygon layer that intersects the Jurisdiction layer (Austin, Full Purpose Only) located on GISDM and create Block\_Group\_Census\_COA * **Household Income - Median Household Income** :moneybag: * Convert B19001 table into GIS compatible format, field names must be named exactly as listed under Attribute Schema Section, save as Excel Workbook. * Excel to Table to import Spreadsheet into Data Collection FGDB and call it Block\_Group\_HH\_Income * Run the Median Household Income Script Tool located in the PHB Pro Project Toolbox. This Tool will add additional fields and calculate the median household income for each Block Group based on the input fields. * Join table using GEOID field to Block\_Group\_Census\_COA and export to create household median income polygon layer called Block\_Group\_HH\_Income\_COA * The table breaks household income into specific dollar ranges. Use the household median income from the B19013 table to break down for the category ranges (Ex: median income of 67k, 60k and above - 0 pts, 50k-60k - 3 pts, 40k-50k - 6 pts, 40k and below - 10 pts) * Add new field to layer called Median\_HH\_Income\_Merge, calculate based on Median\_HH\_Income field using category ranges determined by the city's median income. * Dissolve layer by Median\_HH\_Income\_Merge to create layer to be used in the analysis called Block\_Group\_HH\_Income\_COA\_Dissolve. * **Total Population - Population Density** :homes: * Convert the B01003 table into GIS compatible format, field names must be named exactly as listed under Attribute Schema Section, save as Excel Workbook. * Excel to Table to import Spreadsheet into Data Collection FGDB and call it Block\_Group\_Population * Add GEOID field (text, 12) and calculate last 12 characters from GEO\_ID field. * Join table using GEOID field to Block\_Group\_Census\_COA and export to create population polygon layer called Block\_Group\_Population\_COA. * Add new field Total\_Acres (Double) and calculate. * Add new field Total\_Pop\_Per\_Total\_Acres (Double) and calculate based on Total\_Acres and Pop\_Estimate\_Total. * Use Statistics to determine what the median population density is, then create ranking scale based on Total\_Pop\_Per\_Total\_Acres data. * Add new field Pop\_Density\_Rank (text, 15) and calculate to < median, 0% - 25%, 25% - 50%, 50% - 75%, 75% - 100%, > 100%. If any Block Groups have population counts of 0, calculate field to < median. * Dissolve layer by Pop\_Density\_Rank to create layer to be used in the analysis called Block\_Group\_Population\_COA\_Dissolve. ### Customer Service Requests :sos: * Copy the Traffic Signal and Pedestrian Signal Requests feature layer available on ArcGIS Online, which is created directly from Knack, to the Data Collection FGDB, and call it Signal\_Requests\_All. * REQUEST\_DATE, REQUEST\_TYPE, REQUEST\_STATUS, and REQUEST\_SOURCE fields will be added to output streets data to give more information regarding the CSRs. * Create a copy that is filtered to only include the following records, call it Signal\_Requests\_PHB * Request Type = PHB * Request Status * Recently Received * Under Evaluation * Not Recommended - greater than 2 years old * Recommended - records will be removed from consideration * Request Type = Signal * Request Status * Recommended - records will be removed from consideration ## Risk ### # Travel Lanes/2 Stage Xing :motorway: * The EX\_XS\_GENERAL (Existing Cross Section) field in the ASMP Street Network breaks down the total number of lanes that need to be crossed, which includes turn lanes, bus lanes, etc. This field also includes whether a street segment is divided or undivided. * These 2 categories will be calculated based on this field. ### Speed :red\_car: * Speed Limit City Code data (GISMAINT1) has been coded to a copy of the ASMP Street Network layer called ASMP\_Street\_Public\_Trans\_HIN. * The Speed\_Limit\_City\_Code field has been added and coded to the various speed limits. * Since the Speed Limit City Code data is not currently complete, research was performed to create this layer. Segments that were not identified in the city code were coded to 30 mph. Additional research was completed as some streets were clearly assigned incorrect speeds, verified with Google Street View. ### Street Lighting :bulb: * Copy of AE Poles layer is provided by Austin Energy quarterly. * Create a copy that is filtered on Street Lights called AE\_Streetlights. ### Distance to Signalized Xing :vertical\_traffic\_light: * Copy of Traffic Signals and Pedestrian Signals feature layer on ArcGIS Online, which is created directly from Knack, to the Data Collection FGDB, call it Signals\_All. * Create a copy that is filtered to include the following records, call it Signals. * Include all records **except**: * Signal Status: REMOVED\_PERMANENTLY ## Safety ### High Injury Network :woman\_walking: :man\_biking: * The High Injury Network (HIN) was created from the CTN, which the ASMP Street Network is also create from. The Pedestrian and Bicycle HINs are located on ArcGIS Online, but do not change often. * Pedestrian and Bicycle HIN data has been coded to a copy of the ASMP Street Network called ASMP\_Street\_Public\_Trans\_HIN * PHIN, BHIN, HIN\_ALL fields have been added and coded based on the locations. ### Pedestrian Crashes :head\_bandage: * 5 years of pedestrian crash data are used for this analysis (from present date). * You will need an account for the Vision Zero Database to complete this section. If you do not have an account, contact the DTS Dev Team for assistance. * Open the Vision Zero Database Project, you will need to login to the Vision Zero Database * Under the State Plan Map, update the definition query on atd\_vz\_data.public.atd\_txdot\_crashes to the following: * Austin Full Purpose is equal to Y and * Crash Date is after today's date 5 years ago and * Harmful Event ID is equal to 1 (1 = pedestrian) * **Note:** Do not export the txdot crashes data as a layer. You must export using the Table to Excel tool since the crash\_id field has a field type of OBJECTID. If you use any other geoprocessing tools, the OBJECTID will remove the crash\_id field which is required to join tables. * Table to Excel on atd\_vz\_data.public.atd\_txdot\_crashes, save Spreadsheet to the Tables directory, call it atd\_txdot\_crashes\_yyyymmdd. * Check to "use field alias as column header" * Delete all fields in Spreadsheet except the fields listed below * Save and close * Excel to Table on atd\_vz\_data.public.atd\_txdot\_crashes\_yyyymmdd.xlsx that was created in the above step. Save to Vision Zero Database FGDB and call atd\_txdot\_crashes\_yyyymmdd. * Update the field Alias' on the new imported FGDB table: * Crash\_id – Crash ID * Crash\_fatal\_fl – Crash Fatality * Crash\_date – Crash Date * At\_intrsct\_fl – At Intersection * Harm\_evnt\_id – Harmful Event ID * Fhe\_collsn\_id – Manner of Collision * Sus\_serious\_injry\_cnt – Suspected Serious Injury Count * Nonincap\_injry\_cnt – Non-incapacitating Injury Count * Poss\_injry\_cnt – Possible Injury Count * Non\_injry\_cnt – Non Injury Count * Unkn\_injry\_cnt – Unknown Injury Count * Tot\_injry\_cnt – Total Injury Count * Death\_cnt – Death Count * Latitude\_primary – Latitude * Longitude\_primary – Longitude * Address\_confirmed\_primary – Primary Address * Address\_confirmed\_secondary – Secondary Address * Austin\_full\_purpose – Austin Full Purpose * Add table to WGS-84 Map * Right click on table and Display XY Data, name layer atd\_txdot\_crashes\_yyyymmdd\_WGS. * Project layer to NAD 1983 State Plane Texas Central FIPS 4203 (US Feet), name layer atd\_txdot\_crashes\_yyyymmdd\_SP. * Assign domains to the following fields in the new State Plane layer: * • Harmful Event ID – Harmful\_Event\_ID domain * Manner of Collision – Collision\_Type domain * Export atd\_txdot\_crashes\_yyyymmdd\_SP to the Data Collection FGDB and name it Pedestrian\_Crashes\_SP.