Re: Why PostGIS sets a max value for SRID IDs?
Paul Ramsey via postgis-users <[email protected]> Mon, 27 Oct 2025 11:45:34 -0700
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <CACowWR29s+a=TGenQ6ZUXGCSd-QpTV9zTLMLDCuZqaJFasDWUQ@mail.gmail.com> |
--0000000000009e55cc0642284ee8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Oct 27, 2025 at 11:37=E2=80=AFAM Jim Klassen <[email protected]>= 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 mo= st > cases "srid" and "auth_srid" just happened to match for the convenience o= f > 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 (Moo= n) > 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 th= e > 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=D1=8Fpa" Praliaskouski <me@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=E2=80=AFPM G. Allegri <[email protected]> = 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=3D998999 [1], wh= ich >>> 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/5dc95f1bc3047b048128616d4543b60= 3b8bbdca7/configure.ac#L1554 >>> >> > --0000000000009e55cc0642284ee8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g= mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Oct 27,= 2025 at 11:37=E2=80=AFAM Jim Klassen <<a href=3D"mailto:klassen.js@gmai= l.com">[email protected]</a>> wrote:<br></div><blockquote class=3D"gm= ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-l= eft-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><u></u= > =20 =20 =20 <div> How does the "srid" column in spatial_ref_sys relate to the C= ODE in Proj?=C2=A0=C2=A0<br> <br> I had always assumed that the PostGIS "srid" was independent = of the ("auth_name", "auth_srid") tuple ("AUTH_NAME&q= uot;, "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. =C2=A0= <br></div></blockquote><div><br></div><div>This assumption is correct. The = PostGIS SRID is a number internal to the PostGIS instance, while the AUTH_N= AME/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 req= uirement 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.)</div><div>=C2= =A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e= x;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,2= 04,204);padding-left:1ex"><div>I would also expect a 1-to-1 mapping between= PostGIS "srid" and "auth_srid" to be impossible because the different authoritie= s can have overlapping codes.=C2=A0 There doesn't appear to be any duplic= ate "auth_srid"s in my spatial_ref_sys table.=C2=A0 However, look= ing at my installed proj.db database, this happens a few times (for 10820, 30165, 30170, and 30175).=C2=A0 10820 looks like ESRI and EPSG separate= ly define essentially the same CRS for that code.=C2=A0 And the 30xxx code= s are all EPSG, IAU_2015 conflicts (Moon) so unlikely to be mistaken in practice.<br></div></blockquote><div><br></div><div>Mostly the exter= nal ids don't conflict, but that is not a guarantee, as you note, and t= he AUTH_NAME/AUTH_SRID pairing along with the independence of the internal = SRID is how we deal with that.</div><div><br></div><div>P.</div><div>=C2=A0= </div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b= order-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,= 204);padding-left:1ex"><div> <br> psql:<br> =C2=A0 =C2=A0 select auth_srid from spatial_ref_sys group by auth_srid = having count(auth_srid) > 1;<br> <br> sqlite3 proj.db:<br> =C2=A0 =C2=A0 select code, count(*) c from projected_crs group by code = having c > 1;<br> =C2=A0 =C2=A0 select * from projected_crs where code in (10820,30165,30170,30175) order by code, auth_name;<br> <br> <div>On 10/27/25 8:15 AM, G. Allegri wrote:<br> </div> <blockquote type=3D"cite"> =20 <div dir=3D"auto">Thanks Darafei,=C2=A0 <div dir=3D"auto"><br> <div dir=3D"auto">The scenario is the following.=C2=A0</div> <div dir=3D"auto"><br> </div> <div dir=3D"auto">I have a custom proj.db which contains custom CRSs and transformations defined with auth ids beyond the PostGIS limit.=C2=A0</div> <div dir=3D"auto">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.=C2=A0</d= iv> <div dir=3D"auto"><br> </div> <div dir=3D"auto">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.=C2=A0</div> <div dir=3D"auto"><br> </div> <div dir=3D"auto">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.=C2=A0</div> <div dir=3D"auto"><br> </div> <div dir=3D"auto">Giovanni=C2=A0</div> <br> <br> <div class=3D"gmail_quote" dir=3D"auto"> <div dir=3D"ltr" class=3D"gmail_attr">Il lun 27 ott 2025, 13:17 Darafei "Kom=D1=8Fpa" Praliaskouski <<a href=3D"= mailto:[email protected]" rel=3D"noreferrer" target=3D"_blank">[email protected]</a= >> ha scritto:<br> </div> <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0= .8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(20= 4,204,204);padding-left:1ex"> <div dir=3D"ltr">Hi,<br> <br> The technical reason is that SRID gets packed into geometry headers and there's only 21 bits there for it.= <br> PostGIS reserves the values above 998999 for internal transformation pipelines that support geography data type<br> A change to this will require significant=C2=A0redesign in the way PostGIS handles SRIDs.<br> If you need this, sharing more information about the actual usage scenarios will be helpful so=C2=A0a new=C2=A0d= esign can be created.</div> <br> <div class=3D"gmail_quote"> <div dir=3D"ltr" class=3D"gmail_attr">On Mon, Oct 27, 2025 at 1:46=E2=80=AFPM G. Allegri <<a href=3D"mailto:gioha= [email protected]" rel=3D"noreferrer noreferrer" target=3D"_blank">giohappy@gma= il.com</a>> wrote:<br> </div> <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0= px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rg= b(204,204,204);padding-left:1ex"> <div dir=3D"ltr">Hello list, <div><br> </div> <div>I'm working on two projects where custom CRSs, with custom authorities and IDs are provided.</div> <div>Both projects use IDs with numbers beyond SRID_USR_MAX=3D998999 [1], which is hardcoded=C2=A0in PostGIS for the spatial_ref_sys id field values.</div= > <div>I can use the auth_id, of course, but having to reassign an id < 998999 is a bit problematic for two reasons:</div> <div><br> </div> <div>- I have custom transformation pipelines defined inside the proj.db, where the custom IDs are defined for CRSs.=C2=A0</div> <div>- other softwares (QGIS, Geoserver) can use the custom IDs but they cannot match the geometries SRIDs returned PostGIS</div> <div><br> </div> <div>I wonder if there's a technical=C2=A0reason fo= r the SRID_USR_MAX constant, and if there's any change to remove it in the future.</div> <div>And I wonder if others have faced the problems I'm having due to this, and what are the solution= s they came=C2=A0up with.</div> <div><br> </div> <div>Thanks,</div> <div>Giovanni</div> <div><br> </div> <div>[1]=C2=A0<a href=3D"https://github.com/postgis/pos= tgis/blob/5dc95f1bc3047b048128616d4543b603b8bbdca7/configure.ac#L1554" rel= =3D"noreferrer noreferrer" target=3D"_blank">https://github.com/postgis/pos= tgis/blob/5dc95f1bc3047b048128616d4543b603b8bbdca7/configure.ac#L1554</a></= div> </div> </blockquote> </div> </blockquote> </div> </div> </div> </blockquote> <br> </div> </blockquote></div></div> --0000000000009e55cc0642284ee8--