Need help combining intersection points, attributes

3449
6
Jump to solution
08-10-2012 12:03 PM
JerryLawson
New Contributor II
Hello,
I have an "Intersections" point layer that I intersected with my street center-line file with the goal of getting the street names and classifications of the streets which form the intersection.  The result is a point layer with a point at the intersection for each of the road segments that form the intersection.  So, for instance, a four way intersection of Polk St and 30th St will have four points.  Two for Polk and two for 30th Streets, the difference being the street segment ID.

How do I combine those four points into one point that holds all the attribute information?  I need the point to include all the street names that come into it, and the classifications as well, but I'm stuck with having all that in multiple points right now.
Thanks!
0 Kudos
1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor
I have adapted Mike Hargrave's method for creating a single point per intersection with all associated street names.  Since you have already done the intersect part to get the points for each segment I have left that part out.

1) Calculate X and Y values on the points that have the Street information into two double fields using Calculate Geometry.

2) Add a DissolveID text field and calculate a concatenation of your X and Y fields in the form:  X_Y

3) Dissolve on DissolveID field to end up with only one point per intersection and just the field DissolveID value.

4) Do a Spatial Join with your target being the intersection points that only contain a DissolveID field, and the Join Features being the undissolved points that still have all of the Street information.  Make sure you use the JOIN_ONE_TO_ONE option.

In the Spatial Join Tool right click on any field that you want to have every value retained in the "Field Map of Join Features" window and choose Properties.  In the property window set the Field Type to Text.  Increase the number of characters allowed to something like 200 so that there is room for all Street Names (less for short text values and most numbers and dates).  Set the Merge Rule to Join.  Add a Delimiter of "_". 

5) Run the Spatial Join tool.  All street segments at the intersection will be included.  Your end result in the Street Name field should look something like "Polk St_Polk St_30th St_30th St" and your Type field should look like "Maintained_Not Maintained_Not Maintained_Maintained".  The Street Name and Type order will be in whatever order the points are read, but they will correspond to each other.  I believe that if you want them in a certain alphabetical order you would have to first permanently sort the points (Sort tool if you have ArcInfo or Criss Snyder's Permanently Sort Features tool").

If you only want each name to appear once (and I think in alphabetical order) do another Dissolve of all of the segment points and only keep DissolveID and Street Names values (do not keep the type if you want to avoid repeats).  When you Spatially Join this second Dissolve with the Dissolve of Step 3 it will only have each Street Name appear once and be in sorted order, resulting in "30th St_Polk St".  keep the Count in a Text field so that you can get "2_2", telling you it is a 4 way intersection with each Street Name having 2 segments.

6) Use Field calculator to replace the "_" character with " & " - set the Parser to Python, then use the following: !YourFieldName!.replace('_',' & '). Your final street combination will look like "Polk St & Polk St & 30th St & 30th St" your Type combination would look like "Maintained & Not Maintained & Not Maintained & Maintained" if you wanted to keep correspondence between each segmentIDs  Street Name and Type association.

If you only wanted unique Street Name combinations and did the second Dissolve on Street Names  then the result would be "30th St & Polk St"

7) If you did the only use each name once option and a point does not contain the " & " characters, it is either a continuation of the street with a node (Count would be "2" or more if you joined its values from the Dissolve) or a Cul-de-Sac, Stub Street, or Topological Error (Count would be 1 if you joined its values from the Dissolve).

See the screen shot for a typical Spatial Join tool set up, Field Property set up, and result.  In this case I joined all of the points from every segment and did not sort the points prior to doing the Spatial Join.

View solution in original post

0 Kudos
6 Replies
RichardFairhurst
MVP Honored Contributor
Hello,
I have an "Intersections" point layer that I intersected with my street center-line file with the goal of getting the street names and classifications of the streets which form the intersection.  The result is a point layer with a point at the intersection for each of the road segments that form the intersection.  So, for instance, a four way intersection of Polk St and 30th St will have four points.  Two for Polk and two for 30th Streets, the difference being the street segment ID.

How do I combine those four points into one point that holds all the attribute information?  I need the point to include all the street names that come into it, and the classifications as well, but I'm stuck with having all that in multiple points right now.
Thanks!


