Geospatial

ScramDB has built-in functions for working with locations: measuring the real distance between two points on Earth, and grouping points into map cells. Both are plain SQL functions, so you can find nearby records and combine location filters with the rest of your query on the same live data.

Note: The geo_* functions come from ScramDB's built-in packages. Install and register them once first, see Install and use a package.

We will use a table of store locations:

CREATE TABLE stores (
    id   BIGINT PRIMARY KEY,
    name VARCHAR(120),
    lat  DOUBLE PRECISION,
    lon  DOUBLE PRECISION
);

INSERT INTO stores (id, name, lat, lon) VALUES
    (1, 'Market Street',  37.7912, -122.3970),
    (2, 'Mission',        37.7599, -122.4148),
    (3, 'Oakland',        37.8044, -122.2712),
    (4, 'San Jose',       37.3382, -121.8863);

Distance between two points

geo_haversine_distance(lat1, lon1, lat2, lon2) returns the great-circle distance in meters between two points. Give latitude first, then longitude for each point:

-- San Francisco to Los Angeles
SELECT geo_haversine_distance(37.7749, -122.4194, 34.0522, -118.2437);
-- about 559000 meters

The distance between a point and itself is exactly 0.

Find points within a radius

To find every store within a given distance of a location, compare the haversine distance against a threshold in meters. Here, stores within 5 km of a point downtown:

SELECT
    id,
    name,
    geo_haversine_distance(lat, lon, 37.7749, -122.4194) AS meters
FROM stores
WHERE geo_haversine_distance(lat, lon, 37.7749, -122.4194) <= 5000
ORDER BY meters;

Because it is ordinary SQL, you can add any other condition to the same query, for example only open stores or a single region.

Find the nearest points

Drop the distance filter and order by distance to get the closest records. This returns the five nearest stores to a point:

SELECT
    id,
    name,
    geo_haversine_distance(lat, lon, 37.7749, -122.4194) AS meters
FROM stores
ORDER BY meters
LIMIT 5;

Group points into map cells with geohash

A geohash turns a latitude and longitude into a short text code. Nearby points share a prefix, which makes geohashes a handy way to bucket locations for heatmaps, clustering, or counts per area.

geo_geohash_encode(lat, lon, precision) returns the geohash as text. The precision is how many characters you want: more characters means a smaller, more precise cell.

SELECT geo_geohash_encode(57.64911, 10.40744, 11);
-- u4pruydqqvj

As a rough guide, each extra character shrinks the cell by about a factor of ten in area: precision 5 is roughly neighborhood scale, precision 7 is roughly street scale, and precision 9 is roughly building scale.

Count points per cell

Group your rows by their geohash to see how many fall in each area. This is a one-query heatmap:

SELECT
    geo_geohash_encode(lat, lon, 6) AS cell,
    COUNT(*) AS store_count
FROM stores
GROUP BY cell
ORDER BY store_count DESC;

Use a geohash as a coarse pre-filter

Points that share a geohash prefix are close together, so matching on a prefix is a cheap way to gather candidates before measuring exact distances. This finds stores in the same cell as a target point:

SELECT id, name
FROM stores
WHERE geo_geohash_encode(lat, lon, 6) = geo_geohash_encode(37.7749, -122.4194, 6);

Keep in mind that cell boundaries are arbitrary: two points can be metres apart yet land in different cells. Treat prefix matching as a coarse bucket, and confirm with geo_haversine_distance when you need an exact radius:

SELECT id, name,
       geo_haversine_distance(lat, lon, 37.7749, -122.4194) AS meters
FROM stores
WHERE geo_geohash_encode(lat, lon, 5) = geo_geohash_encode(37.7749, -122.4194, 5)
  AND geo_haversine_distance(lat, lon, 37.7749, -122.4194) <= 3000
ORDER BY meters;

Tips

• Latitude first. Both functions take latitude before longitude. Mixing them up puts your points in the wrong hemisphere.
• Distances are in meters. Convert as needed: divide by 1000 for kilometers, or multiply by 0.000621371 for miles.
• Skip rows with missing coordinates. These functions expect a value in every argument. Exclude incomplete rows with WHERE lat IS NOT NULL AND lon IS NOT NULL.
• Store a geohash column for large tables. If you filter by area often, compute geo_geohash_encode(lat, lon, N) once, store it in a column, and index it. Then an area lookup becomes a fast equality match on that column.