How to perform a space join in PostGIS |Cartogeek

Learn how to perform a space join in PostGIS with our step-by-step guide. Joining spatial data is made easy with this tutorial. Start optimizing your spatial analysis with PostGIS today

Admin

0 29 8 minutes read

Contents

0.1 How to perform a space join in PostGIS

1 What is a space join?
2 How to perform a space join in PostGIS?

How to perform a space join in PostGIS

This article cartrogeek.com aims to show how a space join at PostGIS, one of the most common operations and that any GIS professional must know and master.

Using this technique is tremendously useful when working on PostGIS or in any desktop GIS software. It is a process used on a regular basis since it covers a basic need, which is repeatedly required in many projects and tasks related to the analysis of spatial data.

As we will see, there are different ways to carry it out, both by the way of constructing the SQL query and by the spatial function to be used.

What is a space join?

A space join or spatial union ( in English spatial join ) is an operation that is based on the combination of two or more spatial data sets through their spatial relationship.

Unlike a traditional or alphanumeric join that uses common keys or identifiers between tables to relate them, a space join uses one or more spatial functions to determine the spatial relationship among his objects. These spatial functions include operations such as intersection, containment, proximity, crossing, contact, among others.

The spatial join allows to recover alphanumeric information ( attributes ) and graph ( geometry ) of the objects of one or more tables that maintain a certain condition or spatial relationship with respect to the objects of another.

For example, we can relate the data from a polygonal layer representing the natural spaces of a country with the data from a layer of fire conate points that occurred in the last decade. With the space join, we can obtain a list of all the fire attempts that occurred within a natural park. We could even add them for each of the different parks and establish occurrence statistics for the entire period, for a specific year of the series, etc.

Let’s see what options exist to perform a space join and what we can get with them.

How to perform a space join in PostGIS?

The first thing is to make sure that both tables have their respective geometries duly geo-referenced to the same coordinate system. Obviously, both tables must have a column with geometry.

For this, we can use the following expression on both tables.

First, on the first geometric table:

1	`select` `st_srid(geom)` `from` `tabla_1`

And then on the second geometric table:

1	`select` `st_srid(geom)` `from` `tabla_2`

Without a correct georeferencing of graphic objects to the same reference system, we will not be able to establish the relationship in space and therefore it will not be possible to perform the space join operation satisfactorily.

It is also interesting to make sure that both tables have spatial indexes created. Otherwise, especially if they are tables with many records, space operations can be slow.

Once these points are secured, we can carry out the space join operation.

Space Join in PostGIS with the JOIN clause

The usual way of carrying out an operation of space join at PostGIS is through a JOIN clause from one table to another.

An example of an SQL query for this operation would be the following:

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Intersects(tabla_1.geom, tabla_2.geom);

In this example we would be performing a spatial join of the attributes of table_2 on table_1, as long as the objects in table_2 meet the intersection condition with the objects in table_1.

As a result, we would obtain a table with the attributes of the objects in table_1 followed by the attributes of the objects in table_2 with which it has intersected.

PostGIS’s ST_Intersects ( ) function returns true if two geometries intersect, that is, if they share any common point or line.

Using this type of operation we can obtain repeated records of both tables, since the objects of the first can intersect with one or more objects of the second, and vice versa.

Space functions available in PostGIS

In addition to the ST_Intersects ( ) function, there are also other variations with PostGIS space operations or functions such as the following.

The ST_Contains ( ) function returns true if one geometry fully contains another:

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Contains(tabla_1.geom, tabla_2.geom);

On the other hand, the ST_Touches ( ) function returns true if two geometries touch at some point:

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Touches(tabla_1.geom, tabla_2.geom);

Also exist the ST_Overlaps ( ) function that returns true if two geometries partially overlap.

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Overlaps(tabla_1.geom, tabla_2.geom);

We also found ST_Crosses ( ), a function that returns true if two geometries intersect, that is, if they have any common point but do not share any segment or line entirely.

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Crosses(tabla_1.geom, tabla_2.geom);

On the other hand, the ST_Within ( ) function returns true if one geometry is completely within another geometry

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_Within(tabla_1.geom, tabla_2.geom);

Finally, the ST_DWithin ( ) function returns true if two geometries are within a certain distance:

SELECT *
FROM tabla_1
JOIN tabla_2
ON ST_DWithin(tabla_1.geom, tabla_2.geom, 50);

In this case, the function returns true if the distance between the geometries is less than 50 space units.

