SQL Example: GeomOverlayTopologyUnion Function

This topic uses the same data described in the SQL Example: GeomOverlayAdjacent Function topic.    Please review that companion topic before proceeding.

 

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.

 

In this example we use the GeomOverlayTopologyUnion function, to illustrate how this function and similar functions such as GeomOverlayTopologyIdentity, GeomOverlayTopologyIntersect and GeomOverlayTopologyUpdate operate.

 

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

 

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

Slices all objects in each drawing with the area boundaries of objects in the other drawing and then places geoms for all pieces, discarding duplicates, into the result table.  See the discussion and illustrations for Topology Overlay, Union in the Transform: Overlay Topology topic.   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 for non-intersecting objects: Every object in the source drawing that is not intersected by 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 overlay drawing that is not intersected by an object in the source drawing will appear as a row in the table with values for the s_ fields set to NULL.   

 

  • Rows for intersecting objects: Every object in the source drawing that is intersected by one or more objects in the overlay drawing will appear as a single row in the table with values for the o_ fields set to NULL but will have the geom altered to remove all regions of intersection from the object.  Each region of intersection will appear as a row in the table with s_ fields and o_ fields populated by the respective objects from the source and overlay drawings that intersected.

 

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 GeomOverlayTopologyUnion function captures the attributes of objects in the source and overlay tables along with all geometry required to make further use of the intersection relationships discovered by the function.   

 

To show how this function operates, we will consider two examples.   In the first example we will utilize the same source and overlay drawings that were used in the SQL Example: GeomOverlayAdjacent Function example topic.   To create the drawings used in that topic we started with a drawing showing all of the provinces of Mexico and then we cut five of the provinces out of the drawing to create a second, overlay drawing, shown below using orange colors.

 

 

eg_sql_adjacent01_01_sm.png   eg_sql_adjacent01_02_sm.png  eg_sql_topo_union01_04_sm.png

 

Those drawings were created to show adjacency.   The sample drawings were created by cutting some objects out of the source drawing and then pasting them into an overlay drawing to ensure that none of the objects in the overlay drawing overlapped, that is, intersected, any of the objects in the source drawing.    Applying a topology union to those drawings simply puts the two drawings back together into a single drawing.

 

eg_sql_topo_union01_07_sm.png   eg_sql_topo_union01_18_sm.png

 

In the second example in this topic we will look at a more complex situation where the objects in the overlay drawing overlap, that is, intersect, objects in the source drawing.  We create a overlay drawing that has a single, triangular object in it and then we do a topology union using that overlay drawing with a source drawing that is the original states drawing that shows all of the provinces of Mexico.

 

First Example

We begin by using the same source and overlay drawings used in the SQL Example: GeomOverlayAdjacent Function topic.

 

eg_sql_adjacent01_01.png

Our source drawing began as a drawing of provinces in Mexico, from which five provinces were cut.

eg_sql_adjacent01_02.png

 

The cut provinces were used to create a new, overlay, drawing.   As a result the areas in the overlay drawing precisely align with and are adjacent to the areas in the source drawing.

 

eg_sql_topo_union01_01.png

To do a topology union between the source and overlay drawings we write the above query in the Command Window, using the GeomOverlayTopologyUnion function.

 

As text, the SQL is:

 

SELECT * INTO [union_result]

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

 

The GeomOverlayTopologyUnion function returns a table that combines fields from objects in the source drawing with fields from objects in the overlay drawing when presenting a table full of the topology union whole objects and intersected pieces.  We save the created table into a new table called union_result.  We use an automatic tolerance setting of 0.   We could have used the fully parallel version, GeomOverlayTopologyUnionPar, 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 GeomOverlayTopologyUnion function.  In "real life" with real data we would automatically use GeomOverlayTopologyUnionPar.

 

To run the query we press the ! run button.

 

eg_sql_topo_union01_02.png

 

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

 

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

 

eg_sql_topo_union01_03.png

 

To simplify our presentation, in the illustration above we have already added a mfd_id field and a btree index on that field.  We have hidden the new mfd_id field and other fields to simplify the illustration.   The table includes all of the objects and their fields from the source drawing as well as all the objects and their fields from the overlay drawing.   Since none of the areas in the overlay drawing overlapped areas in the source drawing there are no intersected pieces that were created and added as rows to the table.

 

We can create a drawing that shows the s_Geom field.  The easiest way to do that is to copy and paste either the source or overlay drawing, rename the pasted copy to what we want and then in the drawing's properties point it to using the s_Geom field in the union_result table.

 

icon_message_red.pngWhen we open the drawing it opens blank and shows a message icon.   When we invoke View - Messages we are told there is no spatial index and we are invited to use a temporary index.  We agree and then we press Zoom to Fit to show the entire drawing in the window.  

 

