SQL Example: GeomOverlayAdjacent Function

 

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.

 

See the Overlay Adjacent discussion in the Overlays section of the Transform Templates - Drawings topic.

 

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.   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.    We then show the two drawings together in a map.

 

eg_sql_adjacent01_01.png

 

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.

 

eg_sql_adjacent01_02.png

 

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.

 

eg_sql_adjacent01_03.png

 

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.

 

eg_sql_adjacent01_04.png

 

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

 

eg_sql_adjacent01_05.png

 

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:  

 

eg_sql_adjacent01_06.png

 

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.

 

eg_sql_adjacent01_07.png

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.

 

eg_sql_adjacent01_08.png

 

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.

 

eg_sql_adjacent01_08a.png

 

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.  

 

eg_sql_adjacent01_09.png

 

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

 

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 Contained -  A frequent use of overlays is to sum the values of many points that fall within an area and to transfer that sum to a new field for an area.  In this example we take a drawing that has cities in the US with a population value for each city.  We use Overlay Contained  to sum the population of each city within a state and to transfer that sum to a total population for the state.

 

Example: Overlay Containing - One of the most common uses of overlays is to transfer fields from areas to points that are contained in those areas.    Tasks such as transferring a census block group number or zip code number from a drawing of areas to points that fall within each area are extremely common.   In this example we transfer the name of a French region  to the points that represent cities which fall within each region.

 

Example: Overlay Topology Intersect - In this example we use the Overlay Topology, Intersect template in the Transform panel to trim a drawing of points so that all points which do not fall within areas in a second drawing are deleted.   The drawing of points we trim will become the US cities drawing that is used in the Example: Overlay Contained topic.

 

Example: Union Areas - Combine multiple area objects into a single area.   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: Learning to Union Areas in SQL from Edit Query - We learn how to write an SQL query that does a custom Union Areas operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform panel template, an exploration of the difference between using Copy and Sum for transfer options.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the  Transform panel 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: Transfer Options and Merge Areas - Using the Merge Areas Transform panel template, an exploration of the difference between using Copy and Sum for transfer options.

 

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