I have seen some user use the Spatial Join tool with the One-to-One option and a rule to merge data for the street names or IDs into a single field with comma delimiters.  I have an ArcInfo license and use Collect Events, a join field of a concatenated X/Y pairs string, Summary, Pivot Table and field calculator to transfer data from a column of multiple features into a single row.  Search on those tools.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I have adapted Mike Hargrave's method for creating a single point per intersection with all associated street names.  Since you have already done the intersect part to get the points for each segment I have left that part out.

1) Calculate X and Y values on the points that have the Street information into two double fields using Calculate Geometry.

2) Add a DissolveID text field and calculate a concatenation of your X and Y fields in the form:  X_Y

3) Dissolve on DissolveID field to end up with only one point per intersection and just the field DissolveID value.

4) Do a Spatial Join with your target being the intersection points that only contain a DissolveID field, and the Join Features being the undissolved points that still have all of the Street information.  Make sure you use the JOIN_ONE_TO_ONE option.

In the Spatial Join Tool right click on any field that you want to have every value retained in the "Field Map of Join Features" window and choose Properties.  In the property window set the Field Type to Text.  Increase the number of characters allowed to something like 200 so that there is room for all Street Names (less for short text values and most numbers and dates).  Set the Merge Rule to Join.  Add a Delimiter of "_". 

5) Run the Spatial Join tool.  All street segments at the intersection will be included.  Your end result in the Street Name field should look something like "Polk St_Polk St_30th St_30th St" and your Type field should look like "Maintained_Not Maintained_Not Maintained_Maintained".  The Street Name and Type order will be in whatever order the points are read, but they will correspond to each other.  I believe that if you want them in a certain alphabetical order you would have to first permanently sort the points (Sort tool if you have ArcInfo or Criss Snyder's Permanently Sort Features tool").

If you only want each name to appear once (and I think in alphabetical order) do another Dissolve of all of the segment points and only keep DissolveID and Street Names values (do not keep the type if you want to avoid repeats).  When you Spatially Join this second Dissolve with the Dissolve of Step 3 it will only have each Street Name appear once and be in sorted order, resulting in "30th St_Polk St".  keep the Count in a Text field so that you can get "2_2", telling you it is a 4 way intersection with each Street Name having 2 segments.

6) Use Field calculator to replace the "_" character with " & " - set the Parser to Python, then use the following: !YourFieldName!.replace('_',' & '). Your final street combination will look like "Polk St & Polk St & 30th St & 30th St" your Type combination would look like "Maintained & Not Maintained & Not Maintained & Maintained" if you wanted to keep correspondence between each segmentIDs  Street Name and Type association.

If you only wanted unique Street Name combinations and did the second Dissolve on Street Names  then the result would be "30th St & Polk St"

7) If you did the only use each name once option and a point does not contain the " & " characters, it is either a continuation of the street with a node (Count would be "2" or more if you joined its values from the Dissolve) or a Cul-de-Sac, Stub Street, or Topological Error (Count would be 1 if you joined its values from the Dissolve).

See the screen shot for a typical Spatial Join tool set up, Field Property set up, and result.  In this case I joined all of the points from every segment and did not sort the points prior to doing the Spatial Join.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I wanted to amend the instructions above, since Step 6 is unnecessary if you use the Delimiter you want to end up with in Step 4.  Therefore, you can use " & " as the Delimiter in Step 4.

Also, I recommend against using "_" as a Delimiter, since this is a wildcard character for a file geodatabase.  You cannot use a LIKE SQL query with that character or a wildcard character of any particular database.  So avoid _, %, *, ?, etc.

I also have found that joins do not work properly for some databases if I use "(" or ")" as separators.  However, "{" and "}" work fine.
0 Kudos
JerryLawson
New Contributor II
I have adapted Mike Hargrave's method for creating a single point per intersection with all associated street names.  Since you have already done the intersect part to get the points for each segment I have left that part out.

1) Calculate X and Y values on the points that have the Street information into two double fields using Calculate Geometry.

2) Add a DissolveID text field and calculate a concatenation of your X and Y fields in the form:  X_Y

3) Dissolve on DissolveID field to end up with only one point per intersection and just the field DissolveID value.

4) Do a Spatial Join with your target being the intersection points that only contain a DissolveID field, and the Join Features being the undissolved points that still have all of the Street information.  Make sure you use the JOIN_ONE_TO_ONE option.

