[postgis-devel] spatial_ref_sys view

[Sandro Santilli]

On Fri, Feb 04, 2022 at 09:12:22PM +0100, Even Rouault wrote:
> 
> > When you talk about speed, in which cases do you think querying
> > spatial_ref_sys is going to be a problem ? Aren't we caching the
> > lookups ?
> 
> The OGR PostgreSQL driver can issue a "SELECT srid FROM spatial_ref_sys
> WHERE srtext = %s" request in a fallback case, when creating a new table
> with a CRS which has no known (auth_name, auth_srid) tuple.

That query in current postgis takes in the worst case around 5ms.
In my spatial-ref-sys-view, with an empty spatial_ref_sys_user
it takes about the same time, with a spatial_ref_sys_user holding
a full copy of the spatial_ref_sys_system it takes about 8ms.

> Retrieving all the CRS from all authorities (mostly EPSG and ESRI. other
> authorities are marginal) from the database of PROJ master and getting their
> WKT definition takes  ~ 1.6 sec with the below program.

Loading spatial_ref_sys.sql takes about 16ms (for 8500 rows).

--strk;