<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote gmail_quote_container"><div dir="ltr" class="gmail_attr">On Mon, Oct 27, 2025 at 11:37 AM Jim Klassen <<a href="mailto:klassen.js@gmail.com">klassen.js@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><u></u>
<div>
How does the "srid" column in spatial_ref_sys relate to the CODE in
Proj? <br>
<br>
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. <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_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.)</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,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 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.<br></div></blockquote><div><br></div><div>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.</div><div><br></div><div>P.</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div>
<br>
psql:<br>
select auth_srid from spatial_ref_sys group by auth_srid having
count(auth_srid) > 1;<br>
<br>
sqlite3 proj.db:<br>
select code, count(*) c from projected_crs group by code having
c > 1;<br>
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="cite">
<div dir="auto">Thanks Darafei,
<div dir="auto"><br>
<div dir="auto">The scenario is the following. </div>
<div dir="auto"><br>
</div>
<div dir="auto">I have a custom proj.db which contains custom
CRSs and transformations defined with auth ids beyond the
PostGIS limit. </div>
<div dir="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. </div>
<div dir="auto"><br>
</div>
<div dir="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. </div>
<div dir="auto"><br>
</div>
<div dir="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. </div>
<div dir="auto"><br>
</div>
<div dir="auto">Giovanni </div>
<br>
<br>
<div class="gmail_quote" dir="auto">
<div dir="ltr" class="gmail_attr">Il lun 27 ott 2025, 13:17
Darafei "Komяpa" Praliaskouski <<a href="mailto:me@komzpa.net" rel="noreferrer" target="_blank">me@komzpa.net</a>> ha
scritto:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
<div dir="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 redesign in
the way PostGIS handles SRIDs.<br>
If you need this, sharing more information about the
actual usage scenarios will be helpful so a new design
can be created.</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Mon, Oct 27, 2025
at 1:46 PM G. Allegri <<a href="mailto:giohappy@gmail.com" rel="noreferrer noreferrer" target="_blank">giohappy@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
<div dir="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=998999 [1], which is hardcoded in
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. </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 reason for 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 solutions
they came up with.</div>
<div><br>
</div>
<div>Thanks,</div>
<div>Giovanni</div>
<div><br>
</div>
<div>[1] <a href="https://github.com/postgis/postgis/blob/5dc95f1bc3047b048128616d4543b603b8bbdca7/configure.ac#L1554" rel="noreferrer noreferrer" target="_blank">https://github.com/postgis/postgis/blob/5dc95f1bc3047b048128616d4543b603b8bbdca7/configure.ac#L1554</a></div>
</div>
</blockquote>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
<br>
</div>
</blockquote></div></div>