more name checks in point and poly mergeing, and put the index creation earlier in...

diff --git a/sql/functions.sql b/sql/functions.sql
index 806e1566f09fd1c9d26a2c755a4eab87d112e661..5f7fc3107da71f6c47af3efd28e123ff0c8bb32f 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1509,7 +1509,11 @@ BEGIN
           select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
             and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
 
           select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
             and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
 

-          IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search THEN
+          -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+          -- But that can be fixed by explicitly setting the label in the data
+          IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
+            AND NEW.rank_search = linkedPlacex.rank_search THEN
+

             -- If we don't already have one use this as the centre point of the geometry
             IF NEW.centroid IS NULL THEN
               NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
             -- If we don't already have one use this as the centre point of the geometry
             IF NEW.centroid IS NULL THEN
               NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));


@@ -1531,7 +1535,8 @@ BEGIN
       END IF;
 
       -- not found one yet? how about doing a name search
       END IF;
 
       -- not found one yet? how about doing a name search

-      IF NEW.centroid IS NULL THEN
+      IF NEW.centroid IS NULL AND NEW.name->'name' is not null and make_standard_name(NEW.name->'name') != '' THEN
+

         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
           AND placex.rank_search = NEW.rank_search
         FOR linkedPlacex IN select placex.* from placex WHERE
           make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
           AND placex.rank_search = NEW.rank_search

diff --git a/sql/indices.src.sql b/sql/indices.src.sql
index 57a6cdd749d3330e04761cf70f82eb3fb8b0f583..d6a1f08f2aae886cb4b6e317d77a0a69c92331c3 100644 (file)
--- a/sql/indices.src.sql
+++ b/sql/indices.src.sql
@@ -15,7 +15,6 @@ CREATE INDEX idx_placex_pendingsector ON placex USING BTREE (rank_search,geometr
 CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL;
 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);
 CREATE INDEX idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) where parent_place_id IS NOT NULL;
 CREATE INDEX idx_placex_interpolation ON placex USING BTREE (geometry_sector) where indexed_status > 0 and class='place' and type='houses';
 CREATE INDEX idx_placex_sector ON placex USING BTREE (geometry_sector,rank_address,osm_type,osm_id);

-CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26;

 CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id);
 
 CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid);
 CREATE INDEX idx_location_area_country_place_id ON location_area_country USING BTREE (place_id);
 
 CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid);

diff --git a/sql/tables.sql b/sql/tables.sql
index 51690ae079ad101117e0015967b31e40c48032cb..9baa2da0f1c15666fb26c2f76e942b885ed040cc 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -219,6 +219,7 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id);
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector);
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id);
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector);
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);

+CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26;

 
 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
 
 
 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);