No subject

- It looked weird in a PostGIS context to use underneath a SQLite db, but I think that this is
just a PROJ implementation detail. One suggested idea was to have some storage plugable
abstraction layer so that PostGIS could for example decide that the CRS DB would be stored
in PostgreSQL tables. I'm not entirely convinced that this is really something worth it :
PostGIS spatial_ref_sys table cannot be directly derived from the soure tables. So such
abstraction layer seems to me to be 'premature complication'. In any case, any abstraction
would likely be constrained to rely on SQL.

- There was also some questionning about the difficulity of porting the new PROJ to languages
like JavaScript. My quick look at proj4js is that it doesn't contain currently the 'epsg' file.
Furthermore there are ports to JavaScript of sqlite like https://github.com/kripken/sql.js that
could be used if proj4js wanted to add the new capabilities (but they'd have to first port
more recent changes of proj v5, like pipelines etc)

~~~~~~~

For the sake of avoiding to have different proj flavours, I'd be inclined to make sqlite a
required dependency of proj. SQLite is available anywhere, so this shouldn't cause practical
issues (binary distributions of the GIS stack have all sqlite packaged, being a requirement of
GDAL, QGIS, spatialite, etc...). The SQLite capabilities and API effectively needed shouldn't
require imposing a version newer than 3.7.0.

~~~~~~

Performance wise, I just did a little experiment to see how fast it is to get all the elements
needed to build a WKT or proj string from the raw tables

For example, if you need to solve EPSG:32758 (WGS 84 / UTM zone 58S), you have mostly
to do the following queries:

select coord_ref_sys_kind, projection_conv_code, projection_conv_code from epsg_coordinatereferencesystem where coord_ref_sys_code = 32758;
select coord_ref_sys_kind, datum_code from epsg_coordinatereferencesystem where coord_ref_sys_code = 4326;
select * from epsg_datum where datum_code = 6326;
select * from epsg_primemeridian where prime_meridian_code = 8901;
select * from epsg_ellipsoid where ellipsoid_code = 7030;
select coord_op_method_code from epsg_coordoperation where coord_op_code = 16158;
select sort_order, cop.parameter_code, parameter_name, parameter_value from epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = 9807 AND copv.coord_op_method_code = 9807 AND coord_op_code = 16158 ORDER BY sort_order;

If I put this in a script.sql file and run the following:

sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'; # clears OS I/O caches
echo "" | sqlite3;  # warms things a bit by loading sqlite
time sqlite3 epsg.sqlite < script.sql; # cold run
time sqlite3 epsg.sqlite < script.sql; # hot run

On my PC, the cold run is 174 ms, and following ones in 3 ms


Now let's try to compare this to the current situation with

sudo sh -c 'echo 3 >/proc/sys/vm/drop_caches'; # clears OS I/O caches
LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs -v;  # warms a bit loading proj bin/lib
time echo "0 0" | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +proj=utm +zone=58 +south;  # reference time to measure proj loading + actual computation overhead
time echo "0 0" | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +init=epsg:32758; # cold run
time echo "0 0" | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +init=epsg:32758; # hot run

The time specific of reading the 'epsg' file is obtained substracting the time with
'+init=epsg:32758;' to the time with '+proj=utm +zone=58 +south'
The cold run is 45 ms (with lots of variations, sometimes up to 300 ms) and 10 ms for a hot run.
This case is a bit the worth one in the CSV usage since 32758 is at the end of the file, so it must be fully ingested.

strace shows that in the sqlite case 100 KB of data have been read, to be compared to the 900 KB of
the 'epsg' file

Overall, the performance of hot runs with SQLite should be better. Anyway, actual uses of
proj do not require setting up thousands of CRS, so querying the DB is generally not something in
the critical path.

~~~~~~

I have another potentially controversial topic to discuss, but I'll address it in a separate thread :-)

Even

[1] This DB could also *potentially* contain the polygons of the area of validity from
the shapefiles provided by EPSG, although that would substantially increase its size (if using
original geometries, 62 MB once converted in GeoPackage format, the same as the original
shapefile), so probably not something to consider right now (or with generalized geometries)


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com

