easily select street segment(s) between two cross streets for editing

[KatherineHayman1]

Hello,
I am working with a massive streets database, in which each street is broken into segments at each cross street.  My work involves editing groupings of these streets at a time (for example, along "5th" from street A to street F) and while manual selection of those segments is certainly possible it is very tedious.  I have been using Select by Attributes as well as the find option. 

I was thinking a program that selected only the segments of "5th" between Street A and Street F would be fantastic, although I don't know if anything similar to this exists or if it would be relatively straightforward to write. 

Does anyone have any suggestions for automating/speeding this process?

[RichardFairhurst]

I did ESRI's intro to Linear Referencing course and it was working with trails data with about 14 trails.  It wasn't much help, making an event table consisted of typing in the trail number for the route you want to place a point on.  I want to be able to do some kind of calculation to create my route name.  So I figure I concantonate the primary, direction and secondary street and that will be my route name.

I guess my concern is how do I reliably and repeatibly find a particular intersection.  The labeled section of centerline refers to the two different discriptions I could have from the police department that would be describing the same piece of road. 
So my concern is with new event tables I receive on a quarterly basis.  How will I know and match up the police description of the street segment (i.e. Primary St = Arneill, Secondary St = Ponderosa, distance 100 ft, direction South) matching up with what I have. 
  I like the idea of having single segments, or even single segments between intersections and for example subtracting from the total if it is south and adding if it is North, but I would think I would need to program something to know the total measure of each segment and know a particular description of it means it should subtract from the total and not add.

Where you are going opposite of my approach is that you are trying to make the cross-streets tie to roadways segments or routes and not to intersections or points and therefore are multiplying routes.  I tie my cross-street names to intersections or points and not to road segments or routes and therefore multiply my points.

My approach allows me to use any intersection of a road as a reference relative to any other point along that road, no matter how many intersections the road has, which is the real world way things work.  If I tell you to go 3,000 feet south of ARNELL RD_PONDEROSA DR, I could just as easily say 2,000 feet south of ARNELL RD_PICKWICK DR if those two intersections are 1,000 feet apart.  Police will use either to mean the same thing, so your model needs to allow for either description.

Therefore I have at the same point for any two cross-streets two points.  The intersection of ARNELL RD_PONDEROSA DR_ has a measure relative to ARNELL RD, while the intersection of PONDEROSA DR_ARNELL RD has a measure relative to PONDEROSA DR at the exact same point.  These two points in the exact same place tie me to two different routes that cross in the same place.  I then use the same principal of combining the street names at the intersection that you want to use for your Routes and can join to the intersections for calculations the same way that you want to do it with Routes.

So just switch your thinking away from the Route and apply it to the intersection.  After all we use the expression "reference points" and "points of reference", not "reference segments" and "segments of reference".

My model has been applied to over 70,000 different event points and line segments, and I may have only typed in 12 measure in all that time apart from a calculation.  I have also found that I have only had to inspect and fix less than 5% of everything I create to be certain it was created the correct way.  Most of my features are created without me looking at them and check out when I do look at them or when other look at them (I have not personally looked at everyone of the 44,000 signs I have created, but when I do I can activate my hyperlink to streetview and see the sign within 5 feet of the position I calculated most of the time).

Offset direction (north/south or east/west) are just handled by multiplying the offset by 1 or -1.  If you built your Route from the upper-left you can predict that north and west are a multiplier of -1 about 90% of time and south or east are a multiplyier of 1 about 90% of the time if your roads are at all cose to being built on a grid.  You can use the normal angle of the intersection point (generated as an option of an LR Event point layer) to find anomolies based on the intersection angle.  An SQL expression can test for anomolous angles relative to your normal expectation.  Typically on diagonal roads where the user may say north/south but GIS says east/west or loop roads where I have two intersections of the same name pair and I have to figure out which end of the road is meant.

[TomMagdaleno]

Ahh, I see the light!  Forget about the routes, and just use the intersection points.  That makes sense.    What if the intersection is at a 45 degree angle or I have curvealinear streets?  The offset may be NW.  Will the offset know to follow the segment?  I want the point in the middle of the road, side doesn't matter.  I'll start looking into this tomorrow.  Thanks again!

[RichardFairhurst]

