liveBook · Manning

St_equals

As explained in Just Use Postgres!

ST_Equals is a PostGIS spatial predicate that tests whether two geometries have equal dimensions and occupy the same point set.

ST_Equals tests whether two geometries are topologically equal: they share the same dimension and occupy the same point set. It is useful for excluding a containing boundary when finding distinct geometries inside it. section 10.5.2

Exclude the containing theme park boundary

Use ST_Within to find theme parks inside Walt Disney World, then remove the boundary itself with ST_Equals: AND NOT ST_Equals(p.way, d.boundaries) The query returns six distinct theme parks rather than the Walt Disney World polygon. section 10.5.2

Listing 10.18 Finding distinct parks within Walt Disney World
WITH disney_world AS (
  SELECT way AS boundaries
  FROM florida.planet_osm_polygon
  WHERE name = 'Walt Disney World'
)
SELECT p.name, p.tourism
FROM florida.planet_osm_polygon AS p
JOIN disney_world AS d ON ST_Within(p.way, d.boundaries)
WHERE p.name IS NOT NULL 
  AND p.tourism = 'theme_park'
  AND NOT ST_Equals(p.way, d.boundaries)
ORDER BY p.name;

Browse the complete SQL query that combines ST_Within with ST_Equals to identify distinct theme parks within a larger polygon. section 10.5.2

How Just Use Postgres! covers it

How deep it goes
Defines topological equality and demonstrates it in a nested-polygon SQL filter, without exploring implementation internals.
Examples
Demonstrates excluding the Walt Disney World boundary while listing six theme parks contained within it.
What you can do after
Apply it via PostGIS to distinguish an enclosing geometry from geometries selected within it.
How it connects
Connects ST_Equals with ST_Within, geometry, polygons, and spatial filtering.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.