remove country from place_addressline

The country information can be determined sufficiently from
the country code. We only loose the specific OSM object
behind the address.

Also streamlines the location_area_country table.

diff --git a/sql/functions.sql b/sql/functions.sql
index 9ff9c415922a47066676f5e0d9e957e7c63ff59c..bad4b17c4a61e00938a97a4b6c8385bc203c7666 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -547,7 +547,7 @@ BEGIN
 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
 
   -- Try for a OSM polygon
 -- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
 
   -- Try for a OSM polygon

-  FOR nearcountry IN select country_code from location_area_country where country_code is not null and not isguess and st_covers(geometry, place_centre) limit 1
+  FOR nearcountry IN select country_code from location_area_country where country_code is not null and st_covers(geometry, place_centre) limit 1

   LOOP
     RETURN nearcountry.country_code;
   END LOOP;
   LOOP
     RETURN nearcountry.country_code;
   END LOOP;


@@ -1824,7 +1824,7 @@ BEGIN
 
       -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
       -- Add it to the list of search terms
 
       -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
       -- Add it to the list of search terms

-      IF NOT %REVERSE-ONLY% AND location.rank_search > 4 THEN
+      IF NOT %REVERSE-ONLY% THEN

           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
       END IF;
       INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
       END IF;
       INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address)

diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql
index 3959661e7a4e567a5c983ed25723db5903b46399..20f71584c281c8ef28aa163dc00b97d260adab3b 100644 (file)
--- a/sql/partition-functions.src.sql
+++ b/sql/partition-functions.src.sql
@@ -6,11 +6,9 @@ BEGIN
 -- start
   IF in_partition = -partition- THEN
     FOR r IN 
 -- start
   IF in_partition = -partition- THEN
     FOR r IN 

-      SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid FROM (
-        SELECT * FROM location_area_large_-partition- WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
-        UNION ALL
-        SELECT * FROM location_area_country WHERE ST_Intersects(geometry, feature) and rank_search < maxrank
-      ) as location_area
+      SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(feature, centroid)) as distance, isguess, postcode, centroid
+      FROM location_area_large_-partition-
+      WHERE ST_Intersects(geometry, feature) and rank_search < maxrank

       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
       ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
         ST_Distance(feature, centroid) *
       GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
       ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
         ST_Distance(feature, centroid) *


@@ -64,9 +62,9 @@ BEGIN
     RETURN TRUE;
   END IF;
 
     RETURN TRUE;
   END IF;
 

-  IF in_rank_search <= 4 THEN
-    INSERT INTO location_area_country (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, centroid, geometry)
-      values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry);
+  IF in_rank_search <= 4 and not in_estimate THEN
+    INSERT INTO location_area_country (place_id, country_code, geometry)
+      values (in_place_id, in_country_code, in_geometry);

     RETURN TRUE;
   END IF;
 
     RETURN TRUE;
   END IF;
 

diff --git a/sql/partition-tables.src.sql b/sql/partition-tables.src.sql
index 20dafcd754f3c54888bc58ee1445d507f0954715..f651e7ac09c6f1bab2783b42ba508e40886dff8a 100644 (file)
--- a/sql/partition-tables.src.sql
+++ b/sql/partition-tables.src.sql
@@ -35,9 +35,6 @@ CREATE TABLE search_name_blank (
   );
 
 
   );
 
 

-CREATE TABLE location_area_country () INHERITS (location_area_large) {ts:address-data};
-CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index};
-

 -- start
 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large) {ts:address-data};
 CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) {ts:address-index};
 -- start
 CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large) {ts:address-data};
 CREATE INDEX idx_location_area_large_-partition-_place_id ON location_area_large_-partition- USING BTREE (place_id) {ts:address-index};

diff --git a/sql/tables.sql b/sql/tables.sql
index 949dc6ffe1c8c26dc7e6bb48727b61c4a9443a42..e2960aea0147f0069ea0edc0555dd2ed65615314 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -69,6 +69,15 @@ CREATE TABLE location_area (
 
 CREATE TABLE location_area_large () INHERITS (location_area);
 
 
 CREATE TABLE location_area_large () INHERITS (location_area);
 

+DROP TABLE IF EXISTS location_area_country;
+CREATE TABLE location_area_country (
+  place_id BIGINT,
+  country_code varchar(2),
+  geometry GEOMETRY(Geometry, 4326)
+  ) {ts:address-data};
+CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index};
+
+

 drop table IF EXISTS location_property CASCADE;
 CREATE TABLE location_property (
   place_id BIGINT,
 drop table IF EXISTS location_property CASCADE;
 CREATE TABLE location_property (
   place_id BIGINT,