Merge pull request #2577 from rouault/insert_sql

Add capability to get SQL statements to add custom CRS in the database

2 files changed, 145 insertions, 3 deletions

diff --git a/docs/source/apps/projinfo.rst b/docs/source/apps/projinfo.rst
index 826cd244..6c090494 100644
--- a/docs/source/apps/projinfo.rst
+++ b/docs/source/apps/projinfo.rst
@@ -25,11 +25,15 @@ Synopsis
     |    [--show-superseded] [--hide-ballpark] [--accuracy {accuracy}]
     |    [--allow-ellipsoidal-height-as-vertical-crs]
     |    [--boundcrs-to-wgs84]
+    |    [--authority name]
     |    [--main-db-path path] [--aux-db-path path]*
+    |    [--dump-db-structure]
     |    [--identify] [--3d]
+    |    [--output-id AUTH:CODE]
     |    [--c-ify] [--single-line]
-    |    --searchpaths | --remote-data | {object_definition} |
-    |    {object_reference} | (-s {srs_def} -t {srs_def})
+    |    --searchpaths | --remote-data |
+    |    --dump-db-structure [{object_definition} | {object_reference}] |
+    |    {object_definition} | {object_reference} | (-s {srs_def} -t {srs_def})
     |
 
     where {object_definition} or {srs_def} is one of the possibilities accepted
@@ -78,7 +82,7 @@ The following control parameters can appear in any order:
 .. option:: -o formats
 
     formats is a comma separated combination of:
-    ``all``, ``default``, ``PROJ``, ``WKT_ALL``, ``WKT2:2015``, ``WKT2:2019``, ``WKT1:GDAL``, ``WKT1:ESRI``, ``PROJJSON``.
+    ``all``, ``default``, ``PROJ``, ``WKT_ALL``, ``WKT2:2015``, ``WKT2:2019``, ``WKT1:GDAL``, ``WKT1:ESRI``, ``PROJJSON``, ``SQL``.
 
     Except ``all`` and ``default``, other formats can be preceded by ``-`` to disable them.
 
@@ -87,6 +91,8 @@ The following control parameters can appear in any order:
     .. note:: Before PROJ 6.3.0, WKT1:GDAL was implicitly calling --boundcrs-to-wgs84.
               This is no longer the case.
 
+    .. note:: When SQL is specified, :option:`--output-id` must be specified.
+
 .. option:: -k crs|operation|datum|ensemble|ellipsoid
 
     When used to query a single object with a AUTHORITY:CODE, determines the (k)ind of the object
@@ -237,6 +243,16 @@ The following control parameters can appear in any order:
     geographic CRS, and if found, wraps those CRS into a BoundCRS object.
     This is mostly to be used for early-binding approaches.
 
+.. option:: --authority name
+
+    Specify the name of the authority into which to restrict looks up for
+    objects, when specifying an object by name or when coordinate operations are
+    computed. The default is to allow all authorities.
+
+    When used with SQL output, this restricts the authorities to which intermediate
+    objects can belong to (the default is EPSG and PROJ). Note that the authority
+    of the :option:`--output-id` option will also be implicitly added.
+
 .. option:: --main-db-path path
 
     Specify the name and path of the database to be used by projinfo. The
@@ -260,6 +276,15 @@ The following control parameters can appear in any order:
     For example, `+proj=utm +zone=31 +datum=WGS84 +type=crs` will be identified
     with a likelihood of 70% to EPSG:32631
 
+.. option:: --dump-db-structure
+
+    .. versionadded:: 8.1
+
+    Outputs the sequence of SQL statements to create a new empty valid auxiliary
+    database. This option can be specified as the only switch of the utility.
+    If also specifying a CRS object and the :option:`--output-id` option, the
+    definition of the object as SQL statements will be appended.
+
 .. option:: --3d
 
     .. versionadded:: 6.3
@@ -271,6 +296,26 @@ The following control parameters can appear in any order:
     automatically promoted to a 3D version, where its vertical axis is the
     ellipsoidal height in metres, using the ellipsoid of the base geodetic CRS.
 
+.. option:: --output-id=AUTH:NAME
+
+    .. versionadded:: 8.1
+
+    Identifier to assign to the object (for SQL output).
+
+    It is strongly recommended that new objects should not be added in common
+    registries, such as ``EPSG``, ``ESRI``, ``IAU``, etc.
+    Users should use a custom authority name instead. If a new object should be
+    added to the official EPSG registry, users are invited to follow the
+    procedure explained at https://epsg.org/dataset-change-requests.html.
+
+    Combined with :option:`--dump-db-structure`, users can create
+    auxiliary databases, instead of directly modifying the main proj.db database.
+    See the :ref:`example how to export to an auxiliary database <projinfo_aux_db_example>`.
+
+    Those auxiliary databases can be specified through
+    :cpp:func:`proj_context_set_database_path` or the :envvar:`PROJ_AUX_DB`
+    environment variable.
+
 .. option:: --c-ify
 
     For developers only. Modify the string output of the utility so that it
@@ -442,6 +487,91 @@ Output:
         }
     }
 
