postgis_srs_search — Return metadata records for projected coordinate systems that have areas of usage that fully contain the bounds parameter.
setof record postgis_srs_search(
geometry bounds, text auth_name=EPSG)
;
Return a set of metadata records for projected coordinate systems that have areas of usage that fully contain the bounds parameter. Each record will have the auth_name
, auth_srid
, srname
, srtext
, proj4text
, and the corners of the area of usage, point_sw
and point_ne
.
The search only looks for projected coordinate systems, and is intended for users to explore the possible systems that work for the extent of their data.
Availability: 3.4.0
Proj version 6+
Search for projected coordinate systems in Louisiana.
SELECT auth_name, auth_srid, srname, ST_AsText(point_sw) AS point_sw, ST_AsText(point_ne) AS point_ne FROM postgis_srs_search('SRID=4326;LINESTRING(-90 30, -91 31)') LIMIT 3; auth_name | auth_srid | srname | point_sw | point_ne -----------+-----------+--------------------------------------+---------------------+--------------------- EPSG | 2801 | NAD83(HARN) / Louisiana South | POINT(-93.94 28.85) | POINT(-88.75 31.07) EPSG | 3452 | NAD83 / Louisiana South (ftUS) | POINT(-93.94 28.85) | POINT(-88.75 31.07) EPSG | 3457 | NAD83(HARN) / Louisiana South (ftUS) | POINT(-93.94 28.85) | POINT(-88.75 31.07)
Scan a table for max extent and find projected coordinate systems that might suit.
WITH ext AS ( SELECT ST_Extent(geom) AS geom, Max(ST_SRID(geom)) AS srid FROM foo ) SELECT auth_name, auth_srid, srname, ST_AsText(point_sw) AS point_sw, ST_AsText(point_ne) AS point_ne FROM ext CROSS JOIN postgis_srs_search(ST_SetSRID(ext.geom, ext.srid)) LIMIT 3;