search

cityofaustin / atd-data-tech

Austin Transportation Data & Technology Services
17 stars 2 forks source link

Review Impact of Menchaca Street Name Change #849

Closed johnclary closed 4 years ago

johnclary commented 4 years ago

This name change potentially impacts Data Tracker, Signs and Markings, Banners — and the way this data is stored is different in each app.

Are we going to retroactively update all of our records? Ensure we have the new street name in all our apps. Verify that the street segment ID hasn't changed.

@SurbhiBakshi will provide geo expertise and take the lead on looking at this. We'll have a meeting if it turns out the impact is significant.

SurbhiBakshi commented 4 years ago

@johnclary - Looks like CTM-GIS has not completed changing the name for all features from Manchaca to Menchaca. I looked at some of the most commonly used location datasets - address points and street segments. Though a lot of the records show the name has been changed, some don't. I will reach out and see why that is the case, and also confirm my assumption that the modified date is for the name change.

Most common street location sources from GIS.

Name Geo ID Date of Change Change in ID? Check with CTM GIS
TRANSPORTATION.street_segment SEGMENT_ID Nov 10, 2019 (modified_date) since most of the segments were created in 2005, but modified in 2019, looks like the segment ID remained the same. (4 out of 72 segments are still Manchaca, one is null) Exceptions are in the 2 mile ETJ and not in Full Purpose Jurisdiction. Not sure what the time line for those will be.
LOCATION.address_point PLACE_ID Nov 10, 2019 (modified_date) since most of the segments were created much earlier, but modified in 2019, looks like the segment ID remained the same. (37 out of 502 points are still Manchaca) Exceptions are in the 2 mile ETJ and not in Full Purpose Jurisdiction. Not sure what the time line for those will be.
SurbhiBakshi commented 4 years ago

@johnclary I have listed some other geospatial datasets, I am not sure if any of these are being referenced in the apps. Please let me know if any of these need to be considered as well.

Check if any of these are being pulled into Knack for location -

  1. LOCATION.address_point
  2. LOCATION.coa_intersection_points
  3. LOCATION.coa_intersection_segments
  4. LOCATION.coa_str_name_intersection
  5. LOCATION.coa_str_range_intersection
  6. LOCATION.coa_street_locator
  7. LOCATION.address_locator
  8. TRANSPORTATION.segment_locator
  9. Sidewalks
  10. TRANSPORTATION.street_segment
SurbhiBakshi commented 4 years ago

@johnclary - I went over the Data Tracker app and tried to gauge which of the objects was the main location provider based on connections, and it looks like the street_segment object is the primary, with locations being a secondary source. I have tried to see how many existing records would be affected, but not sure if this is comprehensive. Let me know if there is something I am missing, because I am looking at the objects one by one. Not sure if there is a ~simpler~ better way.

Name of Application Location Object Location Object Connects To Connects to Location Object Connection Field No. of Records Affected
Data Tracker locations street_segments PRIMARY_ST_SEGMENT_DISPLAY_NAME
street_segments CROSS_ST_SEGMENT_DISPLAY_NAME
cameras ATD_LOCATION_ID 17
construction_cost_estimate_reports ATD_LOCATION_ID 0
damage_reports ATD_LOCATION_ID 2
dms ATD_LOCATION_ID 0
hazard_flashers ATD_LOCATION_ID 1
pole_attachments ATD_LOCATION_ID 1
school_beacons ATD_LOCATION_ID 0
signals ATD_LOCATION_ID 22
signal_requests location 29
tmc_issues location 551
travel_sensors ATD_LOCATION_ID 3
work_orders_signals ATD_LOCATION_ID
locations_traffic_counts street_segments PRIMARY_ST_SEGMENT_DISPLAY_NAME
locations_traffic_counts street_segments CROSS_ST_SEGMENT_DISPLAY_NAME
traffic_counts ATD_LOCATION_ID 2
street_segments locations PRIMARY_ST_SEGMENT_DISPLAY_NAME 52
CROSS_ST_SEGMENT_DISPLAY_NAME 4
locations_traffic_counts PRIMARY_ST_SEGMENT_DISPLAY_NAME
PRIMARY_ST_SEGMENT_DISPLAY_NAME
traffic_counts STREET_SEGMENT_IDS
vision_zero_enforcements enforcement_primary_street_segment
enforcement_cross_street_segment
tmc_activities 1611
street_segments STREET_NAME 49
SurbhiBakshi commented 4 years ago

https://www.kut.org/post/city-council-votes-change-spelling-south-austin-street-manchaca-menchaca

Selected features are still Manchaca.

image.png

SurbhiBakshi commented 4 years ago

Signs and Markings

Name of Application Location Object Location Object Connects To Connects to Location Object Connection Field No. of Records Affected
Signs and Markings street_segments work_orders_markings STREET_SEGMENT_IDS 90
street_names work_order_markings PRIMARY_STREET_NAME 24
CROSS_ST_1_NAME
CROSS_ST_2_NAME
street_names work_order_signs PRIMARY_STREET_NAME 36
CROSS_ST_1_NAME
CROSS_ST_2_NAME
work_order_signs_locations SIGNS_LOCATION 15
csr_issues CSR_ADDRESS_csr 23
SurbhiBakshi commented 4 years ago

Signs and Markings

Name of Application Location Object Location Object Connects To Connects to Location Object Connection Field No. of Records Affected
Street Banner Program banner_locations banner_reservations_lpbs banner_locations 24
TO_REMOVE_lpb_banners_district_sub_districts banner jobs
street_banners_inventory_transactions work_order_ots_banner_job
street_banners_inventory_transactions work_order_ots_banner_job
banner_over_the_street_locations 2
banner_locations 2
over_the_street_loc_direction 2
johnclary commented 4 years ago

thank you @SurbhiBakshi for this excellent research. i believe the easiest approach would be to export the affected records to csv, modify them, and import them back into each application. we only need to update the primary objects, and the connected objects should be updated accordingly:

Can you work on this? You'll definitely want to do this in test first.

SurbhiBakshi commented 4 years ago

@johnclary, yes, will work on this issue. Planning to wait to hear from CathyW from CTM GIS who will be able to give me an estimate on when the ETJ street names will change, since that may affect some of the records.