Inroduction
Many PostgreSQL applications that use PostGIS construct geographic points like this:
ST_SetSRID(ST_MakePoint(lon, lat), 4326)
This pattern is extremely common in location-based queries such as:
● radius searches
● nearest neighbor queries
● geospatial filtering
● proximity alerts
However, YugabyteDB currently does not include PostGIS geometry types or functions.
That raises a common migration question:
- What should replace
ST_SetSRID(ST_MakePoint(...),4326)when moving from PostgreSQL/PostGIS to YugabyteDB?
The answer is simpler than many developers expect.
In YugabyteDB, geographic coordinates are typically stored as plain numeric latitude and longitude columns, standardized on the WGS84 coordinate system.
If you haven’t already seen it, this concept is part of the distributed search pattern described in the earlier tip:
That article explains how to perform distributed radius searches using:
● coverage cells
● bounding boxes
● distance calculations
● distributed filtering
This tip focuses on a smaller but extremely common building block:
ST_SetSRID(ST_MakePoint(...),4326)
What does 4326 mean?
The number 4326 refers to a coordinate system identifier from the EPSG spatial reference registry.
EPSG:4326 details:
Example coordinate:
- Latitude: 40.4406
- Longitude: -79.9959
Almost every GPS coordinate you see on a mobile phone is stored using EPSG:4326.
What ST_SetSRID() actually does
In PostGIS, this function attaches coordinate system metadata to a geometry object.
Example:
SELECT ST_SetSRID(ST_Point(-79.9959, 40.4406), 4326);
Important detail:
ST_SetSRID()does NOT transform coordinates.
It simply tells PostGIS:
- “These coordinates are stored in coordinate system EPSG:4326.”
If coordinate reprojection is needed, PostGIS uses:
ST_Transform()
Why this matters for YugabyteDB
PostGIS geometry objects store coordinate system metadata.
YugabyteDB’s geospatial approach instead favors:
● flat numeric columns
● explicit query logic
● distributed filtering pipelines
So instead of geometry objects like this:
POINT(-79.9959 40.4406)
you typically store:
- lon = -79.9959
- lat = 40.4406
And the database simply assumes the coordinates follow WGS84.
The YugabyteDB equivalent pattern
Instead of this PostGIS code:
ST_SetSRID(ST_MakePoint(-79.9959, 40.4406), 4326)
In YugabyteDB you typically just write:
lon = -79.9959
lat = 40.4406
The SRID concept becomes a convention rather than metadata.
A lightweight helper for migrating applications
Step 1: Create a lightweight point type
CREATE TYPE yb_wgs84_point AS (
lon DOUBLE PRECISION,
lat DOUBLE PRECISION,
srid INTEGER
);
This preserves the developer intent that the coordinates belong to SRID 4326.
Step 2: Create a helper function
CREATE OR REPLACE FUNCTION yb_make_wgs84_point(
lon DOUBLE PRECISION,
lat DOUBLE PRECISION
)
RETURNS yb_wgs84_point
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT ROW(lon, lat, 4326)::yb_wgs84_point;
$$;
Example usage:
SELECT yb_make_wgs84_point(-79.9959, 40.4406);
Result:
(-79.9959,40.4406,4326)
Optional helper that mimics ST_SetSRID
If you want SQL that looks even closer to the PostGIS pattern:
CREATE OR REPLACE FUNCTION yb_set_srid(
lon DOUBLE PRECISION,
lat DOUBLE PRECISION,
srid INTEGER
)
RETURNS yb_wgs84_point
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
IF srid <> 4326 THEN
RAISE EXCEPTION
'Only SRID 4326 is supported by this helper. Got %',
srid;
END IF;
RETURN ROW(lon, lat, srid)::yb_wgs84_point;
END;
$$;
Usage:
SELECT yb_set_srid(-79.9959, 40.4406, 4326);
This provides a migration bridge from:
ST_SetSRID(ST_MakePoint(lon,lat),4326)
to
yb_set_srid(lon,lat,4326)
Best practice for real tables
In YugabyteDB production schemas, it is usually better to keep coordinates flat and numeric.
Example table:
CREATE TABLE places (
place_id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
lon DOUBLE PRECISION NOT NULL,
lat DOUBLE PRECISION NOT NULL,
CHECK (lat BETWEEN -90 AND 90),
CHECK (lon BETWEEN -180 AND 180)
);
Insert using the helper:
INSERT INTO places (name, lon, lat)
SELECT
'Pittsburgh',
(p).lon,
(p).lat
FROM (
SELECT yb_make_wgs84_point(-79.9959, 40.4406) AS p
) s;
Important limitation
These helpers do not recreate PostGIS geometry behavior.
They only preserve the coordinate convention.
They do NOT provide:
● geometry operators
● spatial indexing
● polygon logic
● reprojection
● native
ST_DWithin()support
Actual spatial filtering in YugabyteDB uses the distributed pipeline described in the earlier tip.
If you are replacing radius queries such as ST_DWithin() and ST_Distance(), see:
How to Replace ST_DWithin Radius Queries in YugabyteDB Without PostGIS
That article explains how to implement distributed radius searches using:
- ● coverage cells
- ● bounding box pruning
- ● exact distance calculations
- ● nearest-result ordering
Final takeaway
ST_SetSRID() is primarily about declaring the coordinate system, not performing spatial computation.
In PostGIS it attaches metadata indicating that coordinates use EPSG:4326 (WGS84).
In YugabyteDB, the equivalent approach is usually much simpler:
● store
lonandlatdirectly● standardize on WGS84 coordinates
● optionally provide helper functions for readability
● use distributed filtering techniques for spatial queries
This keeps schemas simple, scalable, and compatible with YugabyteDB’s distributed architecture.
Have Fun!
