Why PostGIS sets a max value for SRID IDs?

[Jim Klassen]

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.

Back in the proj4 days when I was regularly dealing with a CRS that didn't have an assigned EPSG code, OGR would create a new spatial_ref_sys row with a "srid" (usually starting at 900914) that didn't match anything else in the row (auth_name, auth_srid, srtext, proj4text).

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.

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/17843808/attachment.htm>