Spatial Join Example: Add the Sum of City Populations to States

Spatial joins between drawings use spatial relationships between the geometry of objects in the source, joined drawing and the geometry of objects in the target, original drawing to join data into the original drawing.   The Edit - Join dialog provides spatial joins between drawings that are layers in maps.    Spatial joins done by the Join dialog are an easier way to accomplish classic GIS "overlay" operations.    Spatial joins work between drawing layers in the same map.  Layers can be in different data sources.


Overlays in ESRI nomenclature are called spatial joins in the data science / IT world, two different terms to describe exactly the same thing.  The Edit - Join dialog is a more modern way to do the same jobs with easier workflow.  Edit - Join, for example, updates the target table or drawing in place.   Even skilled ESRI people often prefer Edit - Join.


Add fields using a spatial join:


  1. With the focus on a drawing layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined drawing in the map from which fields will be joined.

  3. In the second row of boxes, choose the geometry fields to use and the spatial method, such as contains or contained in.

  4. Click the Add button to choose a field in the joined drawing's table that will provide data for a new field in the original table.

  5. Double-click a cell to edit the field name (new fields) or to choose a different source field (joined table) or to choose a different transfer method.

  6. Press Join.


This example provides a drawing to drawing spatial join:  Given a drawing of cities in the US, with a population for each city, and a drawing of states, we sum the population of each city located in a state to get the population for that state.  


See the video version of this example in the Join Dialog Part 1 -  Spatial Joins video.  



We start with a map of the US that has a layer called US cities that shows major cities in the US as points, and a layer called US states that shows lower-48 states in the US as areas.  



Opening the US cities Table we see that the drawing has the name of each city and the population of that city.   



Opening the US states Table we see it has the name of each state, but it does not have the population for each state.   We would like to create a new field,  called Population, that will provide the population for each state by summing the populations of all cities within that state, taken from the US cities drawing.


With the focus on the US states layer in the open map window, we choose Edit - Join from the main menu.


We choose the US cities drawing as the joined drawing.   We choose contains as the spatial method for the join.  We want to match all cities that each state contains so we can sum their population.  


 Press the Add button to add a new field, the Population field, from the joined drawing.



We next choose sum as the join method.   Since we are joining a numeric field we have many choices.



Press Join.



A new Population field is added to the US states Table.  The new Population field is filled with the sum of the populations for each city that state contains, taken from the Population field in the Cities Table.



Terminology - The original table also may be called the target table, and the table providing data also may be called the source table.


Quick reference - With the Join dialog open, press F1 to open a quick reference guide in a browser window.


Guessing the key field - When we launch the dialog with an original field and when we choose a joined field, the dialog will try to guess what fields we might want to use as key fields and will load those first by default.  If it guesses wrong, we can choose the field we want.   For the original table, the dialog tries to use a field (other than mfd_id) that has a BTREE / BTREENULL index, with a data type preference for numbers, then text, then everything else, and a name preference for field names with id in them, such as ... id or ..._id, then and then everything else. For the joined table, the dialog uses similar logic, but first tries to use a field with the same name as in the original table.


Added fields are static - Added fields are static, that is, if data in the joined table changes the fields in the original table that received data in the Join operation from the joined table will not automatically be updated.  We can easily update the table any time we want using a saved update query.  


Spatial joins are automatically parallel - Joining data between drawings uses parallel GeomOverlay function variants.  Joins from images to drawings are parallelized through a nested SELECT using THREADS.



See the Join Videos list of videos showing how to use the Join dialog.

See Also











Editing and Combining Data




Join Videos


Join Examples


Command Window


JOIN Statements


Editable Results Tables