From: Sarah Hoffmann <lonvia@denofr.de>
Date: Mon, 8 Feb 2021 10:48:45 +0000 (+0100)
Subject: move postcode table setup to sql/
X-Git-Tag: v3.7.0~38^2~21
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/7dfe645b5f2fcfc320264baad73f52c8003e857f

move postcode table setup to sql/

Also moves the call to the setup from the setup-db
step to the calculate-postcodes step. The tables also need
no longer be accessible by the webservice.
---

diff --git a/data/gb_postcode_table.sql b/data/gb_postcode_table.sql
deleted file mode 100644
index bee8a964..00000000
--- a/data/gb_postcode_table.sql
+++ /dev/null
@@ -1,26 +0,0 @@
--- This data contains Ordnance Survey data © Crown copyright and database right 2010. 
--- Code-Point Open contains Royal Mail data © Royal Mail copyright and database right 2010.
--- OS data may be used under the terms of the OS OpenData licence:
--- http://www.ordnancesurvey.co.uk/oswebsite/opendata/licence/docs/licence.pdf
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-SET escape_string_warning = off;
-
-SET search_path = public, pg_catalog;
-
-SET default_tablespace = '';
-
-SET default_with_oids = false;
-
-CREATE TABLE gb_postcode (
-    id integer,
-    postcode character varying(9),
-    geometry geometry,
-    CONSTRAINT enforce_dims_geometry CHECK ((st_ndims(geometry) = 2)),
-    CONSTRAINT enforce_srid_geometry CHECK ((st_srid(geometry) = 4326))
-);
-
diff --git a/data/us_postcode_table.sql b/data/us_postcode_table.sql
deleted file mode 100644
index 9958916c..00000000
--- a/data/us_postcode_table.sql
+++ /dev/null
@@ -1,16 +0,0 @@
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-
-SET search_path = public, pg_catalog;
-
-SET default_tablespace = '';
-
-SET default_with_oids = false;
-
-CREATE TABLE us_postcode (
-    postcode text,
-    x double precision,
-    y double precision
-);
diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php
index dda49160..4e9daaf8 100755
--- a/lib/setup/SetupClass.php
+++ b/lib/setup/SetupClass.php
@@ -173,22 +173,6 @@ class SetupFunctions
         }
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_name.sql');
         $this->pgsqlRunScriptFile(CONST_DataDir.'/data/country_osm_grid.sql.gz');
-        $this->pgsqlRunScriptFile(CONST_DataDir.'/data/gb_postcode_table.sql');
-        $this->pgsqlRunScriptFile(CONST_DataDir.'/data/us_postcode_table.sql');
-
-        $sPostcodeFilename = CONST_InstallDir.'/gb_postcode_data.sql.gz';
-        if (file_exists($sPostcodeFilename)) {
-            $this->pgsqlRunScriptFile($sPostcodeFilename);
-        } else {
-            warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
-        }
-
-        $sPostcodeFilename = CONST_InstallDir.'/us_postcode_data.sql.gz';
-        if (file_exists($sPostcodeFilename)) {
-            $this->pgsqlRunScriptFile($sPostcodeFilename);
-        } else {
-            warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
-        }
 
         if ($this->bNoPartitions) {
             $this->pgsqlRunScript('update country_name set partition = 0');
@@ -521,6 +505,23 @@ class SetupFunctions
     public function calculatePostcodes($bCMDResultAll)
     {
         info('Calculate Postcodes');
+        $this->pgsqlRunScriptFile(CONST_DataDir.'/sql/postcode_tables.sql');
+
+        $sPostcodeFilename = CONST_InstallDir.'/gb_postcode_data.sql.gz';
+        if (file_exists($sPostcodeFilename)) {
+            $this->pgsqlRunScriptFile($sPostcodeFilename);
+        } else {
+            warn('optional external GB postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
+        }
+
+        $sPostcodeFilename = CONST_InstallDir.'/us_postcode_data.sql.gz';
+        if (file_exists($sPostcodeFilename)) {
+            $this->pgsqlRunScriptFile($sPostcodeFilename);
+        } else {
+            warn('optional external US postcode table file ('.$sPostcodeFilename.') not found. Skipping.');
+        }
+
+
         $this->db()->exec('TRUNCATE location_postcode');
 
         $sSQL  = 'INSERT INTO location_postcode';
diff --git a/sql/postcode_tables.sql b/sql/postcode_tables.sql
new file mode 100644
index 00000000..c445d6af
--- /dev/null
+++ b/sql/postcode_tables.sql
@@ -0,0 +1,15 @@
+DROP TABLE IF EXISTS gb_postcode;
+CREATE TABLE gb_postcode (
+    id integer,
+    postcode character varying(9),
+    geometry geometry,
+    CONSTRAINT enforce_dims_geometry CHECK ((st_ndims(geometry) = 2)),
+    CONSTRAINT enforce_srid_geometry CHECK ((st_srid(geometry) = 4326))
+);
+
+DROP TABLE IF EXISTS us_postcode;
+CREATE TABLE us_postcode (
+    postcode text,
+    x double precision,
+    y double precision
+);
diff --git a/sql/tables.sql b/sql/tables.sql
index 8647e304..d15e42c4 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -35,8 +35,6 @@ GRANT UPDATE ON new_query_log TO "{www-user}" ;
 GRANT SELECT ON new_query_log TO "{www-user}" ;
 
 GRANT SELECT ON TABLE country_name TO "{www-user}";
-GRANT SELECT ON TABLE gb_postcode TO "{www-user}";
-GRANT SELECT ON TABLE us_postcode TO "{www-user}";
 
 drop table IF EXISTS word;
 CREATE TABLE word (