When matching conversions from ESRI db, keep searching if we find

a deprecated conversion for a non-deprecated CRS

2 files changed, 27 insertions, 13 deletions

diff --git a/data/sql/esri.sql b/data/sql/esri.sql
index 3506ce63..6323f7ef 100644
--- a/data/sql/esri.sql
+++ b/data/sql/esri.sql
@@ -7001,29 +7001,29 @@ INSERT INTO "projected_crs" VALUES('ESRI','21019','GSK-2011_Gauss-Kruger_CM_111E
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21019_USAGE','projected_crs','ESRI','21019','EPSG','1778','EPSG','1024');
 INSERT INTO "projected_crs" VALUES('ESRI','21020','GSK-2011_Gauss-Kruger_CM_117E',NULL,'EPSG','4400','EPSG','7683','EPSG','16320',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21020_USAGE','projected_crs','ESRI','21020','EPSG','1779','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21021','GSK-2011_Gauss-Kruger_CM_123E',NULL,'EPSG','4400','EPSG','7683','EPSG','16171',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21021','GSK-2011_Gauss-Kruger_CM_123E',NULL,'EPSG','4400','EPSG','7683','EPSG','16321',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21021_USAGE','projected_crs','ESRI','21021','EPSG','1780','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21022','GSK-2011_Gauss-Kruger_CM_129E',NULL,'EPSG','4400','EPSG','7683','EPSG','16173',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21022','GSK-2011_Gauss-Kruger_CM_129E',NULL,'EPSG','4400','EPSG','7683','EPSG','16322',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21022_USAGE','projected_crs','ESRI','21022','EPSG','1781','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21023','GSK-2011_Gauss-Kruger_CM_135E',NULL,'EPSG','4400','EPSG','7683','EPSG','16175',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21023','GSK-2011_Gauss-Kruger_CM_135E',NULL,'EPSG','4400','EPSG','7683','EPSG','16323',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21023_USAGE','projected_crs','ESRI','21023','EPSG','1782','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21024','GSK-2011_Gauss-Kruger_CM_141E',NULL,'EPSG','4400','EPSG','7683','EPSG','16177',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21024','GSK-2011_Gauss-Kruger_CM_141E',NULL,'EPSG','4400','EPSG','7683','EPSG','16324',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21024_USAGE','projected_crs','ESRI','21024','EPSG','1783','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21025','GSK-2011_Gauss-Kruger_CM_147E',NULL,'EPSG','4400','EPSG','7683','EPSG','16179',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21025','GSK-2011_Gauss-Kruger_CM_147E',NULL,'EPSG','4400','EPSG','7683','EPSG','16325',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21025_USAGE','projected_crs','ESRI','21025','EPSG','1784','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21026','GSK-2011_Gauss-Kruger_CM_153E',NULL,'EPSG','4400','EPSG','7683','EPSG','16181',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21026','GSK-2011_Gauss-Kruger_CM_153E',NULL,'EPSG','4400','EPSG','7683','EPSG','16326',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21026_USAGE','projected_crs','ESRI','21026','EPSG','1785','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21027','GSK-2011_Gauss-Kruger_CM_159E',NULL,'EPSG','4400','EPSG','7683','EPSG','16183',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21027','GSK-2011_Gauss-Kruger_CM_159E',NULL,'EPSG','4400','EPSG','7683','EPSG','16327',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21027_USAGE','projected_crs','ESRI','21027','EPSG','1786','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21028','GSK-2011_Gauss-Kruger_CM_165E',NULL,'EPSG','4400','EPSG','7683','EPSG','16185',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21028','GSK-2011_Gauss-Kruger_CM_165E',NULL,'EPSG','4400','EPSG','7683','EPSG','16328',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21028_USAGE','projected_crs','ESRI','21028','EPSG','1787','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21029','GSK-2011_Gauss-Kruger_CM_171E',NULL,'EPSG','4400','EPSG','7683','EPSG','16187',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21029','GSK-2011_Gauss-Kruger_CM_171E',NULL,'EPSG','4400','EPSG','7683','EPSG','16329',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21029_USAGE','projected_crs','ESRI','21029','EPSG','1788','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21030','GSK-2011_Gauss-Kruger_CM_177E',NULL,'EPSG','4400','EPSG','7683','EPSG','16189',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21030','GSK-2011_Gauss-Kruger_CM_177E',NULL,'EPSG','4400','EPSG','7683','EPSG','16330',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21030_USAGE','projected_crs','ESRI','21030','EPSG','1789','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21031','GSK-2011_Gauss-Kruger_CM_177W',NULL,'EPSG','4400','EPSG','7683','EPSG','16191',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21031','GSK-2011_Gauss-Kruger_CM_177W',NULL,'EPSG','4400','EPSG','7683','EPSG','16331',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21031_USAGE','projected_crs','ESRI','21031','EPSG','1790','EPSG','1024');
-INSERT INTO "projected_crs" VALUES('ESRI','21032','GSK-2011_Gauss-Kruger_CM_171W',NULL,'EPSG','4400','EPSG','7683','EPSG','16193',NULL,0);
+INSERT INTO "projected_crs" VALUES('ESRI','21032','GSK-2011_Gauss-Kruger_CM_171W',NULL,'EPSG','4400','EPSG','7683','EPSG','16332',NULL,0);
 INSERT INTO "usage" VALUES('ESRI', 'PCRS_21032_USAGE','projected_crs','ESRI','21032','EPSG','1791','EPSG','1024');
 INSERT INTO alias_name VALUES('projected_crs','EPSG','21035','Arc_1960_UTM_Zone_35S','ESRI');
 INSERT INTO alias_name VALUES('projected_crs','EPSG','21036','Arc_1960_UTM_Zone_36S','ESRI');
diff --git a/scripts/build_db_from_esri.py b/scripts/build_db_from_esri.py
index 82bb196a..5637c995 100755
--- a/scripts/build_db_from_esri.py
+++ b/scripts/build_db_from_esri.py
@@ -1027,7 +1027,7 @@ def import_projcs():
                         conv_name = esri_name + " (Gauss Kruger)"
 
                     cursor.execute(
-                        """SELECT code FROM conversion WHERE auth_name = 'EPSG' AND
+                        """SELECT code, deprecated FROM conversion WHERE auth_name = 'EPSG' AND
                         method_code = '9807' AND
                         param1_code = '8801' AND param1_value = ? AND param1_uom_code = ? AND
                         param2_code = '8802' AND param2_value = ? AND param2_uom_code = ? AND
@@ -1046,6 +1046,20 @@ def import_projcs():
                     if conv_name == 'unnamed' and src_row:
                         conv_auth_name = 'EPSG'
                         conv_code = src_row[0]
+                        conv_is_deprecated = bool(src_row[1])
+                        while not deprecated and conv_is_deprecated:
+                            # if we found a deprecated conversion but the CRS isn't deprecated, keep looking...
+                            src_row = cursor.fetchone()
+                            if not src_row:
+                                break
+
+                            conv_code = src_row[0]
+                            conv_is_deprecated = bool(src_row[1])
+
+                        if conv_is_deprecated and not deprecated:
+                            # if conversion is marked as deprecated, we have to deprecate the CRS also
+                            print('Flagging ESRI:{} ({}) as deprecated because conversion EPSG:{} is deprecated'.format(code, esri_name, conv_code))
+                            deprecated = True
                     else:
                         conv_auth_name = 'ESRI'
                         conv_code = code