eg_sql_topo_union01_04.png

 

The result shows us that the new drawing has filled in the missing provinces in the source drawing by filling them in with the provinces from the overlay drawing.  It is a union of the two.

 

We created the drawing by copying the source drawing, pasting it, renaming it and then pointing it at the s_Geom field in the union_result table.  By doing that we retained the original properties of the source drawing that specified the style.  Those properties used the drawing's POP1990 field to specify the fill colors of areas.  In the union_result table that field was renamed to either s_POPO1990 or to o_POPO1990, so the styling based on a nonexistent field cannot occur.   

 

We can fix that by adding a POP1990 field and then using the Transform panel to fill it with non-NULL values from the s_POP1990 and o_POP1990 fields.

 

eg_sql_topo_union01_05a.png

 

NULL values are not zero: they are indeterminate and the result of any arithmetic expression using a NULL is also NULL.   So we cannot simply add the s_POP1990 and o_POP1990 fields to each other to get a result for our POP1990 field.   Instead, we must use the Coalesce function in the Expression tab of the Transform panel:

 

Coalesce([s_POP1990], [o_POP1990])

 

This function returns the first non-NULL value found in the list of arguments.  In this case it returns the first non-NULL value found in either the s_POP1990 or o_POP1990 fields for each record.  

 

As soon as we enter the Coalesce function expression the Transform panel previews what will happen in blue preview color:

 

eg_sql_topo_union01_05.png

 

We press the Update Field button in the Transform panel to apply the previewed change to the table.  We now have a fully-populated POP1990 field in the table that we can use for thematic formatting via the Style properties of the drawing:

 

eg_sql_topo_union01_06.png

The result is a single drawing that contains all of the objects that were in the original states drawing.  That drawing was chopped up into two drawings to serve as examples in the SQL Example: GeomOverlayAdjacent Function  topic.   We have now used a topology union operation to union them back together into a single drawing.

Creating a Drawing on the Fly from a Query

So far in this example we have created a second table by using SELECT ... INTO.    Instead of creating another table we could have created a drawing dynamically from a query.

 

We right-click into the Project pane and choose New Query from the context menu and then name the new query Union Query.   We enter the following text into the new Command Window that opens up for the query:

 

eg_sql_topo_union01_25.png

 

The query text is:

 

SELECT [s_Geom], Coalesce([s_POP1990], [o_POP1990]) AS [Pop1990]

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

 

If we press the ! run button we can see the results produced by the query as shown above.    The query fetches the s_Geom field that contains the results of the union, and it dynamically constructs a POP1990 field using the Coalesce function expression we previously used in the Expression tab of the Transform panel.

 

Even though the results of this query are a virtual table when we run the query manually using the ! as above, we can nonetheless create a drawing from the query.  Whenever the drawing is refreshed it will automatically run the query in background to get the data needed for the drawing.   

 

To set that up we create a drawing called Union Query drawing.   We do that in the Project pane by copying the union_result_drawing, then pasting the drawing and then renaming the pasted drawing to use the name Union Query drawing.

 

Next, we right-click on the new Union Query drawing and from the context menu choose Properties.   We will edit the properties to point the drawing to using the Union Query instead of the table that the union_result_drawing utilized.

 

eg_sql_topo_union01_26.png

 

By copying and pasting the union_result_drawing drawing our new Union Query drawing inherited all of the original properties of the union_result_drawing drawing, including use of the s_Geom field for geometry and use of the POP1990 field for thematic formatting.  We now will double-click into the Table property to change the Table that is used from the [union_result] table to the [Union Query] query.

 

eg_sql_topo_union01_27.png

 

After making the edit we press Enter and then OK.

 

When we open the drawing it opens blank and shows a message icon.   When we invoke View - Messages we are told there is no spatial index and we are invited to use a temporary index.  We agree and then we press Zoom to Fit to show the entire drawing in the window.  

 

eg_sql_topo_union01_28.png

 

The result shows a drawing just like the one created from a newly-created table.   But this drawing is created on the fly from a query.   If the original tables/drawings used in this example changed, the drawing seen above would automatically change because it is dynamically created every time from a query.

Second Example

As useful as it may be to union together drawings that contain objects which do not overlap each other, a topology union also serves us in the more complex situation where objects in the source and overlay drawings overlap.

 

eg_sql_topo_union01_07.png

 

In this next part of our example we will use the original states drawing of Mexico that has all provinces in a single drawing as our source drawing.   For our overlay drawing we will use a drawing called overlay2 that has a single, triangular area in it, shown above in yellow in a map where the overlay2 drawing layer is shown above the states drawing layer.

 

eg_sql_topo_union01_08.png

 

