How to Replace ST_DWithin Radius Queries in YugabyteDB Without PostGIS

Many geospatial applications built on PostgreSQL use PostGIS queries that combine:

● ST_DWithin() to filter objects within a radius
● bounding-box checks to eliminate obvious misses
● ORDER BY ST_Distance() to return the nearest objects first

Because PostGIS is not currently available in YugabyteDB, applications often need an alternative approach for implementing common radius and nearest-neighbor queries.

A typical pattern looks like this:

				
					WHERE ST_DWithin(
        g.geom::geography,
        ST_SetSRID(ST_GeomFromText($1), 4326)::geography,
        1000,
        true
      )
AND g.geom::box2d <-> ST_MakeEnvelope($1,$2,$3,$4)::box2d = 0
ORDER BY ST_Distance(
           g.geom::geography,
           ST_SetSRID(ST_MakePoint($1,$2),4326)::geography,
           true
         );

YugabyteDB does not currently support PostGIS, but many real-world application queries using these functions can still be implemented efficiently.

The key is recognizing that the PostGIS query above is really a multi-stage filtering pipeline.

Distributed Spatial Query Pipeline

🌐

Step 1
Cell Filter
H3 / Geohash lookup

→

📦

Step 2
Bounding Box
min/max lat/lon pruning

→

📏

Step 3
Distance Math
great-circle calculation

→

🏁

Step 4
Nearest Results
ORDER BY distance

🧭 Key Insight

ST_DWithin() is not a single operation. It is a pipeline:

1 generate candidate rows
2 prune aggressively
3 compute exact distance

YugabyteDB executes this pattern efficiently when the stages are made explicit.

Mapping PostGIS Functions to YugabyteDB

The following table shows how common PostGIS constructs translate to YugabyteDB patterns.

PostGIS Construct	YugabyteDB Approach
ST_SetSRID(point,4326)	Standardize on WGS84 latitude/longitude coordinates
ST_MakeEnvelope()	Explicit bounding box columns (min_lat, min_lon, max_lat, max_lon)
geom::box2d	Replace with stored bounding box metadata
ST_Distance()	Great-circle distance helper function in SQL
ST_DWithin()	Cell filtering + bounding box pruning + distance filter

This tip demonstrates how to implement that pattern.

🧭 Conceptual Mapping

This approach reproduces the query behavior used by many radius and nearest-neighbor searches, but it does not attempt to fully reimplement PostGIS geometry semantics.

Instead, it focuses on the practical execution strategy behind these queries: generate candidates, prune aggressively, then compute exact distance on a reduced set.

In practice, many PostGIS queries already follow this same strategy internally. YugabyteDB simply makes the stages explicit so they can scale efficiently across distributed tablets.

Step 1 – Store geometry metadata explicitly

Instead of storing geometry objects, store simple numeric metadata.

				
					CREATE TABLE geo_objects (
    object_id       BIGINT PRIMARY KEY,
    label           TEXT,

    center_lat      DOUBLE PRECISION NOT NULL,
    center_lon      DOUBLE PRECISION NOT NULL,

    min_lat         DOUBLE PRECISION NOT NULL,
    min_lon         DOUBLE PRECISION NOT NULL,
    max_lat         DOUBLE PRECISION NOT NULL,
    max_lon         DOUBLE PRECISION NOT NULL
);

These columns allow fast numeric filtering without requiring geometry types.

Insert some sample data.

				
					INSERT INTO geo_objects (
    object_id,
    label,
    center_lat,
    center_lon,
    min_lat,
    min_lon,
    max_lat,
    max_lon
)
SELECT
    gs AS object_id,
    'Object ' || gs AS label,

    lat,
    lon,

    lat,
    lon,
    lat,
    lon

FROM (
    SELECT
        gs,

        -- random offset in degrees (~5km)
        40.4406 + (random() - 0.5) * 0.09 AS lat,
        -79.9959 + (random() - 0.5) * 0.09 AS lon

    FROM generate_series(1,1000) gs
) s;

The points are randomly distributed within roughly ±0.045 degrees (~5km) of the center.

Bounding boxes equal the point location for simplicity.

Step 2 – Store spatial coverage cells

To emulate spatial index behavior, store H3 or geohash coverage cells.

				
					CREATE TABLE geo_object_cells (
    object_id BIGINT NOT NULL,
    cell_id   TEXT NOT NULL,
    PRIMARY KEY (object_id, cell_id)
);

