Spatial Join’s hidden trick or how to transfer attribute values in a One to Many relationship


From time to time, even the experienced GIS users (my 17 years in GIS allow me to call myself one) come across hidden tools and workflows that have existed in ArcGIS for Desktop for ages, but have never been used.

Recently, a couple of my clients asked me how to solve the following problem. Imagine you had a polygon feature class representing cadastral boundaries (i.e. properties) and another one representing zones (i.e. zoning). One property can fall within several adjacent zoning polygons, which effectively defines the cardinality (or the type of the relationships between features in both datasets) as One to Many (Properties to Zones).

The following simplified graphic illustrates the case (very schematically, I must add):

SpJoin_01

What if you were provided with the task to transfer the attribute of the “Zone code” to the attribute table of the property boundaries?

Well, obviously some of the properties intersect two zones and in the real-world example they can intersect even more zones.

If you use the Spatial Join tool with the following parameters (even if you try to use both types of relationships available in the tool)

SpJoin_02

You will end up with having multiple overlapping polygons:

SpJoin_03

Overlay tools won’t be of help either. If you use Union, you will end up with even more polygons and you’ll get stuck trying to aggregate the resulting polygons using Dissolve (which won’t work because Zone is a text field and you can’t really use a text field in statistical calculations).

You can try Python and that’s what I was about to do; however, it turned out that the Spatial Join tool which I had been using for many years contains a hidden function. That function allows to list the attribute values from a particular field (of the feature class that you’re joining to the target layer) and enter them into a designated field using a user-defined delimiter.

So, this is how this works:

  • In the Spatial Join tool’s dialog navigate to the Zoning field in the dataset that is being joined and Right-click on it. Choose Properties.

SpJoin_04

 

  • Set the following:
    • Length: set the value which will be capable of storing the appropriate number of characters
    • Merge Rule: use Join
    • Delimiter: use any delimiter you like

SpJoin_05

  •  Set the cardinality in the Spatial Join tool to “One to One”
  • Run the tool.

The resulting dataset should display zones that each of the property boundary polygons intersect, stored as text values separated by the delimiter of your choice.

SpJoin_06

Little things like this can save you a lot of time that you could otherwise spend on writing python scripts or changing the attributes manually. I hope you will find this workflow useful.

This entry was posted in General and tagged , on by .

About Ivan E.

Russia-born geographer with a degree in Cartography and GIS with a 18-year Esri-related experience. Had been working for Esri Russia (CIS) then moved to Australia to join Esri Australia as support trainer in the Professional services dept. Area of expertise: ArcGIS Desktop software, 3D modelling (CityEngine), cartography, remote sensing

14 thoughts on “Spatial Join’s hidden trick or how to transfer attribute values in a One to Many relationship

  1. Christina

    I am using this technique which I too just discovered after 13yrs of GISing, but for my purposes I need only boundary polygons which are completely within the join feature, each time I try it with the COMPLETELY_WITHIN match option I encounter a “Background geoprocessing error”.
    I wonder if you have encountered an error like this or can suggest a workaround?

    Reply
    1. Ivan E. Post author

      Hi Christina,
      Unfortunately, it’s pretty hard to say why it’s crashing, without having some additional information, such as version of ArcGIS that you’re using, maybe a sample of the data etc.
      I would recommend you to contact Esri Australia’s technical support team (support@esriaustralia.com.au) or your local Esri distributor, if you’re not in Australia. Our tech.support people will help you to troubleshoot this problem.

      Reply
    2. Christina

      Do you have background geoprocessing enabled? If so, try disabling it. Geoprocessing > Geoprocessing options

      Reply
      1. Christina Tardif

        Yes I did try disabling. It still was not able to complete the function. I contacted ESRI support and was told that my data was too complex. I gave them a sample of it and they were not able to get the join with completely_within to complete successfully either. Disappointing.

    3. Jay Higgins

      I have also had issues getting this to work while trying to get a COMPLETELY_WITHIN match. After I turned off Background Processing it will still freeze. This is with 10.2. Error messages never reveal more as they never seem to populate prior to freezing.

      Reply
  2. Martie Clemons

    Tried this using parcels for the target and zoning districts for the join but 18% were unpopulated. I used INTERSECT because the two are much like Ivan’s simplified graphic. My experience with SPATIAL JOIN is that it’s very fickle in this way but it would be wonderful it worked and was dependable.

    Reply
  3. Martie Clemons

    I tried this and it worked for some parcels (got multiple zoning districts) but came up NULL for about 18% of them. Would be lovely if it wasn’t so fickle. No apparent pattern emerged when I looked at the NULL parcels. So it’s back to ONE TO MANY JOIN then using a script to concatenate the multiple zoning records by APN into a new field. Hope this works!

    Reply
  4. Nick

    Very useful, thanks. One point to note: the merge rule “Join” is only available after selecting the Type “Text”. That’s obvious to seasoned users but maybe not the ArcMap noob (like myself).

    Reply
  5. Chris Willett

    Great article! This method has been very helpful, though occasionally I have to tweak it: If a parcel polygon and a zone polygon share part of a boundary or a vertex, the Spatial Join intersect method will still transfer the attributes even if those two polygons do not share any area. To work around this problem, I perform an intersect between the two layers first and then join the intersection results to the target layer using the completely contains method. That seems to solve the problem.

    Reply
  6. Daniel Moreno

    Just what I needed. I have to build a list of transmission lines that feed into/out of electric substations. Thank you!

    Reply
  7. Sabelo Mguli

    Hello. I am completely new to GIS and these analysis tools.
    Please assist; “Well, obviously some of the properties intersect two zones and in the real-world example they can intersect even more zones.” quoted from the Ivan’s text above.

    I have census tracts data that I am trying to join with police precicincts data, the two layers are of differing scales (some polygons of police “cut” the census in “half” some are completely within). So how do I set the process to take that into account by using some proportionality rule like: if polygon A(census) is intesecting polygon B(police) by 70/30 split, then allocate certain numeric attribute values to the target feature.

    Perhaps there is another tool or method for joining these and take into account the split or containment of two spatial layers.

    I hope I am making sense. I am completely stuck!

    Reply
    1. Nicholas Kobel

      Use the Tabulate Intersection tool (Analysis tools > Statistics > Tabulate Intersection). Without seeing your data, it’s hard to say exactly how to set this up. Play around with it.

      Reply
  8. Georgie

    It would be great if this delimited field concatenation was available for attribute joining. I am joining a polygon layer (Cadastre) to multiple records from a non-spatial table. I would love to be able to stack the multiple values that are joined (eg. Owners names) into a comma delimited single value. Is there such capability existtng in the tools ? I currently have to use SQL to create a table with this stacked value and then I join these afterward to my polygon layer.

    Reply

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s