+.. _projinfo_aux_db_example:
+
+4. Exporting the SQL statements to insert a new CRS in an auxiliary database.
+
+.. code-block:: console
+
+        # Get the SQL statements for a custom CRS
+        projinfo "+proj=merc +lat_ts=5 +datum=WGS84 +type=crs +title=my_crs" --output-id HOBU:MY_CRS -o SQL -q > my_crs.sql
+        cat my_crs.sql
+
+        # Initialize an auxiliary database with the schema of the reference database
+        echo ".schema" | sqlite3 /path/to/proj.db | sqlite3 aux.db
+
+        # Append the content of the definition of HOBU:MY_CRS
+        sqlite3 aux.db < my_crs.db
+
+        # Check that everything works OK
+        projinfo --aux-db-path aux.db HOBU:MY_CRS
+
+or more simply:
+
+.. code-block:: console
+
+        # Create an auxiliary database with the definition of a custom CRS.
+        projinfo "+proj=merc +lat_ts=5 +datum=WGS84 +type=crs +title=my_crs" --output-id HOBU:MY_CRS --dump-db-structure | sqlite3 aux.db
+
+        # Check that everything works OK
+        projinfo --aux-db-path aux.db HOBU:MY_CRS
+
+Output:
+
+.. code-block:: sql
+
+    INSERT INTO geodetic_crs VALUES('HOBU','GEODETIC_CRS_MY_CRS','unknown','','geographic 2D','EPSG','6424','EPSG','6326',NULL,0);
+    INSERT INTO usage VALUES('HOBU','USAGE_GEODETIC_CRS_MY_CRS','geodetic_crs','HOBU','GEODETIC_CRS_MY_CRS','PROJ','EXTENT_UNKNOWN','PROJ','SCOPE_UNKNOWN');
+    INSERT INTO conversion VALUES('HOBU','CONVERSION_MY_CRS','unknown','','EPSG','9805','Mercator (variant B)','EPSG','8823','Latitude of 1st standard parallel',5,'EPSG','9122','EPSG','8802','Longitude of natural origin',0,'EPSG','9122','EPSG','8806','False easting',0,'EPSG','9001','EPSG','8807','False northing',0,'EPSG','9001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);
+    INSERT INTO usage VALUES('HOBU','USAGE_CONVERSION_MY_CRS','conversion','HOBU','CONVERSION_MY_CRS','PROJ','EXTENT_UNKNOWN','PROJ','SCOPE_UNKNOWN');
+    INSERT INTO projected_crs VALUES('HOBU','MY_CRS','my_crs','','EPSG','4400','HOBU','GEODETIC_CRS_MY_CRS','HOBU','CONVERSION_MY_CRS',NULL,0);
+    INSERT INTO usage VALUES('HOBU','USAGE_PROJECTED_CRS_MY_CRS','projected_crs','HOBU','MY_CRS','PROJ','EXTENT_UNKNOWN','PROJ','SCOPE_UNKNOWN');
+
+::
+
+    PROJ.4 string:
+    +proj=merc +lat_ts=5 +lon_0=0 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs +type=crs
+
+    WKT2:2019 string:
+    PROJCRS["my_crs",
+        BASEGEOGCRS["unknown",
+            ENSEMBLE["World Geodetic System 1984 ensemble",
+                MEMBER["World Geodetic System 1984 (Transit)"],
+                MEMBER["World Geodetic System 1984 (G730)"],
+                MEMBER["World Geodetic System 1984 (G873)"],
+                MEMBER["World Geodetic System 1984 (G1150)"],
+                MEMBER["World Geodetic System 1984 (G1674)"],
+                MEMBER["World Geodetic System 1984 (G1762)"],
+                ELLIPSOID["WGS 84",6378137,298.257223563,
+                    LENGTHUNIT["metre",1]],
+                ENSEMBLEACCURACY[2.0]],
+            PRIMEM["Greenwich",0,
+                ANGLEUNIT["degree",0.0174532925199433]],
+            ID["HOBU","GEODETIC_CRS_MY_CRS"]],
+        CONVERSION["unknown",
+            METHOD["Mercator (variant B)",
+                ID["EPSG",9805]],
+            PARAMETER["Latitude of 1st standard parallel",5,
+                ANGLEUNIT["degree",0.0174532925199433],
+                ID["EPSG",8823]],
+            PARAMETER["Longitude of natural origin",0,
+                ANGLEUNIT["degree",0.0174532925199433],
+                ID["EPSG",8802]],
+            PARAMETER["False easting",0,
+                LENGTHUNIT["metre",1],
+                ID["EPSG",8806]],
+            PARAMETER["False northing",0,
+                LENGTHUNIT["metre",1],
+                ID["EPSG",8807]]],
+        CS[Cartesian,2],
+            AXIS["(E)",east,
+                ORDER[1],
+                LENGTHUNIT["metre",1]],
+            AXIS["(N)",north,
+                ORDER[2],
+                LENGTHUNIT["metre",1]],
+        ID["HOBU","MY_CRS"]]
+
 
 .. only:: man
 
diff --git a/docs/source/usage/environmentvars.rst b/docs/source/usage/environmentvars.rst
index 0717a9fa..304b9276 100644
--- a/docs/source/usage/environmentvars.rst
+++ b/docs/source/usage/environmentvars.rst
@@ -51,6 +51,18 @@ done by setting the variable with no content::
     :envvar:`PROJ_LIB` to allow for multiple versions of PROJ
     resource files on your system without conflicting.
 
+
+.. envvar:: PROJ_AUX_DB
+
+    .. versionadded:: 8.1.0
+
+    To set the path to one or several auxiliary SQLite3 databases of structure
+    identical to the main ``proj.db`` database and that can contain additional
+    object (CRS, transformation, ...) definitions. If several paths are
+    provided, they must be separated by the colon (:) character on Unix, and
+    on Windows, by the semi-colon (;) character.
+
+
 .. envvar:: PROJ_DEBUG
 
     Set the debug level of PROJ. The default debug level is zero, which results