Spatial Joins

You can perform spatial joins using standard SQL INNER JOIN syntax. The join condition is defined in the ON clause using a spatial function that specifies the relationship between the geometries of the two tables.

General Spatial Join

Use functions like ST_Contains, ST_Intersects, or ST_Within to join tables based on their spatial relationship.

Example

Assign a country to each city by checking which country polygon contains each city point.

SELECT
    cities.name as city,
    countries.name as country
FROM cities
INNER JOIN countries
ON ST_Contains(countries.geometry, cities.geometry)

K-Nearest Neighbor (KNN) Join

Use the specialized ST_KNN function to find the k nearest neighbors from one table for each geometry in another. This is useful for proximity analysis.

Example For each city, find the 5 other closest cities.

SELECT
    cities_l.name AS city,
    cities_r.name AS nearest_neighbor
FROM cities AS cities_l
INNER JOIN cities AS cities_r
ON ST_KNN(cities_l.geometry, cities_r.geometry, 5, false)

Optimization Barrier

Use the barrier function to prevent filter pushdown and control predicate evaluation order in complex spatial joins. This function creates an optimization barrier by evaluating boolean expressions at runtime.

The barrier function takes a boolean expression as a string, followed by pairs of variable names and their values that will be substituted into the expression:

barrier(expression, var_name1, var_value1, var_name2, var_value2, ...)

The placement of filters relative to KNN joins changes the semantic meaning of the query:

• Filter before KNN: First filters the data, then finds K nearest neighbors from the filtered subset. This answers "What are the K nearest high-rated restaurants?"
• Filter after KNN: First finds K nearest neighbors from all data, then filters those results. This answers "Of the K nearest restaurants, which ones are high-rated?"

Example

Find the 3 nearest high-rated restaurants to luxury hotels, ensuring the KNN join completes before filtering.

SELECT
    h.name AS hotel,
    r.name AS restaurant,
    r.rating
FROM hotels AS h
INNER JOIN restaurants AS r
ON ST_KNN(h.geometry, r.geometry, 3, false)
WHERE barrier('rating > 4.0 AND stars >= 4',
              'rating', r.rating,
              'stars', h.stars)

With the barrier function, this query first finds the 3 nearest restaurants to each hotel (regardless of rating), then filters to keep only those pairs where the restaurant has rating > 4.0 and the hotel has stars >= 4. Without the barrier, an optimizer might push the filters down, changing the query to first filter for high-rated restaurants and luxury hotels, then find the 3 nearest among those filtered sets.