Ahh, I see the light!  Forget about the routes, and just use the intersection points.  That makes sense.    What if the intersection is at a 45 degree angle or I have curvealinear streets?  The offset may be NW.  Will the offset know to follow the segment?  I want the point in the middle of the road, side doesn't matter.  I'll start looking into this tomorrow.  Thanks again!

Streets at 45 degree angles and curvilinear streets (at least S curves with an intersection in the middle of the S and loops that intersect the same street twice) are problematic to interpret no matter what.  People in the field will make their best guess on 45 degree streets and usually pick something opposite of a nearby east/west or north/south street, which the model cannot "see".  Or else they do not pay attention to the fact that there are two street intersections with the same name.  Of you don't know if they are describing the S relative to its overall direction or its local direction at the intersection.  Whether you have a Thomas Bros or a GIS model, those descriptions remain ambiguous without more information.

If your orientation is built from the upper left than NW (-1) and SE (1) are not a problem.  For NE and SW you have ambiguity that may require looking at the overall trend of the route to make your best guess.  The angle of the intersection can give you a clue, since it rarely is exactly at 45 degrees.

The offset always follows the line as long as it is within the bounds of the line's measures.  If it is really ambigous sometime I just try a multiplier and if it throws an error by being off the line, try the other direction.  If a measure is too low or too high to fall in the Route's measures, you can generate an Error field when you create the Event layer that tells you there is a problem.  When nothing seems to work, I just temporarily enter the known intersection measure from my intersection table so I can use the record to zoom to the intersection and then look the road over before applying the offset multiplier.  I also have a field for making adjustment to the offset distance so that I keep the event on the line (say it overshoots by 5 feet, I will shorten it in the modified offset field).

I also calculate the From and To measures of the Route so I can join to it and see that a measure value is too high.  Too low is usually negative, so it is easy to figure out.  If it is just -20 feet or so then I may just adjust the offset by 20 (very typical in cul-de-sac bulbs).

Anyway, now you are getting the idea.

[TomMagdaleno]

I also calculate the From and To measures of the Route so I can join to it and see that a measure value is too high.  Too low is usually negative, so it is easy to figure out.  If it is just -20 feet or so then I may just adjust the offset by 20 (very typical in cul-de-sac bulbs).

Richard,
  How do you calculate the values of from and to?  It seems like the LR route information is embedded in the line and only works with the identify tool.

[RichardFairhurst]

Richard,
  How do you calculate the values of from and to?  It seems like the LR route information is embedded in the line and only works with the identify tool.

You use a Python calculation.  For the From measure it is:   !shape.firstpoint.M!   And for the To measure it is:  !shape.lastpoint.M!

[TomMagdaleno]

Richard, I'm doing mine a little differently, I don't have From and To lines, everything is point based. 

Here is my issue.  I created multiple points at each intersection with every possible combination of street names.  For example if I have 3 streets coming together my intersection would be made up of 6 points.  That way no matter how my primary and secondary street is written, it will join to this intersection and get this XY.  I perform the join then calculate the measures into my event table.  Then I add or subtract their distances from the intersection from those measures. 

FULTON ST_HARTNELL ST
FULTON ST_PICKWICK DR
PICKWICK DR_FULTON ST
PICKWICK DR_HARTNELL ST
HARTNELL ST_PICKWICK DR
HARTNELL ST_FULTON ST

The problem is when I go to �??locate features along route�?� it doesn�??t know which measurement to apply to which point.  I uncheck "Keep only the closest route location" So my output looks like this�?�

[ATTACH=CONFIG]11654[/ATTACH]

This won�??t work.  The street name on the left in the "Concan" field is the Primary Street and needs to match the RID, so Fulton and Hartnell should be at 450.9697 ft.  The problem is that it may join to my table with 0 or 1977.0115.  So I have to go through 5720 records to clean up the whole city.  This will take weeks!

[RichardFairhurst]

Richard, I'm doing mine a little differently, I don't have From and To lines, everything is point based. 

Here is my issue.  I created multiple points at each intersection with every possible combination of street names.  For example if I have 3 streets coming together my intersection would be made up of 6 points.  That way no matter how my primary and secondary street is written, it will join to this intersection and get this XY.  I perform the join then calculate the measures into my event table.  Then I add or subtract their distances from the intersection from those measures. 

FULTON ST_HARTNELL ST
FULTON ST_PICKWICK DR
PICKWICK DR_FULTON ST
PICKWICK DR_HARTNELL ST
HARTNELL ST_PICKWICK DR
HARTNELL ST_FULTON ST