In the Spatial Join Tool right click on any field that you want to have every value retained in the "Field Map of Join Features" window and choose Properties.  In the property window set the Field Type to Text.  Increase the number of characters allowed to something like 200 so that there is room for all Street Names (less for short text values and most numbers and dates).  Set the Merge Rule to Join.  Add a Delimiter of "_". 

5) Run the Spatial Join tool.  All street segments at the intersection will be included.  Your end result in the Street Name field should look something like "Polk St_Polk St_30th St_30th St" and your Type field should look like "Maintained_Not Maintained_Not Maintained_Maintained".  The Street Name and Type order will be in whatever order the points are read, but they will correspond to each other.  I believe that if you want them in a certain alphabetical order you would have to first permanently sort the points (Sort tool if you have ArcInfo or Criss Snyder's Permanently Sort Features tool").

If you only want each name to appear once (and I think in alphabetical order) do another Dissolve of all of the segment points and only keep DissolveID and Street Names values (do not keep the type if you want to avoid repeats).  When you Spatially Join this second Dissolve with the Dissolve of Step 3 it will only have each Street Name appear once and be in sorted order, resulting in "30th St_Polk St".  keep the Count in a Text field so that you can get "2_2", telling you it is a 4 way intersection with each Street Name having 2 segments.

6) Use Field calculator to replace the "_" character with " & " - set the Parser to Python, then use the following: !YourFieldName!.replace('_',' & '). Your final street combination will look like "Polk St & Polk St & 30th St & 30th St" your Type combination would look like "Maintained & Not Maintained & Not Maintained & Maintained" if you wanted to keep correspondence between each segmentIDs  Street Name and Type association.

If you only wanted unique Street Name combinations and did the second Dissolve on Street Names  then the result would be "30th St & Polk St"

7) If you did the only use each name once option and a point does not contain the " & " characters, it is either a continuation of the street with a node (Count would be "2" or more if you joined its values from the Dissolve) or a Cul-de-Sac, Stub Street, or Topological Error (Count would be 1 if you joined its values from the Dissolve).

See the screen shot for a typical Spatial Join tool set up, Field Property set up, and result.  In this case I joined all of the points from every segment and did not sort the points prior to doing the Spatial Join.



This worked well, thank you!  I was unaware of the field Properties in the Join dialog.  Much appreciated information!
0 Kudos
RichardFairhurst
MVP Honored Contributor
This worked well, thank you!  I was unaware of the field Properties in the Join dialog.  Much appreciated information!


Here is another suggestion that may help.  I find it is very useful to have a point layer containing every street name pair in all possible orders for each intersection.  So 2 street names have 2 pairs (A ST & B ST; B ST & A ST), 3 names at an intersection have 6 pairs, and 4 names at an intersection have 12 pairs.

These combinations may seem repetitive, but it really helps to not have to worry about name arrangement or alphabetical order when trying to match user provided cross-street names to your intersection names, especially when users do not spell them accurately.  For example, if a user gave me "B AVE" at  "A AVE", and I get no match, I can easily select STREET LIKE 'B %' AND CROSS_STREET LIKE 'A %' to find that only B ST crossing A ST exists in my network.  If names were only in alphabetical order I would have had to remember to first switch the name order in my query to get the match (which I never remember to do until I notice I am getting no hits on names that I know I have in my network).

To Create such a point layer, do the Spatial Join of the Dissolved layers with each Dissolve_ID and Street Name to itself (you can put it in twice).  Specify that you want the ONE_TO_MANY option and Keep All Target values (the join merge rule and delimiter do not apply).  You can use the Field Properties to rename the second set of fields to label them as CROSS_STREET, CROSS_TYPE, etc.  The output will include real street name pairs, together with pairs that have the same street name for both names.  Just select and export the pairs where STREET_NAME and CROSS_STREET are not the same and your export will contain every possible street name pair in every possible order from your network.

This pairing arrangement is especially useful if it is extended to include a Route ID and Measure of each street at an intersection derived from a Linear Referencing route network.  You could use Locate Features On Route with your original points or the above pairing output if you only have ArcView or ArcEditor, but it is a slow process. 

If you have ArcInfo, you can use Features To Lines on your Linear Reference Route Network to segment your routes at each intersection.  Then use Feature Vertices to Points with the BOTH option to get the segment end points that have M values.  Create a field called MEAS and calculate the M value of each point into it with the formula: !Shape.Centroid.M!

