CRS Transformation

WherobotsDB provides coordinate reference system (CRS) transformation through the ST_Transform function. The transformation engine supports multiple CRS input formats and grid-based transformations for high-accuracy datum shifts.

Supported CRS Formats

WherobotsDB supports the following formats for specifying source and target coordinate reference systems.

Authority Code

The most common way to specify a CRS is using an authority code in the format AUTHORITY:CODE. WherobotsDB uses spatialreference.org as an open-source CRS database, which supports multiple authorities:

Authority	Description	Example
EPSG	European Petroleum Survey Group	`EPSG:4326`, `EPSG:3857`
ESRI	Esri coordinate systems	`ESRI:102008`, `ESRI:54012`
IAU	International Astronomical Union (planetary CRS)	`IAU:30100`
SR-ORG	User-contributed definitions	`SR-ORG:6864`

-- Transform from WGS84 (EPSG:4326) to Web Mercator (EPSG:3857)
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    'EPSG:3857'
) AS transformed_point

Output:

POINT (-13627665.271218014 4548257.702387721)

-- Transform using ESRI authority code (North America Albers Equal Area Conic)
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    'ESRI:102008'
) AS transformed_point

-- Transform from WGS84 to UTM Zone 10N (EPSG:32610)
SELECT ST_Transform(
    ST_GeomFromText('POLYGON((-122.5 37.5, -122.5 38.0, -122.0 38.0, -122.0 37.5, -122.5 37.5))'),
    'EPSG:4326',
    'EPSG:32610'
) AS transformed_polygon

You can browse available CRS codes at spatialreference.org or EPSG.io.

WKT1 (OGC Well-Known Text)

WKT1 is the OGC Well-Known Text format for CRS definitions. It starts with PROJCS[...] for projected CRS or GEOGCS[...] for geographic CRS.

-- Transform using WKT1 format for target CRS
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    'PROJCS["WGS 84 / Pseudo-Mercator",
        GEOGCS["WGS 84",
            DATUM["WGS_1984",
                SPHEROID["WGS 84",6378137,298.257223563]],
            PRIMEM["Greenwich",0],
            UNIT["degree",0.0174532925199433]],
        PROJECTION["Mercator_1SP"],
        PARAMETER["central_meridian",0],
        PARAMETER["scale_factor",1],
        PARAMETER["false_easting",0],
        PARAMETER["false_northing",0],
        UNIT["metre",1],
        AUTHORITY["EPSG","3857"]]'
) AS transformed_point

WKT2 (ISO 19162:2019)

WKT2 is the modern ISO 19162:2019 standard format. It starts with PROJCRS[...] for projected CRS or GEOGCRS[...] for geographic CRS.

-- Transform using WKT2 format for target CRS
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    'PROJCRS["WGS 84 / UTM zone 10N",
        BASEGEOGCRS["WGS 84",
            DATUM["World Geodetic System 1984",
                ELLIPSOID["WGS 84",6378137,298.257223563]]],
        CONVERSION["UTM zone 10N",
            METHOD["Transverse Mercator"],
            PARAMETER["Latitude of natural origin",0],
            PARAMETER["Longitude of natural origin",-123],
            PARAMETER["Scale factor at natural origin",0.9996],
            PARAMETER["False easting",500000],
            PARAMETER["False northing",0]],
        CS[Cartesian,2],
            AXIS["easting",east],
            AXIS["northing",north],
        UNIT["metre",1],
        ID["EPSG",32610]]'
) AS transformed_point

PROJ String

PROJ strings provide a compact way to define CRS using projection parameters. They start with +proj=.

-- Transform using PROJ string for UTM Zone 10N
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    '+proj=longlat +datum=WGS84 +no_defs',
    '+proj=utm +zone=10 +datum=WGS84 +units=m +no_defs'
) AS transformed_point

-- Transform using PROJ string for Lambert Conformal Conic
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    '+proj=lcc +lat_1=33 +lat_2=45 +lat_0=39 +lon_0=-96 +x_0=0 +y_0=0 +datum=NAD83 +units=m +no_defs'
) AS transformed_point

PROJJSON

PROJJSON is a JSON representation of CRS, useful when working with JSON-based workflows.

