Spatial Join Example: Add Names of Provinces to Cities

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.   Spatial joins done by the Join dialog are an easier way to accomplish classic GIS "overlay" operations.  Adding attributes from polygonal areas, like states or provinces, to points using a Join is also faster than using a geocoder to do the same, especially when many points are involved.

 

The Edit - Join dialog provides spatial joins between drawings that are layers in maps.    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. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  7. Press Join.

Example

This example provides a drawing to drawing spatial join:  Given a drawing of cities and a drawing of provinces, we copy the name of the province in which it is located to each city.  

 

 

We start with a map of France that has a layer called Cities that shows major cities in France as points, and a layer called Provinces that shows pre-2016  provinces in France as areas.  

 

 

Opening the Cities Table we see that the drawing has only the name of each city.   It does not have the name of the province in which the city is located.

 

 

Opening the Provinces Table we see it has the name of each province.   We would like to copy the name of the province for each city into a new field, called Provinces, in the Cities drawing.

 

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

 

 

We choose the Provinces drawing as the joined drawing.   We choose contained in as the spatial method for the join.  We want to match all cities contained in each province with that province.

 

 

adjacent to

An original object is adjacent to a joined object if both objects have at least one boundary location in common but have no interior locations in common.  Any locations in common between an original object and a joined object are only on the boundaries of the objects, and nowhere else.

An object that is adjacent to another object also touches that other object, but it does not intersect the other object.  Objects that are contained by or which contain another object are not adjacent.

contained in

An original object is contained in a joined object only if all locations in the original object are entirely within the joined object.

An object that is contained in another object also touches that other object but it is not adjacent to and it does not intersect that other object.

contains

An original object contains a joined object if all locations in the joined object are entirely within the original object.

An object that contains another object also touches that other object but it is not adjacent to and it does not intersect that other object.

intersects

An original object  intersects a joined object if both objects have at least one interior location in common and also at least one exterior location not in common.

An object that intersects another object also touches that other object, but it is not adjacent to, is not contained in, and does not contain the other object.

touches

An original object touches a joined object if there is any location in common between the two.

One object that touches another object may or may not be intersecting, and it may or may not be adjacent to the other object.  Objects that are contained by or which contain another object are also touching.

 

 

 Press the Add button to add a new field.

 

 

All fields in the Provinces drawing will appear.  We choose Province.

 

 

Press Join.

 

 

A new Province field is added to the Cities Table.  The name of the province for each city is copied from the Provinces Table into the new field in the Cities Table.

 

Notes

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 ...id 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.

 

Videos

Join Videos

 

Find Percentages of Open Space in ZIP Code Area - Find the percentage of open space in each ZIP code area given a layer of polygons representing ZIP codes and a layer of polygons showing open spaces like parks and green spaces. This video shows how to do that start to finish in a few simple steps, from initial importing of shape files to final results, in just five minutes, with an additional few minutes of explanation what each step does. Works in Manifold Release 9 or using the free Manifold Viewer.

 

See Also

Tables

 

Queries

 

Editing and Combining Data

 

Join

 

Command Window

 

JOIN Statements

 

Editable Results Tables

 

Example: Use Edit - Join to Map a Pandemic - Creating a data source for a CSV web server, we acquire current data on cases and deaths worldwide for the Covid-19 pandemic.  We then use the point and click Edit - Join dialog to rapidly join that data, automatically aggregated as we desire, into a world map for visualization.

 

Example: Create a Map Showing OSM Use by Country - A start-to-finish real life example of map creation that combines various Manifold capabilities, including use of Edit - Join.  Copying a table of numbers from a web site, we create a map that is thematically colored to show usage of OpenStreetMap by country in proportion to the population of that country.

 

Example: Trace Vector Areas from Raster Pixels - This example follows the Trace Vector Areas from Raster Pixels video on the Gallery page.   We use the Trace Areas template in the Transform pane for images to create a drawing with vector areas covering regions of similarly-colored pixels.  Next, we use a simple query to add classification codes from a USGS table of classes to the resulting drawing, using a simple INNER JOIN SQL statement.

 

Example: Import E00 and Rearrange - An intricate example showing how to use Manifold tools to adapt legacy data imported from E00 format into useful, modern form.