Opening the overlay2 drawing's table we see it has a limited number of fields.   We have made these similar to the fields in the states table.   There is absolutely no meaning to the CODE or POP1990 figures as these have been made up out of thin air for the sake of this example.

 

eg_sql_topo_union01_09.png

 

Once again we write a query using the GeomOverlayTopologyUnion function, this time saving the results into a table called union2_result.

 

To run the query we press the ! run button.

 

eg_sql_topo_union01_10.png

 

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

 

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

 

eg_sql_topo_union01_11.png

 

There are two types of rows in the table.  One set of rows are those parts of the objects in states which are not overlapped by the triangle in overlay2.   All of those rows have NULLs in the o_ fields.  The other set of rows are those parts of the objects in states which are overlapped by the triangle, which have inherited both s_ and o_ field values.    The rows at the top of the table are those which represent the pieces where overlaps between the triangle and the provinces.

 

If the triangular area in overlay2 had extended outside of the region covered by areas in states there would have been a piece of it that was not overlapped by any area in states.  That piece would have appeared in a row in the table with all s_ field values set to NULL.  However, the triangular area in overlay2 is entirely within all of the area objects in states, so there is no part of the triangle that is chopped up and occupying a row of its own in the table with all s_ field values set to NULL.

 

We can create a drawing that shows the s_Geom objects.

 

eg_sql_topo_union01_12.png

 

As in the first part of this topic, because there is no POP1990 field the styling based on that field cannot work and all areas are shown in the style's default blue color.   With color as it is can be difficult to tell what is going on in the region of the triangle.  

 

We will fix that with a few steps.  First, we add an mfd_id field and a btree index on that field.  That makes the table editable and selectable.   We will then add a POP1990 field and fill it using the Transform panel either with values from s_POP1990 or with some fake computed values for the three rows at the top of the list, intended to cause different area fill colors for those objects so they become more visually distinct from adjacent areas.

 

Finally, we use the Transform panel to change the s_NAME values for the three areas of overlap to have their province names prefixed with Tri- to indicate these are the overlaps with the triangular area.   Doing that has nothing to do with logic or algorithms but is just a convenient way for us to keep track of what we are looking at.

 

eg_sql_topo_union01_13.png

 

We can now make selections in the table and then see those selections in the drawing so we see the relationship between the selected record and its selected object in the drawing.   We begin by selecting the first record in the table.

 

eg_sql_topo_union01_14.png

 

This shows that the first record corresponds to the small area where the triangle overlapped the province of Chihuahua.

 

eg_sql_topo_union01_15.png

 

Next, we select the second record and see which object that selects in the drawing.

 

eg_sql_topo_union01_16.png

 

This shows that the second record is for the area which is the region of overlap between the triangle and the province of Coahuila De Zaragoza.

 

eg_sql_topo_union01_17.png

 

Finally, we select the third record and see which object that selects in the drawing.

 

eg_sql_topo_union01_18.png

 

That shows the third record contains the geom for the area which is the region of overlap between the triangle and the province of Durango.

 

Keep in mind that the region of overlap seen above has been cut out of the province of Durango.   The area object originally in the states drawing was modified by the GeomOverlayTopologyUnion function when the geom for it was created in the union2_result table.   

 

eg_sql_topo_union01_19.png

We can see that by selecting the province of Durango in the drawing and then seeing which record that selects in the union2_result table.

 

eg_sql_topo_union01_20.png

 

The selected row is for the province of Durango.

 

eg_sql_topo_union01_21.png

 

We can select the other two provinces in the drawing and again see which records are selected in the table.

 

eg_sql_topo_union01_22.png

 

This shows that the entries left for the provinces that had overlaps with the triangle are for the provinces with the regions of overlap cut out of them.

 

We can see the relationship between the pieces cut out of provinces as a result of overlaps and the remainder of the province areas by creating a map with the drawing and labels for the areas, and then zooming into region where the triangular area overlapped provinces.

 

eg_sql_topo_union01_23.png

 

We can see above the names of the cut out pieces.   To make the display clearer, we can use Style to apply a palette where each area object is a different color.

 

eg_sql_topo_union01_24.png

 

There, that's better!  What once were three areas representing the three provinces of Chihuahua, Coahuila De Zaragoza and Durango are now the six areas that represent portions overlapped by the triangle in the overlay drawing as well as the three areas representing the remainder of the provinces with the overlapped portions cut out.

 

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: GeomOverlayAdjacent Function - Using the GeomOverlayAdjacent function, a companion example that shows how this function and similar functions such as GeomOverlayContained, GeomOverlayContaining, GeomOverlayIntersecting and GeomOverlayTouching 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: Clip Areas with a Transform Expression - Use the Expression tab of the Transform panel to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Smooth Lines with a Transform Expression - Use the Expression tab of the Transform panel to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.

 

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.