liveBook · Manning

Bounding box operator

As explained in Just Use Postgres!

A bounding box operator tests spatial relationships between the minimum bounding rectangles of geometries.

A bounding box operator tests whether the minimum bounding rectangles of geometries intersect or overlap. In PostgreSQL spatial queries, && provides a fast preliminary filter for GiST-indexed geometry data. section 10.6.2

For a 500-meter search, ST_Expand creates a station-centered bounding box and && quickly selects candidates. ST_DWithin then checks actual distance, removing candidates that only overlap the box. section 10.6.2

Finding nearby shops

A query around Brickell City Centre uses ST_DWithin(..., 500) to find nearby shops. The execution plan shows a Bitmap Index Scan using the GiST index; replacing it with ST_Distance(...) <= 500 removes 18,640 rows through a slower join filter. section 10.6.2

Listing 10.23 Finding the 10 closest shops near Brickell City Centre
WITH brickell AS (
  SELECT way FROM florida.planet_osm_point
  WHERE name = 'Brickell City Centre' and railway = 'station'
)
SELECT p.name, p.shop,
  round(ST_Distance(b.way,p.way)) as distance_meters,
  round(ST_Distance(b.way,p.way) * 3.28) as distance_feet
FROM brickell as b
JOIN florida.planet_osm_point AS p
  ON ST_DWithin(b.way, p.way, 500)
WHERE p.shop IS NOT NULL
ORDER BY distance_meters LIMIT 10;

Run the nearby-shops query to inspect how a spatial distance condition benefits from bounding-box filtering. section 10.6.2

How Just Use Postgres! covers it

How deep it goes
Explains bounding-box filtering, execution-plan behavior, and its approximation relative to exact distance checks.
Examples
Demonstrates a 500-meter Brickell City Centre shop search and compares ST_DWithin with ST_Distance execution plans.
What it assumes
Assumes familiarity with geometries, spatial predicates, SQL joins, and PostgreSQL indexes.
What you can do after
Apply it via PostgreSQL GiST-backed spatial queries and interpret the resulting index scans and exact filters.
How it connects
Connects GiST indexes with ST_Expand, ST_DWithin, ST_Contains, ST_Intersects, and ST_Overlaps.
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.