If you only have ArcView or ArcEditor you can alternatively create a copy of your Route Network and in Editor select every Route and then Planarize it from the Topology Tool Bar to segment it at each intersection.  Then you can calculate an X, Y and M set of fields for each end point (calculate !Shape.FirstPoint.M! and !Shape.LastPoint.M! to get M values from the start and end of the line) and export to table those values with the Route IDs and street names.  Then Create two X/Y Event layers for each end and export it to get actual points with Route ID and M values associated with the street name.  Merge or Append the two exports together to get the complete set of network end points.

Dissolve these points and use the X_Y Dissolve ID, Street_Name, Route ID and Meas as your case fields.  These Dissolved points would then be the inputs for the Spatial Join described above.  Export the intersection points that have different primary and cross street names where the primary street is correctly matched with the Route ID and Measure values.  With this output you can find Primary Streets for every intersection by its Street Name and Cross Street pair (possible duplicates in multiple locations), Street Name and X/Y coordinate pair (unique location), or its Route ID and Measure pair (unique location assuming routes have constantly increasing measures).
0 Kudos
JavadSalimi
New Contributor III
Hi there,

This doesn't seem to work with 10.0 SP4 or SP5. Have you had a chance to try this again recently? After running the spatial join tool, I get an output that consists only of the points in my dataset that did not have any other points with the same geometry.

Thank you




I have adapted Mike Hargrave's method for creating a single point per intersection with all associated street names.  Since you have already done the intersect part to get the points for each segment I have left that part out.

1) Calculate X and Y values on the points that have the Street information into two double fields using Calculate Geometry.

2) Add a DissolveID text field and calculate a concatenation of your X and Y fields in the form:  X_Y

3) Dissolve on DissolveID field to end up with only one point per intersection and just the field DissolveID value.

4) Do a Spatial Join with your target being the intersection points that only contain a DissolveID field, and the Join Features being the undissolved points that still have all of the Street information.  Make sure you use the JOIN_ONE_TO_ONE option.

In the Spatial Join Tool right click on any field that you want to have every value retained in the "Field Map of Join Features" window and choose Properties.  In the property window set the Field Type to Text.  Increase the number of characters allowed to something like 200 so that there is room for all Street Names (less for short text values and most numbers and dates).  Set the Merge Rule to Join.  Add a Delimiter of "_". 

5) Run the Spatial Join tool.  All street segments at the intersection will be included.  Your end result in the Street Name field should look something like "Polk St_Polk St_30th St_30th St" and your Type field should look like "Maintained_Not Maintained_Not Maintained_Maintained".  The Street Name and Type order will be in whatever order the points are read, but they will correspond to each other.  I believe that if you want them in a certain alphabetical order you would have to first permanently sort the points (Sort tool if you have ArcInfo or Criss Snyder's Permanently Sort Features tool").

If you only want each name to appear once (and I think in alphabetical order) do another Dissolve of all of the segment points and only keep DissolveID and Street Names values (do not keep the type if you want to avoid repeats).  When you Spatially Join this second Dissolve with the Dissolve of Step 3 it will only have each Street Name appear once and be in sorted order, resulting in "30th St_Polk St".  keep the Count in a Text field so that you can get "2_2", telling you it is a 4 way intersection with each Street Name having 2 segments.

6) Use Field calculator to replace the "_" character with " & " - set the Parser to Python, then use the following: !YourFieldName!.replace('_',' & '). Your final street combination will look like "Polk St & Polk St & 30th St & 30th St" your Type combination would look like "Maintained & Not Maintained & Not Maintained & Maintained" if you wanted to keep correspondence between each segmentIDs  Street Name and Type association.

If you only wanted unique Street Name combinations and did the second Dissolve on Street Names  then the result would be "30th St & Polk St"

7) If you did the only use each name once option and a point does not contain the " & " characters, it is either a continuation of the street with a node (Count would be "2" or more if you joined its values from the Dissolve) or a Cul-de-Sac, Stub Street, or Topological Error (Count would be 1 if you joined its values from the Dissolve).

See the screen shot for a typical Spatial Join tool set up, Field Property set up, and result.  In this case I joined all of the points from every segment and did not sort the points prior to doing the Spatial Join.
0 Kudos