Why PostGIS sets a max value for SRID IDs?

[Paul Ramsey]

On Mon, Oct 27, 2025 at 11:37 AM Jim Klassen <klassen.js at gmail.com> wrote:

> How does the "srid" column in spatial_ref_sys relate to the CODE in Proj?
>
> I had always assumed that the PostGIS "srid" was independent of the
> ("auth_name", "auth_srid") tuple ("AUTH_NAME", "CODE" in proj.db) on
> account of them being separate columns in spatial_ref_sys, and that in most
> cases "srid" and "auth_srid" just happened to match for the convenience of
> humans who might happen to recognize a EPSG code.
>

This assumption is correct. The PostGIS SRID is a number internal to the
PostGIS instance, while the AUTH_NAME/AUTH_SRID pair provide the external
code. The fact that they are almost always 1:1 the same is a helpful
affordance, but not indicative of any requirement that the SRID in PostGIS
be the same as an SRID from outside. (For a dramatic example of this, the
handling of Oracle Spatial of SRID numbers unlinked the internal SRID
number from the external number.)


> I would also expect a 1-to-1 mapping between PostGIS "srid" and
> "auth_srid" to be impossible because the different authorities can have
> overlapping codes.  There doesn't appear to be any duplicate "auth_srid"s
> in my spatial_ref_sys table.  However, looking at my installed proj.db
> database, this happens a few times (for 10820, 30165, 30170, and 30175).
> 10820 looks like ESRI and EPSG separately define essentially the same CRS
> for that code.  And the 30xxx codes are all EPSG, IAU_2015 conflicts (Moon)
> so unlikely to be mistaken in practice.
>

Mostly the external ids don't conflict, but that is not a guarantee, as you
note, and the AUTH_NAME/AUTH_SRID pairing along with the independence of
the internal SRID is how we deal with that.

P.


>
> psql:
>     select auth_srid from spatial_ref_sys group by auth_srid having
> count(auth_srid) > 1;
>
> sqlite3 proj.db:
>     select code, count(*) c from projected_crs group by code having c > 1;
>     select * from projected_crs where code in (10820,30165,30170,30175)
> order by code, auth_name;
>
> On 10/27/25 8:15 AM, G. Allegri wrote:
>
> Thanks Darafei,
>
> The scenario is the following.
>
> I have a custom proj.db which contains custom CRSs and transformations
> defined with auth ids beyond the PostGIS limit.
> This proj.db is employed in several contexts, including GDAL scripts,
> QGIS, and Geoserver, with the Proj lib and Geotools handling the custom
> CRSs as expected.
>
> The problem happens when I need to handle the data with PostGIS. I must
> configure the custom CRSs (it's still not totally clear to me the
> relationship between the spatial_ref_sys and the proj.db in PostGIS by the
> way) and assign them to my data.
>
> The various softwares write and read data to/from PostGIS and when it
> comes to setting/reading the SRIDs, the problems with the mismatched IDs
> arise.
>
> Giovanni
>
>
> Il lun 27 ott 2025, 13:17 Darafei "Komяpa" Praliaskouski <me at komzpa.net>
> ha scritto:
>
>> Hi,
>>
>> The technical reason is that SRID gets packed into geometry headers and
>> there's only 21 bits there for it.
>> PostGIS reserves the values above 998999 for internal transformation
>> pipelines that support geography data type
>> A change to this will require significant redesign in the way PostGIS
>> handles SRIDs.
>> If you need this, sharing more information about the actual usage
>> scenarios will be helpful so a new design can be created.
>>
>> On Mon, Oct 27, 2025 at 1:46 PM G. Allegri <giohappy at gmail.com> wrote:
>>
>>> Hello list,
>>>
>>> I'm working on two projects where custom CRSs, with custom authorities
>>> and IDs are provided.
>>> Both projects use IDs with numbers beyond SRID_USR_MAX=998999 [1], which
>>> is hardcoded in PostGIS for the spatial_ref_sys id field values.
>>> I can use the auth_id, of course, but having to reassign an id < 998999
>>> is a bit problematic for two reasons:
>>>
>>> - I have custom transformation pipelines defined inside the proj.db,
>>> where the custom IDs are defined for CRSs.
>>> - other softwares (QGIS, Geoserver) can use the custom IDs but they
>>> cannot match the geometries SRIDs returned PostGIS
>>>
>>> I wonder if there's a technical reason for the SRID_USR_MAX constant,
>>> and if there's any change to remove it in the future.
>>> And I wonder if others have faced the problems I'm having due to this,
>>> and what are the solutions they came up with.
>>>
>>> Thanks,
>>> Giovanni
>>>
>>> [1]
>>> https://github.com/postgis/postgis/blob/5dc95f1bc3047b048128616d4543b603b8bbdca7/configure.ac#L1554
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20251027/43be481c/attachment.htm>