Introduction
Functions returning geometries
7. ST_Intersection
Collections
Summary

Instruction

Perfect! Let's move on to ST_Intersection. This function returns the shared part of two geometries. Take a look:

ST_Intersection

Note that for two geometries without any common area, the function will naturally return an empty geometry.

Now, take a look at the following example:

SELECT 
  sfsr.id, 
  sfpd.id, 
  ST_Intersection(sfpd.boundaries, sfsr.course)
FROM sf_selected_railways sfsr 
JOIN sf_planning_districts sfpd 
ON ST_Intersects(sfpd.boundaries, sfsr.course);

The query above will find all railways that intersect with specific districts and will return the shared part of the given districts and railways. In other words, if a given railway crosses multiple districts, this query will "cut" the railway where it leaves one district and enters another one. It will then show all such segments individually. Note that we used ST_Intersection together with ST_Intersects in the spatial join—this usage is very common.

Exercise

Let's show all bicycle routes (or their parts) within the borders of the Downtown district. For each row, show the name of the district (Downtown), its boundaries, and the intersection of each bicycle route with the district polygon. Show such rows for all bicycle routes that intersect with the Downtown.

Stuck? Here's a hint!

You will have to use a spatial join with ST_Intersects. In the SELECT clause, use ST_Intersection.

Console

Code editor

Result

TableConsoleMap