WKT2 in PostGIS

[Javier Jimenez Shaw]

Hi

Maybe I was not clear on my initial explanation. Let me develop it.

Most of the cases a user will use a CRS from the EPSG. There are well know
ones like 4326, 32612 or 25830. That is covering many users. Cool.
However, not every CRS is in EPSG. There are "infinite" CRSs, just playing
with the parameters of the projections. And WKT2 gives even more options.
One typical case is a site calibration or site localization, where the CRS
is defined particularly for a location, like a construction site. There is
a talk in FOSS4G2023 about it with PROJ.
In those cases, the EPSG catalog is useless, and the user can define it
with WKT2. If I understood correctly, that would be a user defined CRS.

There is the potential problem with the size of "srtext". The WKT2 that
defines the CRS can have more than 2048 chars. (In understand that PostGIS
is supporting WKT2. PROJ does for years) The example of EPSG:25830 was just
an example on how can the WKT2 grow. It was already very verbose (compared
to WKT1) before the new addition of realizations to ETRS89.

As Paul says, probably changing to TEXT is the simplest solution. I don't
know the possible consequences.

Thank you
Javier.

On Wed, 9 Sept 2026 at 17:49, Paul Ramsey <pramsey at cleverelephant.ca> wrote:

> Why wouldn’t we just change those column definitions from varchar(N) to
> text?
>
> On Sep 9, 2026, at 8:47 AM, Regina Obe <lr at pcorp.us> wrote:
>
> Javier,
>
> We’ve been hesitant to touch this table to increase it. I know someone
> else requested this.
> Our feeling is the proj.db should be the authoritative source for most of
> these.
>
> I there a reason you wouldn’t add this to proj directly?  The issue with
> putting it in postgis spatial_ref_sys is if you use your system beyond
> postgis
> You’d have to duplicate the entry anyway.
>
> But I am and I think others were concerned for many users especially those
> who don’t need these defs elsewhere, it’s easier to edit
> postgis.spatial_ref_sys,
>
> PostGIS for since I think 3.0 has delegated most stuff to the proj.db
> table and just uses spatial_ref_sys auth_srid to look up the record in
> proj.db and largely ignores what is in srtext/proj4text columns.
>
> I have to check the auth_name.  I think we used to ignore what was in
> auth_name and assumed any auth_srid matched up with auth_name = epsg, but
> that I think changed somewhere across the line.
>
> Paul,
>
> If per chance you have remember or have the energy to chime in on this,
> that would be much appreciated.
>
> Thanks,
> Regina
>
> *From:* Javier Jimenez Shaw <j1 at jimenezshaw.com>
> *Sent:* Wednesday, September 9, 2026 7:09 AM
> *To:* postgis-devel at lists.osgeo.org
> *Subject:* WKT2 in PostGIS
>
> Hi
>
> In the new PROJ release 9.9.0 that will be released soon, the latest
> version of EPSG is included.
> All European realizations are included in the WKT2 of any CRS using the
> ETRS89 datum ensemble.
> This is an example of EPSG:25830, with 4264 chars.
>  https://epsg.org/crs/wkt/id/25830
> If you format it a bit nicely it goes up to 4874. And this is just an
> example.
>
> I know that this one can be expressed just with the code. However some
> people (including me) need to define a CRS that is not defined in EPSG. For
> that we are using WKT2.
>
> I realized today that in PostGIS the table containing the CRS has fields
> limited to 2048 chars, in particular "srtext".
>
> https://postgis.net/docs/using_postgis_dbmanagement.html#spatial_ref_sys_table
>
> CREATE TABLE spatial_ref_sys (
>   srid       INTEGER NOT NULL PRIMARY KEY,
>   auth_name  VARCHAR(256),
>   auth_srid  INTEGER,
>   srtext     VARCHAR(2048),
>   proj4text  VARCHAR(2048)
> )
>
> If I have to define a CRS with WKT2 because I need some features only
> available in WKT2, it may not work in PostGIS. Am I right?
>
> Thank you.
> Javier.
>
> PS this was first asked in
> https://lists.osgeo.org/pipermail/proj/2026-September/012183.html
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20260909/30e37c18/attachment-0001.htm>