The problem is when I go to �??locate features along route�?� it doesn�??t know which measurement to apply to which point.  I uncheck "Keep only the closest route location" So my output looks like this�?�

[ATTACH=CONFIG]11654[/ATTACH]

This won�??t work.  The street name on the left in the "Concan" field is the Primary Street and needs to match the RID, so Fulton and Hartnell should be at 450.9697 ft.  The problem is that it may join to my table with 0 or 1977.0115.  So I have to go through 5720 records to clean up the whole city.  This will take weeks!

No it won't take weeks.  It will take two minutes.  If this is in a File Geodatabase write a query that reads:

"Concan" Like CONCAT("RID", '_%')

That will select your primary street.  Now export that set.  It should be what you want.  I have to do this also, but it takes only little time and I don't care if the computer works for its living.

You should not use underscore "_" as your separator, since it is the single character wildcard in file geodatabases.  I am not sure how to write a query on a file geodatabase that takes that character as a literal value in a LIKE statement.  Don't use parenthesis "()" (used by all languages), straight brackets "[]" (Field Calculator field delimiter and sometimes SQL character sets), percent "%" (File geodatabase multi-character wildcard), ampersand "&" (VB Script Concatenator), star "*" (Access multi-character widcard) or question mark "?" (Access single-character wildcard), for the same reasons in that these are special characters in SQL or the field calculator and confuse the join and/or the route event layer, or make it hard to write query logic.  The above will be confused if you had two streets that are only different in the characters that follow the shorter name.  It may occur rarely in a City, but it might happen (in a County or State it occurs too often).  A comma would be a better sperator and would would in that special case with:

"Concan" Like CONCAT("RID", ',%')


That is why I use curly brackets "{}".  They aren't special characters used in SQL or anywhere else and do not confuse queries, break joins or fail for event layer creation.  For me to do a similar query that will not be confused if two streets are only different in the characters that follow the shorter name I do:

"Concan" Like CONCAT(CONCAT('{', "RID"), '}%')

[RichardFairhurst]

Actually now that I think about it I don't create the pairs first.  I run the Locate Features operation on a single point with the names in alphabetical order.  It then creates 3 events at the intersection you mention with three different primary names and three different measures.  Then I go through a set of queries and appends to match the primary street and its measure and create the pairs with the two secondary streets.  It takes only 3 query and append operation if there are any cases of 4 different street names coming together at a single intersection (those 3 appends of 4 records each generate the 12 unique combinations that occur at 4 name intersections).  My approach just means I create fewer records and no wasted records as opposed to your method (which is significant, since the Locate Features operation takes a long time with 90K intersections), but either approach can get to the same outcome.

5,720 records is nothing.  I can deal with that in two days tops once I find a logical pattern.  (I have visually inspected 7,000 records out of 20,000 in the last two days to validate an automated conflation of one line set with another and made correcting edits to about 10%)

[TomMagdaleno]

Richard,
  It works now!  Thank you so much for your consulting help these last few months!  I have been struggling with this project in my free time and it finally works!  Its down to about 5% errors instead of 40%.

[RichardFairhurst]

Richard,
  It works now!  Thank you so much for your consulting help these last few months!  I have been struggling with this project in my free time and it finally works!  Its down to about 5% errors instead of 40%.

While you cannot mark this thread as answered, since you did not start it and it began before the new forum options existed, you can assign one or more of my answers with points.  That will help my MVP standing and guide others to the parts of this thread that were most useful to you.  Glad I could help.

By the way, what kinds of errors still exist?  I assume they are things like someone describing an intersection that does not exist or is not included in your network, or that they misspelled one or more street names, or that they said to go west of an intersection that is at the west end of the road, or they said go 400 feet westerly of an intersection that is only 300 feet from the west end of the road, or that they used an intersection name pair that exists in two places and the join picked the wrong one, etc.  Basically, is the error due to human error, inaccuracy and ambiguity?  That is a never ending complication of data collection.

The other note is that this system will work for other kinds of data.  For example, traffic counts, traffic signals, and traffic signs work as points if described relative to a single intersection.  Pavement conditions of road segments, road construction and improvement projects, and speed limit zones work as lines if you have limit descriptions relative to the two ends.  I have created all of these and more with my system.  I hope you have similar success as you master this approach.