From 63857c92b271bbcd10df0a032304982011acb2a9 Mon Sep 17 00:00:00 2001
From: Even Rouault <even.rouault@spatialys.com>
Date: Tue, 10 Sep 2019 20:21:36 +0200
Subject: Coordinate transformation: improve transformations from/to WGS84
 (Gxxxx)

Currently very few transformations from/to WGS84 (Gxxxx) are registered
in the EPSG database, and there isn't even transformations between WGS84 EPSG:4326
and those ones. Consequently transformations to those realizations often
ended up as no-operation, whereas going through WGS84 EPSG:4326 will bring
more meaningful results. So register those EPSG:4326<-->WGS 84 (Gxxx)
null transformations, and when having WGS 84 (Gxxx) as source/target,
consider EPSG:4326 as an intermediate.
This change has no effect on the existing direct transformations
from/to WGS 84 (Gxxx).
---
 data/sql/proj_db_table_defs.sql | 13 +++++++++++++
 1 file changed, 13 insertions(+)

(limited to 'data/sql/proj_db_table_defs.sql')

diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index 255de827..6b38e736 100644
--- a/data/sql/proj_db_table_defs.sql
+++ b/data/sql/proj_db_table_defs.sql
@@ -126,6 +126,19 @@ FOR EACH ROW BEGIN
         WHERE EXISTS(SELECT 1 FROM area WHERE area.auth_name = NEW.area_of_use_auth_name AND area.code = NEW.area_of_use_code AND area.deprecated != 0) AND NEW.deprecated = 0;
 END;
 
+-- indicates that if there is no transformation from/into (src_auth_name, src_code),
+-- a research going through (hub_auth_name, hub_code) should be made
+CREATE TABLE geodetic_datum_preferred_hub(
+    src_auth_name TEXT NOT NULL CHECK (length(src_auth_name) >= 1),
+    src_code TEXT NOT NULL CHECK (length(src_code) >= 1),
+    hub_auth_name TEXT NOT NULL CHECK (length(hub_auth_name) >= 1),
+    hub_code TEXT NOT NULL CHECK (length(hub_code) >= 1),
+
+    CONSTRAINT unique_geodetic_datum_preferred_hub UNIQUE (src_auth_name, src_code, hub_auth_name, hub_code),
+    CONSTRAINT fk_geodetic_datum_preferred_hub_src FOREIGN KEY (src_auth_name, src_code) REFERENCES geodetic_datum(auth_name, code),
+    CONSTRAINT fk_geodetic_datum_preferred_hub_src FOREIGN KEY (hub_auth_name, hub_code) REFERENCES geodetic_datum(auth_name, code)
+);
+
 CREATE TABLE vertical_datum (
     auth_name TEXT NOT NULL CHECK (length(auth_name) >= 1),
     code TEXT NOT NULL CHECK (length(code) >= 1),
-- 
cgit v1.2.3

