Use ST_SrEqual to compare spatial reference systems - ApsaraDB RDS

Determines whether two spatial reference systems are semantically equivalent by comparing their projection parameters rather than their string representations.

Syntax

boolean ST_SrEqual(cstring sr1, cstring sr2, boolean strict default true);

Parameters

Parameter	Type	Description	Default
`sr1`	cstring	The first spatial reference system. Must be an OGC WKT or PROJ.4 string.	—
`sr2`	cstring	The second spatial reference system. Must be an OGC WKT or PROJ.4 string.	—
`strict`	boolean	When `true`, also compares the names of the reference ellipsoids for georeferenced spatial reference systems.	`true`

Description

ST_SrEqual parses the semantics of both input strings and compares the following parameters:

Projection method
Reference ellipsoid
Semi-major and semi-minor axes of the reference ellipsoid

Returns t if the spatial reference systems are equivalent, or f if they differ.

Examples

Compare two spatial reference systems in different formats

This example compares an OGC WKT string against a PROJ.4 string representing WGS 84. Both define the same spatial reference system, so the function returns t.

SELECT ST_srEqual(
  'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]',
  '+proj=longlat +datum=WGS84 +no_defs'
);

Result:

 st_srequal
------------
 t

Look up a SRID by PROJ.4 string

Use ST_SrEqual as a filter against the spatial_ref_sys table to find the spatial reference identifier (SRID) that matches a given PROJ.4 string.

SELECT srid FROM spatial_ref_sys
WHERE ST_srEqual(srtext::cstring, '+proj=longlat +ellps=GRS80 +no_defs ')
LIMIT 1;

Result:

 srid
------
 3824