Index optimized for lookups:

				
					CREATE INDEX geo_object_cells_cell_idx
ON geo_object_cells(cell_id, object_id);

If you want the cell filtering portion of the query to actually work, you can generate H3 cells from SQL inputs or use placeholders.

For the simplest demo approach:

				
					INSERT INTO geo_object_cells
SELECT
    object_id,
    '882a100d6bfffff'
FROM geo_objects;

🧪 Demo Dataset

For demonstration purposes, all rows are assigned the same placeholder H3 cell. This keeps the example simple and runnable in ysqlsh.

In a real system, each object would be associated with the H3 cells covering its location or geometry, which is what makes the first filtering stage selective.

Step 3 – Generate H3 search cells in the application

The application typically generates:

● query point
● radius
● search bounding box
● H3 cells covering the search area

Example Python code:

				
					import math
import h3

def bounding_box(lat, lon, radius_m):
    earth_radius = 6371000

    dlat = (radius_m / earth_radius) * (180 / math.pi)
    dlon = dlat / math.cos(math.radians(lat))

    return (
        lat - dlat,
        lon - dlon,
        lat + dlat,
        lon + dlon
    )

def h3_cells_for_radius(lat, lon, radius_m, resolution=8):

    bbox = bounding_box(lat, lon, radius_m)

    cells = list(
        h3.grid_disk(
            h3.latlng_to_cell(lat, lon, resolution),
            3
        )
    )

    return {
        "query_lat": lat,
        "query_lon": lon,
        "radius_m": radius_m,
        "cells": cells,
        "bbox": bbox
    }

search = h3_cells_for_radius(40.44, -79.99, 1000)

print(search)

The values produced by the application… the query point, bounding box, search radius, and H3 cell list… become the inputs for the YugabyteDB query shown in the next section.

⚠️ Demo Simplification

The grid_disk(..., 3) call in this example is a simplified way to generate nearby H3 cells for demonstration purposes.

In production systems, the number of rings should typically be derived from the requested search radius and the chosen H3 resolution. The goal is to cover the entire search area while keeping the candidate cell list reasonably small.

Step 4 – Helper SQL functions

These helper functions simulate common PostGIS operations.

Distance calculation

				
					CREATE OR REPLACE FUNCTION yb_geo_distance_meters(
    lat1 DOUBLE PRECISION,
    lon1 DOUBLE PRECISION,
    lat2 DOUBLE PRECISION,
    lon2 DOUBLE PRECISION
)
RETURNS DOUBLE PRECISION
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT 6371000 * acos(
    greatest(-1.0, least(1.0,
        cos(radians(lat1))
      * cos(radians(lat2))
      * cos(radians(lon2) - radians(lon1))
      + sin(radians(lat1))
      * sin(radians(lat2))
    ))
);
$$;

Radius filter

				
					CREATE OR REPLACE FUNCTION yb_point_within_radius(
    lat DOUBLE PRECISION,
    lon DOUBLE PRECISION,
    q_lat DOUBLE PRECISION,
    q_lon DOUBLE PRECISION,
    radius DOUBLE PRECISION
)
RETURNS BOOLEAN
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT yb_geo_distance_meters(lat, lon, q_lat, q_lon) <= radius;
$$;

Bounding box overlap

				
					CREATE OR REPLACE FUNCTION yb_bbox_overlaps(
    min_lat1 DOUBLE PRECISION,
    min_lon1 DOUBLE PRECISION,
    max_lat1 DOUBLE PRECISION,
    max_lon1 DOUBLE PRECISION,
    min_lat2 DOUBLE PRECISION,
    min_lon2 DOUBLE PRECISION,
    max_lat2 DOUBLE PRECISION,
    max_lon2 DOUBLE PRECISION
)
RETURNS BOOLEAN
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT max_lat1 >= min_lat2
AND min_lat1 <= max_lat2
AND max_lon1 >= min_lon2
AND min_lon1 <= max_lon2;
$$;

Step 5 – YugabyteDB query using `CROSS JOIN LATERAL`

The YugabyteDB query uses four inputs produced by the earlier steps:

● candidate cells
● a bounding box
● a query point
● a radius in meters

The final query follows the same pipeline as the original PostGIS pattern:

1. find candidate rows from the cell table
2. prune by bounding box
3. compute exact distance once per surviving row
4. filter by radius
5. return the nearest matches first

