Linear Referencing for Traffic Collisions. What prep is needed?

[TomMagdaleno]

I am trying to get my data in order so I can place points along my centerlines to represent traffic collisions.  I have a centerline feature with street names and a unique number for each segment.  This centerline is broken at alleys and cross streets. 

I have a table with a list of collisions.  It has a column for the street, distance, direction and cross-street.  So a collision may be something like this.

Carmen Dr, 120???, East, Daily Dr.

From what I have read this is a bit more complicated than what Linear Referencing can do.  I was wondering if I can specify columns for street and cross street, distance and direction, and have the program place my points.  Is this possible?  If not, what are my other options?

[RichardFairhurst]

I am trying to get my data in order so I can place points along my centerlines to represent traffic collisions.  I have a centerline feature with street names and a unique number for each segment.  This centerline is broken at alleys and cross streets. 

I have a table with a list of collisions.  It has a column for the street, distance, direction and cross-street.  So a collision may be something like this.

Carmen Dr, 120�??, East, Daily Dr.

From what I have read this is a bit more complicated than what Linear Referencing can do.  I was wondering if I can specify columns for street and cross street, distance and direction, and have the program place my points.  Is this possible?  If not, what are my other options?

There is no built in methods for using your data directly.  What I do is I derive an intersection point class from my Centerlines.  I then process that through the Locate Features Along Routes tool.  I then extract all cross street pairings and insert them into a table.  The cross streets are concatenated in curly brackets and the final table has all route ID and measure information of each intersection.  All of this is created by a python script that is triggered by windows scheduler each weekend (the script takes about 2 1/2 hours to run for about 120K Centerlines and 200K intersection pairings, but since it is automated during non-working hours I don't mind). The final Intersection table can be displayed on a map as either an X/Y Event Table or an LR Event Table.

Once you have a table like that you can create a similar cross street concatenation field in your collission data.  You process a join and transfer your street route ID and measure reference to your collission.  Then you convert your direction field into another field containing a distance multiplier of 1 or -1.  If your offset distance is a text field convert it to new a numeric field using units that match your measure system.  Then your collision position is calculated using a formula of:

Intersection_Measure + Offset_Distance * Direction_Multiplier

If your data is good and you can get matches on your intersection table, the whole process can take about 10 minutes to finish.  If you have a lot of spelling variations or many intersections with duplicate names (loop streets, branched roads, complex routes, etc) the process is more time consuming, but that kind of data always takes longer to evaluate and a program won't do it for you accurately.  (My method will choose a position according to the first join if there are duplicates, but it is not always the correct choice and I have to reevaluate them even if it got it right.)

Spell checking takes the most amount of time and I always create new fields to store the spelling corrections I make rather than overwrite the original spellings.  The intersection table makes the spell checking process much easier as well.  I also use the Event Error field to determine when the measure given is not on my Route so that I can examine them (sometimes field measurements overshoot a route or I used the wrong direction multiplier for a route.  I create new fields for overrides of these values as well, since I never want to overwrite original field data, even if it was wrong to begin with).

I use this same process for translating Pavement Management data, Traffic Control Device Inventory (Signs and pavement markings), Traffic Signals, Transporation Improvement Program data (capital projects) and almost any other kind of data described in the format you have mentioned.  (You are fortunate to have your data preparsed.  Only one of the databases I get is parsed for me.  The rest I have to parse, which adds about 1 hour to the process).  Using the tools to Overlap Events makes it possible to correllate these different data types into a single table that can be sorted by Route ID and measure to get all of their relative positions to each other.

[TomMagdaleno]

Thanks Richard,
  I will take a look at this and see if I have any problems.

[RichardFairhurst]

Thanks Richard,
  I will take a look at this and see if I have any problems.

You most likely will have quite a few problems.  I know I encountered 100s of them, but I was able to build a script that exclusively originated in ModelBuilder.  To do it that way you must have an ArcInfo licence.

I found relatively little help on the forums to piece all of this together.  It is not like there are just a few tools I used to build what I have created.  It took me a huge amount of trial and error over many months and I ended up using over 120 tool steps to achieve the result I wanted.  Most of the script involves adding fields, summarizing, doing joins, calculating data transfers, concatenating results, resummarizing, derive new feature class, etc.   I kept adding new features as I found I wanted more derived data that would help me solve new problems that came up as I started use the model (I have been using it for about 2 years now).

One of the trickier parts was figuring out how to use the Collect events and pivot tool to collapse many Centerline end points into a single interesection point that could be attributed with all of its street names.  The other trick to making the model work was concatenating X and Y coordinates into a formatted concatenated string in curly brackets that could be used as a join field.

I have found the model and script were worth the effort because I am now able focus my maintenance task on the Centerline FC and rederive every all other classes from it weekly.  The detivatives the script buiilds include my LR network, my intersection points, a cross street pair table, a table history of all points every on my centerline network, a maintained only route class, and a cul-de-sac and stub point class.  The intersection pair table also can identify if the intersection includes maintained roads or not and has fields to tell me if duplicate intersection names exist and the amount of distance and relative direction of the duplicates going north to south or west to east as either a direction (for duplicate pairs) or a number (for 3 or more duplicates).  It also has 5 different fields I use for joins to make it easier for me to revalidate my event data if street name changes or realignments occur (duplicate intersections make this many jion possibilities necessary).

I am willing to share what I learned to save you the effort of all of the trial and error I went through.  (It will help me revist parts of what I did in the process and refine it for myself, and I also need a refresher on what I did to make sure it will continue to work in ArcGIS 10).

[TomMagdaleno]

Thanks Richard.  I was able to build a Macro in Excel that does a find and replace on my street names to clean them up.  I also have a point layer with all the intersections, so I just added a field and concantonated them.  I guess the part I am not sure about is how do I assign route numbers to the lines that they sit on.  I also didn�??t get much of a join when I joined the data, I need to clean it up more.  Then I can try to do the distance multiplier field.  Thank you for sharing.

[RichardFairhurst]

Thanks Richard.  I was able to build a Macro in Excel that does a find and replace on my street names to clean them up.  I also have a point layer with all the intersections, so I just added a field and concantonated them.  I guess the part I am not sure about is how do I assign route numbers to the lines that they sit on.  I also didn�??t get much of a join when I joined the data, I need to clean it up more.  Then I can try to do the distance multiplier field.  Thank you for sharing.

The Route ID and measure are assigned to the intersections by doing a Locate Features Along Route with the ooption to find all routes that the intersection point touches.  The Centerlines, routes, and intersections need to be all topologically related to get the best results.  I guess I am not sure how you have created your Routes from what you have said so far.  My Routes do not break at intersections but continue through all logically related Centerline segments that have the same Street name and that are connected or potentially connected (I allow gaps with measures that account for the gap length).

I personally do not use numbers for my Route IDs, although that is the normal way Routes are identified.  On my Centerlines I have fields for a Community Plan abbreviation and an optional 3 character subroute field for distinguishing branches which I append with the Street Name to create my Route IDs.  I find that make it easier for me to know which Route I am working with.  However, a number is fine as long as the Street Name and number association is easy to join to derivatives of your Route Data.

Normally an intersection point class has the street names listed in order.  That does not work for the approach I take.  The join works best with a table that has all possible street name pairings in all possible orders of street names (but the first street name always relates to the Route ID and measure).  I do a series of queries and table extractions from the locate result and append them together to create that table.  The table will contain two or three records with identical Route ID and Measure values if 3 street names or 4 street names meet at a single intersection.

It is best to begin developing a model for the process as you create this so that it can be repeated to reflect changes as they occur on your network.  The steps to maintain synchronization of multiple FCs grows pretty quickly and it is nearly impossible to do them all manually in the correct order.

[TomMagdaleno]

It sounds as though my next step is to create a bunch of If/Then statements (or a python equivalent) and assign a route number based on what direction from the intersection it is located.

So if my accident was Daily Dr 100' West of Carmen Dr I would need to programatically find the right segment and have it measure away from the intersection.  Daily at Carmen -100'.

Between 225 and 315 degrees would be considered West.  I see what you mean about complicated.

[RichardFairhurst]

It sounds as though my next step is to create a bunch of If/Then statements (or a python equivalent) and assign a route number based on what direction from the intersection it is located.

So if my accident was Daily Dr 100' West of Carmen Dr I would need to programatically find the right segment and have it measure away from the intersection.  Daily at Carmen -100'.

Between 225 and 315 degrees would be considered West.  I see what you mean about complicated.

I don't need to do any if/then statements or python scripts other than to construct the intersection pair table.  I would have an entry in the table that would read "{DAILY DR}{CARMEN DR}", which would give me the Route ID of the route that is derived from all Centerline sections of DAILY DR and a measure at that intersection (say 1123.125 feet).  Since my routes are normally built from upper left, west would normally translate to -1.  (I can use the intersection angle to validate my assumption and it does tell me where I built the route differently because they were better oriented from upper right or lower right, but those cases are the exception for my model).  My formula would be 1123.125 + 100 * -1 and the collision would therefore be at measure 1023.125 feet.

My Routes have already chained all of my related Centerline segments together using the Create Routes tool and assigned measures on a single route for all portions of DAILY DR.  I do not use a script to construct any sort of segment chain during the collision geocoding phase (where a collsion potentially could be offset from an intersection across more than one Centerline segment if the officer did not use an alley for example, but a primary street interection with an offset that crossed over the alley).

If I want to know which Centerline segment it falls on I do a Locate Features on Routes with my Centerlines (keeping either the ObjectID or other ID of the segment stored in a calculated field) and merge the collision and Centerline event tables.  Working from segmented Centerlines to LR measures is backwards from my perspective if that is what you are trying to do and not really an LR solution (IMHO).  The set of collisions with the same Route ID have ordered measures which allow me to sort the collisions as they travel west or east (in the case you mentioned) on the route and detect the order of the Centerline segments apart from whether the Centerline segments have ordered IDs or not.

The continuous Route also lets me do cluster analysis based on distances that may or may not fall within a single Centerline segment, such as looking at bands of 1000 feet which probably would find the largest collision cluster falling 500 on both sides of the intersection with the highest collision rate (if an alley fell in the 500 feet offset on either side breaking it into more segments it probably has only negligible impact on the accident rate, but it would fall within the cluster analysis).  This is where scripting and a series of if/then SQL statements is most useful for examining chains of collisions and Centerlines.

[TomMagdaleno]

Thanks Richard,
  I talked to an ESRI LR expert today regarding some questions I had on the tools and he also mentioned it would be very difficult to convert my data.  I mentioned I have an Avenue Script that we used previously and he said it would be easier to convert that to Python.  So I think I am going to go that route (no pun intended).  In the past I have used it and then used Disperse Markers under Represenation to cluster the stacked accidents that happen right at the intersection. 
  This whole exercise is to move our last legacy workflow away from 3.3 since we will be upgrading to Windows 7 next year.   
I also asked him if he knew of a book that had Python Code specific to ArcGIS workflows, and he says everyone is waiting for that, but nobody has written one.  He pointed me to the python script example for every tool in the toolbox under ToolHelp. 
  Thank you again for your insite into this complicated issue.