Populate cross street name at intersections

Question

Just a follow up of an older post seen here:

Python script/Model to populate cross street names at intersections

I need to create a shapefile of Road segments with a "To_Road/s" and "From_Road/s" as tables in the attributes.

In essence I want to create a shapefile with JP van niekerk street segment name between Piet retief street and Sarel Cilliers street. all in their own field.

I used the Planarize tool to separate the roads into segments. I know it has something to do with spatial join and the FID but I can not figure this one out.

I am not proficient enough in ModelBuilder to build the model in the link.

Answer 1

First thing first is clean up the data.

1) Use the UNSPLIT LINE tool with the dissolve field set to the street names.

2) Use the 'Plannerize tool' on the output at step 1 to create segments between each crossing

3) Used the 'intersect tool' to create points at each intersection. (Join Attributes set to FID_Only and the output type should be set to POINTS)

4) Use 'Delete Identical' on the point feature created in step 3.

5) Use Spatial join:

-Target feature is Points feature -Join feature is road feature -Join operation is JOIN_ONE_TO_ONE -Remove all in the Field map of join feature and add a new output field named Intersections. Type = TEXT, Merge Rule = JOIN, Delimiter = ' & ', Add input field = Streetname. -Match Option: Intersect

This should create a field in the point feature class where it lists all the street intersections name at a point.

6) Use "FEATURE VERTICES TO POINTS" tool and create a start and end point respectively from you street feature. Name the resulted layers "Start" and "End"

7) Use Spatial Join to join these layers created in step 6 to your intersections point layer separately,

Name the resulting layers START_SP and END_SP

-Target features: "start" and "end" layers separately, -Join feature: Street intersections point layer, -JOIN_ONE_TO_ONE, -Match Option: Intersect

8) Create "From" and "To" Fields in your Street Segments layer.

9) Use joins (NOT SPATIAL JOIN) : Join your segment feature to START_SP with (match FID to ORIG_FID)

10) Use field calculator to copy the field in "FROM" field created in step 8.

11) Remove the join and repeat step 9 with the END_SP feature.

This should result in a Line feature with all the intersecting street names in their respective field.

Note: The segment name will also be present in the FROM and TO fields. I still need to find a solution to remove the segment name from the FROM and TO fields. Any suggestions?

Answer 2

I don't consider this a complete solution as it relies on non-out-of-the-box tools and isn't fully tested. It also may require an Advanced license. For now I am removing the comments I made linking to questions with methods using the start and end nodes of the lines, as after some testing I'm not sure how to apply that to this problem (while at the same time needing a start/end attribute - see below).

1. The lines must be cleaned up prior to starting. You've already Planarized, which is one step, but you should also check for pseudo-nodes - cases where two segments of the same road meet without any other intersecting roads - because they will cause problems. Other things to watch for or which aren't handled well (if at all) include more than two streets with different names meeting at a junction and dead-end or cul-de-sac roads. Finally, it's always best to work on a copy of data and in this case that has an added benefit. There will be some field duplication, so the fewer fields you have to start with the easier the table will be to follow. I strongly suggest eliminating all fields except Name and if your data has one the unique road segement ID (note this is distinct from the OID/FID).
2. With everything as clean as you can get it, start with a Spatial Join. You'll be joining the road layer to itself (target and join features), join operation one-to-many, match option of intersect. To ease field recognition in the next steps, in the Field Map box rename the name field (or whatever your road name field is called) to SegName and the name_1 field to CrossName. Do the same for the segment ID field if there is one, and remove other fields (like length) from the field mapping. In the resulting layer, each road segment is duplicated once for every road segment touching it, including itself. Open up the attribute table and immediately delete the Join_Count field to get it out of the way. Then note that target_fid is the original OID of the segment being considered and join_fid the original OID of the segments that touch/intersect the target. The name fields are probably aliased to their original 'name' field name - you can right-click > properties them and make the alias match the new names.
3. With all that duplication, some cleanup is needed. Start with two Select by Attributes: first where target_fid = join_fid (the copy of itself) and then where SegName = CrossName (the copies created by the segments of the same road before and after this one). You can delete the results of each selection in turn (entering an Edit Session first of course). Warning: pseudonodes cause the second selection to delete segments that do not touch other roads and will leave gaps/holes in both the road lines and the final attributes (there will be only one populated cross-street). Also, in my sample data I have a case where I end up with a road segment that has the same cross street at both ends, and in my case it isn't an error - the cross street has an offset for a few blocks, so it really does hit the road in two places.
4. There's still some duplicate left, but it requires more advanced methods to eliminate. If you have an Advanced license, you can use the Delete Identical tool (note this directly modifies the data, not create an output layer). Any case where target_fid and CrossName field values match in two different rows is an unnecessary duplicate. Note that if target_fid is the same SegName will be as well, while join_fid will always be different even if CrossName is the same since two different segments of CrossName meet at either end of the segment in question. In fact at this point the join_fid field can be deleted and no longer serves a purpose. If you do not have an Advanced license, Summary Statisics can serve here by using target_fid and CrossName as CASE fields. However it will create a standalone table with no geometry, and some new additional fields to get rid of.

And this is where the solution breaks down. You should have a table now with theoretically only two rows for any given target_fid (possibly one if it's a dead end or more if you have three or more named streets coming together at one intersection). You can look at the table and see that each of the two rows gives you a cross street from one end or the other of the segment. The problem is getting those two row values into columns. I could think of two options that wouldn't work (Transpose Fields and Pivot Table), one that would be overly complex (iterative joins with a complicated where selection to match as many records as possible in steps), and two more that are feasible but require extra steps:

• There is a script in an answer at Summary table with a field that shows components of each summarized record which can combine the two CrossName field values from the matching rows and concatenate them into a single field in a single row with a specified delimiter between.
• Do yet another Spatial Join. This time target is your original roads, join features is the layer you've been working on (if you had to use Summary Statistics and just have a table, it will require additional steps to get back to having geometry to Spatially Join). This time it will be a join operation of one-to-one with a match option of are_identical_to. In the field map box, remove all fields except Name (the original one) and CrossName. In that same box, right-click CrossName and choose properties. Change the Merge Rule dropdown to Join and enter a character that isn't part of any street name (hopefully something simple like &). Note you can right-click CrossName and access the Merge Rule there, but unless you go into properties you can't set the delimiting character. Click ok to do the join, and in the resulting feature class you should have a field (once again with an alias you'll have to remove) that has both cross names separated by your delimiter. If the field is null, it's a result of a pseudo-node issue outlined above. If there's only one name, the road is a dead end that only has a cross street on one end (or possibly has a pseudo-node on the other) or there's an offset in the cross street as noted earlier. If there's more than two names, more than two named streets hit one end or the other.

With either of those methods you now have to split that concatenated field into the two separate cross street name fields you want. You could then add a new field and do it with a field calculation or there may even be a tool for this. I think I saw one while digging around. Note if there are more than two named cross streets, there's no way to know which end they belong to (say Baker and Mylock meet at one end of the segment of Creek and Dwyer at the other, you won't know if the label should read Creek between Baker/Mylock and Dwyer or Creek between Mylock and Baker/Dwyer, etc.). For that you'd need to actually pay attention to start and end nodes, which is an entirely different process.