Determine if an srid refers to a geodetic CRS

[Paul Ramsey]

Internally we have support functions that check if an SRID uses units
of degrees (srid_is_latlong), but they are not exposed in SQL. Could
be added for next release.

On Wed, Apr 30, 2025 at 3:03 AM Richard Huesken
<richard.huesken at gmail.com> wrote:
>
> All,
>
> Thanks for the replies and confirmation (and warning about caveats). I will use the predicate suggested by  @Erik Wienhold
>
> I'm on Postgis 3.3.3
>
> "SELECT ST_IsGeographic(4326);" returns: "SQL Error [42883]: ERROR: function st_isgeographic(integer) does not exist"
>
> Kind regards,
> Richard
>
>
> Op wo 30 apr 2025 om 00:30 schreef Erik Wienhold <ewie at ewie.name>:
>>
>> On 2025-04-30 00:01 +0200, Pettycash dev wrote:
>> > ### 1) **Checking if `srtext` starts with "GEOGCS"?**
>> > While `srtext` for geodetic CRSs (e.g., WGS84) starts with `GEOGCS` in its
>> > WKT definition (e.g., `GEOGCS["WGS 84", ...]`), this method has caveats:
>> >    - **It’s not foolproof**: The `srtext` format can vary (e.g.,
>> > whitespace, custom definitions).
>> >    - **Not future-proof**: Relies on text parsing, which may break if WKT
>> > conventions change.
>> >
>> > Example query (works in most cases, but not fully robust):
>> > ```sql
>> > SELECT srid, srtext
>> > FROM spatial_ref_sys
>> > WHERE srtext LIKE 'GEOGCS%'; -- Filters geodetic CRSs
>> > ```
>>
>> According to annex C.4.1 of the standard [1] "geodetic CRS" covers both
>> geographic and geocentric CRS.  So GEOGCS and GEOCCS are relevant.  Also
>> note annex B.8 since there's also GEODCRS, GEODETICCRS, GEOGCRS,
>> GEOGRAPHICCRS in new versions.  Because those are the only ones with
>> prefix "GEO", you might get away with just using that predicate:
>>
>>     srtext LIKE 'GEO%'
>>
>> [1] https://docs.ogc.org/is/18-010r11/18-010r11.pdf
>>
>> > ### 2) **Use the built-in PostGIS function `ST_IsGeographic()`** ✅
>> > PostGIS provides **`ST_IsGeographic(srid)`** to directly check if an SRID
>> > is geodetic. This is the **recommended method** because:
>> >    - It uses PostGIS’s internal logic (no text parsing).
>> >    - Handles edge cases and custom definitions.
>>
>> PostGIS does not provide ST_IsGeographic to my knowledge.  Perhaps a
>> separate extension?  Or just an AI hallucination?
>>
>> > > Le mar. 29 avr. 2025, 22:45, Richard Huesken <richard.huesken at gmail.com>
>> > > a écrit :
>> > >
>> > >> I'm wondering if there is an easy way to detect if an srid refers to a
>> > >> geodetic CRS, by checking the spatial_ref_sys table.
>> > >>
>> > >> 1) Would it be safe to assume the srid is a geodetic CRS if the value in
>> > >> the srtext column starts with 'GEO'? It almost seems to be too good to be
>> > >> true...
>> > >>
>> > >> 2) Did I overlook a Postgis function that returns this information (and
>> > >> if so, which one would that be)?
>>
>> --
>> Erik Wienhold