--nextPart2443458.1rdKhr4c5W
Content-Transfer-Encoding: 7Bit
Content-Type: text/html; charset="us-ascii"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">
<html><head><meta name="qrichtext" content="1" /><style type="text/css">
p, li { white-space: pre-wrap; }
</style></head><body style=" font-family:'Sans Serif'; font-size:9pt; font-weight:400; font-style:normal;">
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Hi,</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">As you know, as part of the improvements described at https://gdalbarn.com/,</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">we plan to use a SQLite database as a replacement for proj's 'epsg' and other</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">dictionary files.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Before concrete work is done in that direction, I'd like to make sure we can come</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">to a concensus on that.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">There are many reasons for the SQLite choice, especially:</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- unification of CRS databases: currently libgeotiff, GDAL, proj, QGIS have their own copy of</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">  more or less the same CRS definitions. People are often confused on where to make changes.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- libgeotiff, GDAL, proj definitions come in text files (CSV or custom proj format). Extracting a</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">record in those is in average linear with the size of the database, whereas log(N) performance</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">can be achiveed with a properly indexed database (that said: probably not a huge issue in practice</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">given the limited size of those databases), but the big advantage of SQLite is that it brings</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">naturally a powerful language to request the database.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">So the idea would be to import the tables from a EPSG database dump (potentially not</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">in their raw form, but in a modified form to comply with current EPSG Term of Services.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">We are working with IOGP on that issue) and add auxiliary tables/columns to define:</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- overrides</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- other CRS catalogs which we have in standalone files: ignf, esri_extra</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- additional CRS definitions</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- prefered CRS conversion steps/pipelines</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- location of grids</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- other things [1]</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">The exact DB layout is yet to be defined.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">~~~~~~~</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">From side channels, I've heard some questionning regarding the choice of SQLite. </p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- It looked weird in a PostGIS context to use underneath a SQLite db, but I think that this is</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">just a PROJ implementation detail. One suggested idea was to have some storage plugable</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">abstraction layer so that PostGIS could for example decide that the CRS DB would be stored</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">in PostgreSQL tables. I'm not entirely convinced that this is really something worth it :</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">PostGIS spatial_ref_sys table cannot be directly derived from the soure tables. So such</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">abstraction layer seems to me to be 'premature complication'. In any case, any abstraction</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">would likely be constrained to rely on SQL.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">- There was also some questionning about the difficulity of porting the new PROJ to languages</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">like JavaScript. My quick look at proj4js is that it doesn't contain currently the 'epsg' file.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Furthermore there are ports to JavaScript of sqlite like https://github.com/kripken/sql.js that</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">could be used if proj4js wanted to add the new capabilities (but they'd have to first port</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">more recent changes of proj v5, like pipelines etc)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">~~~~~~~</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">For the sake of avoiding to have different proj flavours, I'd be inclined to make sqlite a</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">required dependency of proj. SQLite is available anywhere, so this shouldn't cause practical</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">issues (binary distributions of the GIS stack have all sqlite packaged, being a requirement of</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">GDAL, QGIS, spatialite, etc...). The SQLite capabilities and API effectively needed shouldn't</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">require imposing a version newer than 3.7.0.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">~~~~~~</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Performance wise, I just did a little experiment to see how fast it is to get all the elements</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">needed to build a WKT or proj string from the raw tables</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">For example, if you need to solve EPSG:32758 (WGS 84 / UTM zone 58S), you have mostly</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">to do the following queries:</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select coord_ref_sys_kind, projection_conv_code, projection_conv_code from epsg_coordinatereferencesystem where coord_ref_sys_code = 32758;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select coord_ref_sys_kind, datum_code from epsg_coordinatereferencesystem where coord_ref_sys_code = 4326;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select * from epsg_datum where datum_code = 6326;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select * from epsg_primemeridian where prime_meridian_code = 8901;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select * from epsg_ellipsoid where ellipsoid_code = 7030;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select coord_op_method_code from epsg_coordoperation where coord_op_code = 16158;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">select sort_order, cop.parameter_code, parameter_name, parameter_value from epsg_coordoperationparam cop LEFT JOIN epsg_coordoperationparamvalue copv LEFT JOIN epsg_coordoperationparamusage copu ON cop.parameter_code = copv.parameter_code AND copu.parameter_code = copv.parameter_code WHERE copu.coord_op_method_code = 9807 AND copv.coord_op_method_code = 9807 AND coord_op_code = 16158 ORDER BY sort_order;</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">If I put this in a script.sql file and run the following:</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">sudo sh -c 'echo 3 &gt;/proc/sys/vm/drop_caches'; # clears OS I/O caches</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">echo &quot;&quot; | sqlite3;  # warms things a bit by loading sqlite</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">time sqlite3 epsg.sqlite &lt; script.sql; # cold run</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">time sqlite3 epsg.sqlite &lt; script.sql; # hot run</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">On my PC, the cold run is 174 ms, and following ones in 3 ms</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Now let's try to compare this to the current situation with</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">sudo sh -c 'echo 3 &gt;/proc/sys/vm/drop_caches'; # clears OS I/O caches</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs -v;  # warms a bit loading proj bin/lib</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">time echo &quot;0 0&quot; | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +proj=utm +zone=58 +south;  # reference time to measure proj loading + actual computation overhead</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">time echo &quot;0 0&quot; | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +init=epsg:32758; # cold run</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">time echo &quot;0 0&quot; | LD_LIBRARY_PATH=src/.libs PROJ_LIB=nad src/.libs/cs2cs +proj=longlat +datum=WGS84 +to +init=epsg:32758; # hot run</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">The time specific of reading the 'epsg' file is obtained substracting the time with</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">'+init=epsg:32758;' to the time with '+proj=utm +zone=58 +south'</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">The cold run is 45 ms (with lots of variations, sometimes up to 300 ms) and 10 ms for a hot run.</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">This case is a bit the worth one in the CSV usage since 32758 is at the end of the file, so it must be fully ingested.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">strace shows that in the sqlite case 100 KB of data have been read, to be compared to the 900 KB of</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">the 'epsg' file</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Overall, the performance of hot runs with SQLite should be better. Anyway, actual uses of</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">proj do not require setting up thousands of CRS, so querying the DB is generally not something in</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">the critical path.</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">~~~~~~</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I have another potentially controversial topic to discuss, but I'll address it in a separate thread :-)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Even</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">[1] This DB could also *potentially* contain the polygons of the area of validity from</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">the shapefiles provided by EPSG, although that would substantially increase its size (if using</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">original geometries, 62 MB once converted in GeoPackage format, the same as the original</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">shapefile), so probably not something to consider right now (or with generalized geometries)</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">&nbsp;</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">-- </p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Spatialys - Geospatial professional services</p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">http://www.spatialys.com</p></body></html>
--nextPart2443458.1rdKhr4c5W--