[postgis-devel] spatial_ref_sys view

[Even Rouault]

> 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.

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.

Restricting it to EPSG is ~ 600 ms , and ESRI ~ 900 ms (I suspect the 
longer time for ESRI is due to the fact of having to massage ESRI WKT to 
"standard" WKT using EPSG object names, involving additional lookups in 
the alias table)

Even



#include <proj.h>
#include <stdio.h>

void get_from_authority(PJ_CONTEXT* ctxt, const char* auth_name)
{
     PROJ_STRING_LIST list = proj_get_codes_from_database(ctxt, 
auth_name, PJ_TYPE_CRS, 0);
     for(char** iter = list; iter && *iter; ++iter)
     {
         PJ* pj = proj_create_from_database(ctxt, auth_name, *iter, 
PJ_CATEGORY_CRS, 0, 0);
         const char* wkt = proj_as_wkt(ctxt, pj, PJ_WKT2_2019, NULL);
         //printf("%s\n", wkt);
         proj_destroy(pj);
     }
     proj_string_list_destroy(list);
}

int main(int argc, char** argv)
{
     PJ_CONTEXT* ctxt = proj_context_create();
     if( argc == 2 )
     {
         get_from_authority(ctxt, argv[1]);
     }
     else
     {
         PROJ_STRING_LIST listAuth = 
proj_get_authorities_from_database(ctxt);
         for(char** iterAuth = listAuth; iterAuth && *iterAuth; ++iterAuth)
         {
             printf("%s\n", *iterAuth);
             get_from_authority(ctxt, *iterAuth);
         }
         proj_string_list_destroy(listAuth);
     }
     proj_context_destroy(ctxt);
     return 0;
}


>
> --strk;
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

-- 
http://www.spatialys.com
My software is free, but my time generally not.