-- Transform using PROJJSON for target CRS
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    'EPSG:4326',
    '{
        "type": "ProjectedCRS",
        "name": "WGS 84 / UTM zone 10N",
        "base_crs": {
            "name": "WGS 84",
            "datum": {
                "type": "GeodeticReferenceFrame",
                "name": "World Geodetic System 1984",
                "ellipsoid": {
                    "name": "WGS 84",
                    "semi_major_axis": 6378137,
                    "inverse_flattening": 298.257223563
                }
            },
            "coordinate_system": {
                "subtype": "ellipsoidal",
                "axis": [
                    {"name": "Longitude", "abbreviation": "lon", "direction": "east", "unit": "degree"},
                    {"name": "Latitude", "abbreviation": "lat", "direction": "north", "unit": "degree"}
                ]
            }
        },
        "conversion": {
            "name": "UTM zone 10N",
            "method": {"name": "Transverse Mercator"},
            "parameters": [
                {"name": "Latitude of natural origin", "value": 0, "unit": "degree"},
                {"name": "Longitude of natural origin", "value": -123, "unit": "degree"},
                {"name": "Scale factor at natural origin", "value": 0.9996},
                {"name": "False easting", "value": 500000, "unit": "metre"},
                {"name": "False northing", "value": 0, "unit": "metre"}
            ]
        },
        "coordinate_system": {
            "subtype": "Cartesian",
            "axis": [
                {"name": "Easting", "abbreviation": "E", "direction": "east", "unit": "metre"},
                {"name": "Northing", "abbreviation": "N", "direction": "north", "unit": "metre"}
            ]
        },
        "id": {"authority": "EPSG", "code": 32610}
    }'
) AS transformed_point

Grid File Support

Grid files enable high-accuracy datum transformations, such as NAD27 to NAD83 or OSGB36 to ETRS89. WherobotsDB supports loading grid files from multiple sources.

Grid File Sources

Grid files can be specified using the +nadgrids parameter in PROJ strings:

Source	Format	Example
Local file	Absolute path	`+nadgrids=/path/to/grid.gsb`
PROJ CDN	`@` prefix	`+nadgrids=@us_noaa_conus.tif`
HTTPS URL	Full URL	`+nadgrids=https://cdn.proj.org/us_noaa_conus.tif`

When using the @ prefix, grid files are automatically fetched from PROJ CDN.

Optional vs Mandatory Grids

@ prefix (optional): The transformation continues without the grid if it’s unavailable. Use this when the grid improves accuracy but isn’t required.
No prefix (mandatory): An error is thrown if the grid file cannot be found.

SQL Examples with Grid Files

-- Transform NAD27 to NAD83 using PROJ CDN grid (optional)
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    '+proj=longlat +datum=NAD27 +no_defs +nadgrids=@us_noaa_conus.tif',
    'EPSG:4269'
) AS transformed_point

-- Transform using mandatory grid file (error if not found)
SELECT ST_Transform(
    ST_GeomFromText('POINT(-122.4194 37.7749)'),
    '+proj=longlat +datum=NAD27 +no_defs +nadgrids=us_noaa_conus.tif',
    'EPSG:4269'
) AS transformed_point

-- Transform OSGB36 to ETRS89 using UK grid
SELECT ST_Transform(
    ST_GeomFromText('POINT(-0.1276 51.5074)'),
    '+proj=longlat +datum=OSGB36 +nadgrids=@uk_os_OSTN15_NTv2_OSGBtoETRS.gsb +no_defs',
    'EPSG:4258'
) AS transformed_point

Coordinate Order

WherobotsDB expects geometries to be in longitude/latitude (lon/lat) order. If your data is in lat/lon order, use ST_FlipCoordinates to swap the coordinates before transformation.

-- If your data is in lat/lon order, flip first
SELECT ST_Transform(
    ST_FlipCoordinates(ST_GeomFromText('POINT(37.7749 -122.4194)')),
    'EPSG:4326',
    'EPSG:3857'
) AS transformed_point

WherobotsDB automatically handles coordinate order in the CRS definition, ensuring the source and target CRS use lon/lat order internally.

Using Geometry SRID

If the geometry already has an SRID set, you can omit the source CRS parameter:

-- Set SRID on geometry and transform using only target CRS
SELECT ST_Transform(
    ST_SetSRID(ST_GeomFromText('POINT(-122.4194 37.7749)'), 4326),
    'EPSG:3857'
) AS transformed_point

Function Signatures

ST_Transform (A: Geometry, SourceCRS: String, TargetCRS: String)

ST_Transform (A: Geometry, TargetCRS: String)

For advanced use cases with an area of interest (experimental):

ST_Transform (A: Geometry, SourceCRS: String, TargetCRS: String, AOI: Geometry, lenientMode: Boolean)

​CRS Transformation

​Supported CRS Formats

​Authority Code

​WKT1 (OGC Well-Known Text)

​WKT2 (ISO 19162:2019)

​PROJ String

​PROJJSON

​Grid File Support

​Grid File Sources

​Optional vs Mandatory Grids

​SQL Examples with Grid Files

​Coordinate Order

​Using Geometry SRID

​Function Signatures

​See Also

CRS Transformation

Supported CRS Formats

Authority Code

WKT1 (OGC Well-Known Text)

WKT2 (ISO 19162:2019)

PROJ String

PROJJSON

Grid File Support

Grid File Sources

Optional vs Mandatory Grids

SQL Examples with Grid Files

Coordinate Order

Using Geometry SRID

Function Signatures

See Also