SQL Example: GeomOverlayAdjacent Function

Using the GeomOverlayAdjacent function, this example shows how this function and similar functions such as GeomOverlayContained, GeomOverlayContaining, GeomOverlayIntersecting and GeomOverlayTouching operate.

 

Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.

 

A description of the GeomOverlayAdjacent function from the SQL Functions topic:

 

GeomOverlayAdjacent(<drawing>, <overlay>, <tolerance>) : <table>

Combines fields between a source drawing and an overlay drawing.  Given a drawing and an overlay drawing, returns a table with the following characteristics:

 

  • Columns: The returned table will contain all fields from the source drawing and all fields from the overlay drawing.  Fields from the source drawing will have their names prefixed with s_ for source.  Fields from the overlay drawing will have their names prefixed with o_ for overlay.  

 

  • Rows: Every object in the source drawing that is not adjacent to an object in the overlay drawing will appear as a row in the table with values for the o_ fields set to NULL.   Every object in the source drawing that is adjacent to one or more objects in the overlay drawing will appear in one or more rows in the table with values for the o_ fields populated from the corresponding object to which it is adjacent.

 

A tolerance of zero means automatic tolerance. The table supports all indexes of the source drawing. The overlay is performed in the coordinate system of the source drawing; objects in the overlay drawing are automatically converted to that coordinate system as necessary.   We can use the table created by this function as desired in subsequent operations, for example, creating a drawing via a query that uses aggregates.

 

The table created by the GeomOverlayAdjacent function captures the attributes of objects that are adjacent, along with all geometry required to make further use of the adjacency relationships discovered by the function.   

 

To show how this function operates, we first created two drawings where objects in one drawing are adjacent to some objects in the other drawing.   We started with a drawing that shows the provinces of Mexico, using Latitude / Longitude projection.  We then created two drawings from that original, with areas for the five provinces of Chihuahua, Durango, Yucatan, Chiapas and Michoacan de Ocampo removed from one drawing and placed into the other drawing.   The drawings created from the original Mexico drawing inherit use of Latitude / Longitude projection from the originating Mexico drawing.  We then show the two drawings together in a map.

 

 

The drawing of Mexico with five provinces removed we have named the source drawing.    We have styled it using a range of blue-green colors.

 

 

The drawing containing the five provinces that were removed we have named the overlay drawing.  We have styled the overlay drawing using a range of orange colors.

 

 

With both drawing layer tabs turned on for display in the map we see that the area objects in the overlay drawing precisely fit into the open regions in the source drawing.   That is to be expected since we created the overlay drawing by cutting areas out of the source drawing and putting them into the overlay drawing.   By doing that we have created two drawings where the objects in the overlay drawing are precisely adjacent to some objects in the source drawing.

 

Both drawings originated in the same Mexico drawing and originally both had the same fields.   To make this example more interesting we have opened the tables for the source drawing and for the overlay drawing and then used Edit - Schema to delete some of the fields from each.   That gives us a situation where some of the fields, like NAME, are in both drawings but others of the fields occur only in the source drawing or only in the overlay drawing.

 

 

The source drawing's table has five fields as seen above.  

 

 

The overlay drawing's table also has five fields as seen above.  Four of the fields are the same field names in both tables.

 

To use the GeomOverlayAdjacent function we write the following query in the Command Window:  

 

 

In the illustration above we have entered GeomOver into the filter box so the very long list of functions is reduced down to only those functions with names similar to the function we want.  As text the query is:

 

SELECT * INTO [adjacent_result]

  FROM CALL GeomOverlayAdjacent([source], [overlay], 0);

 

 

The GeomOverlayAdjacent function returns a table that combines fields from objects in the source drawing with fields from objects in the overlay drawing that are adjacent to objects in the source drawing.  We save the created table into a new table called adjacent_result.  We use an automatic tolerance setting of 0.   We could have used the fully parallel version, GeomOverlayAdjacentPar, which also allows us to specify the number of threads to use, but to keep the arguments as simple as possible in this example we have used the GeomOverlayAdjacent function.  In "real life" with real data we would automatically use GeomOverlayAdjacentPar.

 

To run the query we press the ! run button.

 

The result of 29 means 29 records were created in the new table.

 

We can pop open the adjacent_result table to see what was created.

 

 

The table opens with shaded background indicating it is not editable or selectable, a consequence of not having a btree index.   The original indexes from the source and overlay drawings have been brought into the table, of course, but because some of the indexed field values are duplicates they are no longer btree indexes but have been automatically converted to btreedup indexes that allow duplicates.

 

 

We take a moment to launch Edit - Schema to add an mfd_id field to the table and also an mfd_id_x btree index as discussed in the Add an Index to a Table topic.   This makes the table editable and selectable.  

 

 

We can see that the function created the table by bringing in all of the fields from both the source drawing's table and the overlay drawing's table, prefixing the field names with s_ or with o_ respectively so that at a glance we know which fields came from which drawing.

 

Records for objects in the source drawing that do not have an adjacent object in the overlay drawing have NULL for their o_ field values.  

 

Records for objects in the source drawing which do have an adjacent object in the overlay drawing will have their o_ field values populated for that adjacent object.     

 

Objects in the source drawing which have more than one adjacent object in the overlay drawing will appear in as many records as there are adjacent objects.  We have selected in red selection color the cases where two of the objects in the source drawing each are adjacent to two objects in the overlay drawing, so they both appear twice, once for each of the adjacent objects.

 

Notes

We continue using this same data set in the companion SQL Example: GeomOverlayTopologyUnion Function topic.

 

See Also

Tables

 

Indexes

 

Add an Index to a Table

 

Command Window

 

Queries

 

Schema

 

Edit - Join

 

SQL Functions

 

Example: Create and Run a Query -  See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

SQL Example: GeomOverlayTopologyUnion Function - A continuation of this example, using the GeomOverlayTopologyUnion function, an example that shows how this function and similar functions such as GeomOverlayTopologyIdentity, GeomOverlayTopologyIntersect and GeomOverlayTopologyUpdate operate.

 

Example: Overlay : Intersect - In this example we use the Overlay : intersect operation in the Transform pane to trim a drawing of points so that all points which do not fall within areas in a second drawing are deleted.   At the end of the topic, we repeat the operation using the Join dialog in a different approach.

 

Example: Merge : areas (dissolve) - In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option.  A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

 

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the  Transform pane to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.