From 709ab1bb038efb92930a7b4c3bb3db6689b3bd8b Mon Sep 17 00:00:00 2001 From: Brian Quinion Date: Sun, 29 Apr 2012 16:40:44 +0100 Subject: [PATCH] more name checks in point and poly mergeing, and put the index creation earlier in the process --- sql/functions.sql | 9 +++++++-- sql/indices.src.sql | 1 - sql/tables.sql | 1 + 3 files changed, 8 insertions(+), 3 deletions(-) diff --git a/sql/functions.sql b/sql/functions.sql index 806e1566..5f7fc310 100644 --- 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; - 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)); @@ -1531,7 +1535,8 @@ BEGIN 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 diff --git a/sql/indices.src.sql b/sql/indices.src.sql index 57a6cdd7..d6a1f08f 100644 --- 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_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); diff --git a/sql/tables.sql b/sql/tables.sql index 51690ae0..9baa2da0 100644 --- 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_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); -- 2.39.5