Introduction
ST_Equals
Relationships Between Geometries
6. Function - ST_Overlaps
ST_Distance and ST_Dwithin
Spatial joins practice
Summary

Instruction

Perfect! We'll now get to know a few related functions that all check whether two geometries have some space in common. These functions will make most sense for linestrings and polygons. For points, you can simply use ST_Equals, which we covered earlier.

The first function is ST_Overlaps. It checks if two geometries have some (but not all) space in common:

ST_Overlaps

Note that if one of the geometries is entirely inside the other, this function will return FALSE! Another very important thing to note is that the two geometries must be of the same subtype (e.g., two polygons or two linestrings). Otherwise, the function won't work properly. Now, let's take a look at an example query:

SELECT
  *
FROM sf_selected_railways s1
JOIN sf_selected_railways s2
ON ST_Overlaps(s1.course, s2.course)

This query returns all data for railways that share at least one point in common. You may have noticed that we joined the table with itself and used the ST_Overlaps function as the joining condition. What we did here roughly translates to the following: "PostGIS, show me the rows where at least one point of a given linestring overlaps with at least one point of another linestring that is also in the same table". As simple as that.

Exercise

Mark is quite passionate about cycling now that he's been at it for so long! He is in excellent physical condition and plans on taking longer trips. Help Mark by showing all bicycle routes that overlap with the bicycle route with id = 334. This route is closest to Mark's favourite restaurant.

Select all information about these bicycle routes. Check the result on the MAP tab.

Stuck? Here's a hint!

Use ST_Overlaps in the ON clause.

Console

Code editor

Result

TableConsoleMap