Using CROSS JOIN LATERAL keeps the distance calculation in one place, so the query does not have to repeat the same expression in both the SELECT list and the WHERE clause.

Application-style parameterized version

In an application, these values would typically be passed in as parameters:

● :cells
● :min_lat, :min_lon, :max_lat, :max_lon
● :query_lat, :query_lon
● :radius_meters

💡 Parameterized Query

The following version illustrates how an application might parameterize the query. The :parameters shown here represent application bind variables and are not directly executable in ysqlsh.

				
					WITH candidates AS (
  SELECT DISTINCT
      o.object_id,
      o.label,
      o.center_lat,
      o.center_lon,
      o.min_lat,
      o.min_lon,
      o.max_lat,
      o.max_lon
  FROM geo_objects o
  JOIN geo_object_cells c
    ON c.object_id = o.object_id
  WHERE c.cell_id = ANY(:cells)
    AND yb_bbox_overlaps(
          o.min_lat, o.min_lon,
          o.max_lat, o.max_lon,
          :min_lat, :min_lon,
          :max_lat, :max_lon
        )
)
SELECT
    c.object_id,
    c.label,
    c.center_lat,
    c.center_lon,
    d.distance_meters
FROM candidates c
CROSS JOIN LATERAL (
    SELECT yb_geo_distance_meters(
             c.center_lat,
             c.center_lon,
             :query_lat,
             :query_lon
           ) AS distance_meters
) d
WHERE d.distance_meters <= :radius_meters
ORDER BY d.distance_meters
LIMIT 50;

ysqlsh does not understand application-style named bind parameters unless you define client-side variables first. For a blog post, it is useful to also show a version with literal values that readers can run immediately.

The example below assumes:

● the search point is near 40.4406, -79.9959
● the search radius is 1000 meters
● the bounding box is already computed
● the candidate cell list contains one example H3 cell

				
					WITH candidates AS (
  SELECT DISTINCT
      o.object_id,
      o.label,
      o.center_lat,
      o.center_lon,
      o.min_lat,
      o.min_lon,
      o.max_lat,
      o.max_lon
  FROM geo_objects o
  JOIN geo_object_cells c
    ON c.object_id = o.object_id
  WHERE c.cell_id = ANY(ARRAY['882a100d6bfffff'])
    AND yb_bbox_overlaps(
          o.min_lat, o.min_lon,
          o.max_lat, o.max_lon,
          40.4316, -80.0049,
          40.4496, -79.9869
        )
)
SELECT
    c.object_id,
    c.label,
    c.center_lat,
    c.center_lon,
    d.distance_meters
FROM candidates c
CROSS JOIN LATERAL (
    SELECT yb_geo_distance_meters(
             c.center_lat,
             c.center_lon,
             40.4406,
             -79.9959
           ) AS distance_meters
) d
WHERE d.distance_meters <= 1000
ORDER BY d.distance_meters
LIMIT 50;

Why use CROSS JOIN LATERAL here?

The query computes distance_meters with a CROSS JOIN LATERAL so the distance expression is written only once.

That makes the flow easier to read:

● first reduce the candidate set
● then compute distance for each remaining row
● then filter and sort using that derived value

It is a small refinement, but it makes the “compute late” part of the spatial pipeline visible directly in the SQL.

Why This Pattern Scales in YugabyteDB

⚡ Performance Insight

The key to scalable spatial queries is: prune early, compute late.

1. H3 cells reduce the search space first

2. Bounding boxes eliminate obvious misses

3. Distance math runs only on a small candidate set

Execution flow becomes:

				
					. cell lookup
       ↓
  bbox pruning
       ↓
  distance math
       ↓
ORDER BY distance

This keeps the expensive work late in the pipeline and produces predictable performance as the dataset grows.

Precision Considerations

For many workloads, centroid distance is sufficient.

Examples:

● store locator queries
● nearby assets
● service region lookup

For exact polygon-distance calculations, the final candidates can be refined in the application layer.

Related YugabyteDB Tips

Conclusion

Queries using:

				
					ST_DWithin(...)
ORDER BY ST_Distance(...)

are really executing a multi-stage filtering pipeline.

By expressing those stages explicitly using:

● H3 or geohash coverage cells
● bounding box filtering
● great-circle distance math

you can implement efficient radius and nearest-neighbor queries in YugabyteDB today, even without PostGIS.

For distributed SQL workloads, this explicit pipeline often produces more predictable and scalable performance than relying on monolithic spatial operators.

Have Fun!