Data Collection / Processing
Data Sources and Processing required for Data Driven PHB Ranking Process.
Last updated
Was this helpful?
Data Sources and Processing required for Data Driven PHB Ranking Process.
Last updated
Was this helpful?
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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
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 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.
Copy of AE Poles layer is provided by Austin Energy quarterly.
Create a copy that is filtered on Street Lights called AE_Streetlights.
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
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.
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.
Go to the to download updated data.
Household Income - Median Household Income
Total Population - Population Density