2 Replies Latest reply on Jun 10, 2018 8:06 AM by Jason Kim

    Working with messy street name text that all describe the same point on a map

    Jason Kim



      I've created a simple table showing the frequency of pedestrian collisions that occur at an intersection of two streets. I created a combined field that takes the longitude and latitude so I can group all my points by distinct coordinates. The number of records for each point then represents the number of collisions at that point.


      However, when I label the coordinates with the street names that describe the intersection, different variations of spelling and ordering of the street names causes my number of records to be aggregated incorrectly, which then causes all sorts of havoc on other calculated fields like Collisions per 1000 pedestrians.

      Flaw with Intersection Records.PNG

      How can I make it so I can label coordinates so they have the same street names (e.g. assign QUEEN ST W. UNIVERSITY AVE) as the label for that coordinate and ignore all other variations? Phrased differently, how can I flatten the rows while having the street names appear.


      The gist of my problem is that I don't care what name variation appears, only that one is picked and displayed because coordinates are not readily interpretable to a human being used to using street names to describe the intersection.


      I'm not sure how to describe this sort of problem otherwise I'd have searched for the answer already but I think this is a fairly common problem in data analysis so I appreciate the help.