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.  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, using Latitude / Longitude projection, and then we cut five of the provinces out of the drawing to create a second, overlay drawing, shown below using orange colors.   The drawings created from the original Mexico drawing inherited use of Latitude / Longitude projection from the originating Mexico drawing.

 

 

    

 

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.

 

  

 

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.

 

 

Our source drawing began as a drawing of provinces in Mexico, from which five provinces were cut.  The display above uses a map window in Pseudo-Mercator projection.  The source drawing is in Latitude / Longitude, so the map window is projecting it on the fly into Pseudo-Mercator.

 

 

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.

 

 

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.

 

 

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.

 

 

To simplify our presentation, in the illustration above we have already added a mfd_id field and a btree index on that field, using the one-click procedure given in the Add an Index to a Table topic.  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.    To do that, in the Project pane we right-click on the union_result table and choose Create - New Drawing

 

 

We use the default suggested name, the default suggested table, the default suggested Geometry field, and the default choice to create a spatial index.  However, the original Coord system shown in red of Pseudo-Mercator (not illustrated above) must be changed to Latitude / Longitude projection.

 

We press the coordinate picker button and choose Latitude / Longitude and then we press Create Drawing.

 

 

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 have used Style to color the above drawing in blue, not using the default gray color.  Displayed in its own window, the drawing is seen in Latitude / Longitude projection.

 

The original source drawing was styled in a thematic format using 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 if we want to use the same thematic format by copying it from the properties for the source drawing, we must add a POP1990 field.

 

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

 

 

Press the Add command button and then choose Field from the drop down menu.

 

 

In the Field dialog, enter the name POP1990 for the new field, leave the default type of int32 in place, and press OK.   

 

 

In the Schema dialog press Save Changes to add the new field to the table.

 

 

That adds a new POP1990 field to the table, with all values initially NULLs.   In the illustration above, we have used the Layers pane to move the new POP1990 field to the leftmost column position.  We will now populate that field with non-NULL values from the s_POP1990 and o_POP1990 fields.   

 

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 use the Coalesce function within a Transform expression to get the POP1990 values we want.

 

With the focus on the open union_result table window, in the Transform pane choose the POP1990 field.    Double-click the Expression template to launch it.

 

 

In the Expression template, press the Edit Expression button to launch the expression builder.

 

 

In the Expression dialog we enter the following expression using the Coalesce function:

 

Coalesce([s_POP1990], [o_POP1990])

 

Press OK to accept the expression and to return to the Transform pane.  

 

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

 

 

Press the Preview button to see what will happen.

 

 

A new preview column appears in blue preview color, with the name of the template as the caption for that column.   Preview columns are virtual columns that appear above all other columns in the table.  We can drag the preview column left or right and it will snap into place next to adjacent columns.  In the illustration above, we have dragged it into place just to the right of the s_POP1990 column, to make comparisons easy between that column and the preview column and also the o_POP1990 column nearby.

 

We can see how the result of the expression is a combination of non-NULL values from the s_POP1990 column and the o_POP1990 column.   The dot in the column head for the POP1990 field indicates that the previewed result will be placed into that column.

 

We like what we see, so we press the Transform button 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, using the same style as used by the source drawing:

 

 

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.

Create 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:

 

 

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 template in the Transform pane.

 

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 could create that drawing by right-clicking on the Union Query query and choosing Create - New Drawing.  However, we would like the new drawing to inherit all of the Style formatting from the union_result Drawing so we will create the new drawing using a different method.

 

In the Project pane we copy the union_result_drawing, and then paste to create a copy, and then we rename the pasted drawing by slow double clicking it 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.

 

 

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.

 

 

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.  

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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.

 

 

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, using the one-click procedure given in the Add an Index to a Table topic.   That makes the table editable and selectable.   

 

We will then add a POP1990 field and fill it using the Transform pane 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.   We style the drawing using the new POP1990 field.

 

Finally, we use the Transform pane 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.

 

After doing all the above, we can work with the table, which we have called union2_result, and a drawing created from the table, which we will call union2_result_drawing, to see what effect selections have.

 

 

We can 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.

 

 

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

 

 

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

 

 

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.

 

 

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

 

 

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.   

 

 

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.

 

 

The selected row is for the province of Durango.

 

 

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

 

 

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.

 

 

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.

 

 

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

 

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: 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 : 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 template in 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: Clip Areas with a Transform Expression - Use the Expression template in the Transform pane 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: Simplify Lines with a Transform Expression - Use the Expression template in the Transform pane 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: 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.