Different types of JOIN to use

It is important to take into account the different types of JOIN that exist in SQL language and what results each one offers when applying them to a spatial operation of these characteristics.

The following image shows a very clear and intuitive summary table about which join SQL to use for each case, according to the records that you want to obtain with the operation:

JOIN types in SQL — Visual scheme of the different types of join that exist. Source: https://stevenjhu.com/

The different types of JOIN existing in SQL are:

Inner Join: returns only the rows that have matching values in both tables that are joining.
Left Join ( also known as Left Outer Join ): returns all the rows in the left table and the corresponding rows in the right table. If there is no correspondence in the right table, it returns null values.
Right Join ( also known as Right Outer Join ): returns all the rows in the right table and the corresponding rows in the left table. If there is no correspondence in the left table, it returns null values.
Full Join ( also known as Full Outer Join ): returns all the rows of both tables. If there is no correspondence in a table, it returns null values.
Cross Join ( also known as Cartesian Product Join ): returns all possible combinations between the rows of both tables.

In fact, the most common thing in a space join-type operation is to use the LEFT JOIN preferably. If we use a simple JOIN or INNER JOIN we would be returning only the cases in which said spatial condition is effectively fulfilled. However, taking as an example the case of the layer of fire points and polygons with natural parks, we could consider the need to return all fires regardless of their spatial relationship with the park layer and, in case of intersecting with a natural park, obtain the attributes of the park.

In this case we would use a LEFT JOIN from the park table on the fire table:

SELECT *
FROM fires
LEFT JOIN parks
ON ST_Intersects(indencios.geom, parques.geom);

In this way we will obtain all the records of the first table ( fires ) and the attributes of its corresponding park in case it effectively meets the spatial condition of intersection indicated in the space join.

So, with the use of the LEFT JOIN we will obtain two types of records in the result:

Fires that do intersect with a park will contain the attributes of the fire followed by the attributes with values of the park with which they have intersected.
Fires that do not intersect with a park will contain the attributes of the fire followed by the empty attributes ( NULL ) since it will not have intersected with any park.

Denial of a space JOIN

By denial we mean to get the result of an operation that returns false rather than true Boolean value. That is, in this case, we consider how we can obtain the objects in a table that do not meet the indicated spatial condition.

Following the example of fires and natural parks, we ask ourselves: How do we obtain all those fires that did not originate within a natural park?

In this case, it would only be necessary to add a WHERE filter on the previous LEFT JOIN operation. This filter should indicate that we only want to obtain the results that do not recover park values ( since the fires are not located inside ), that is, they have zero value.

SELECT *
FROM fires
LEFT JOIN parks
ON ST_Intersects(indencios.geom, parques.geom)
WHERE parques.id IS NULL;

Thus, we will only be obtaining all the records in the first table ( fires ) that do not intersect with the second ( parks ).

Another option to get all the records in a table that do not have any kind of spatial relationship with the objects in another table is through the ST_Disjoint ( ) operation.

SELECT *
FROM fires
JOIN parques
ON ST_Disjoint(indencios.geom, parques.geom);

Space Join with the WHERE clause

It is also possible to perform a spatial join with a properly used WHERE clause, even though a filter with a spatial condition is actually being used as an analysis operation.

SELECT *
FROM tabla_1, tabla_2
WHERE ST_Intersects(tabla_1.geom, tabla_2.geom);

In this query, the tables table_1 and table_2 are crossed in the FROM clause, and the ST_Intersects ( ) function is used in the WHERE clause to determine which records coincide spatially between both tables.

With this SQL query we would be obtaining the attributes of the objects of both tables that meet the spatial condition of intersection.

This method is much more restrictive, since by using a WHERE filter we would only be retrieving the information from those cases in which the condition is met, not being able to enter the « game » that the different types of JOIN offer.

In fact, the WHERE condition used in this example could be assimilated into the operation by a simple JOIN or space INNER JOIN:

SELECT *
FROM tabla_1
INNER JOIN tabla_2
ON ST_Intersects(tabla_1.geom, tabla_2.geom);

Although this option is lexically simpler, being purists the approximation is more correct by using the JOIN clause ( of the type ) with the desired spatial operation.

Practicing with the different types of existing space JOIN is the best way to understand what results they offer, helping to know what type of operation and join use in each case.

We hope the article has been useful and practical, and help you understand the various possible options that can be used when carrying out such recurring operations in PostGIS spatial analysis.