simplify name matching between boundary and place node

Instead of normalising the names simply compare them in lower
case. This removes the dependency on the tokenizer for
linking boundaries and nodes. When looking up the linked places
by place type also allow that one name is simply contained in the
other. This catches the frequent case where one of the names has
an addendum (e.g. Newport vs. City of Newport).

Drops the special index for the name lookup and insted relies
on a slightly extended version of the geometry index used for
reverse lookup. Saves around 100MB on a planet.

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 6998224e7b851893e590d9f63ca47ca6acd1b18e..812bc79ff5c44314858af1ae350ec7d2c8cd0597 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -169,7 +169,7 @@ BEGIN
   END IF;
 
   IF bnd.name ? 'name' THEN
   END IF;
 
   IF bnd.name ? 'name' THEN

-    bnd_name := make_standard_name(bnd.name->'name');
+    bnd_name := lower(bnd.name->'name');

     IF bnd_name = '' THEN
       bnd_name := NULL;
     END IF;
     IF bnd_name = '' THEN
       bnd_name := NULL;
     END IF;


@@ -180,12 +180,14 @@ BEGIN
   IF bnd.extratags ? 'place' and bnd_name is not null THEN
     FOR linked_placex IN
       SELECT * FROM placex
   IF bnd.extratags ? 'place' and bnd_name is not null THEN
     FOR linked_placex IN
       SELECT * FROM placex

-      WHERE make_standard_name(name->'name') = bnd_name
+      WHERE (position(lower(name->'name') in bnd_name) > 0
+             OR position(bnd_name in lower(name->'name')) > 0)

         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'
         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index
         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'
         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index

-        AND _st_covers(bnd.geometry, placex.geometry)
+        AND placex.type != 'postcode'
+        AND ST_Covers(bnd.geometry, placex.geometry)

     LOOP
       {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
     LOOP
       {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;


@@ -201,7 +203,7 @@ BEGIN
         AND placex.linked_place_id is null
         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)
         AND placex.linked_place_id is null
         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)

-      ORDER BY make_standard_name(name->'name') = bnd_name desc
+      ORDER BY lower(name->'name') = bnd_name desc

     LOOP
       {% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
     LOOP
       {% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;


@@ -213,7 +215,7 @@ BEGIN
     {% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %}
     FOR linked_placex IN
       SELECT placex.* from placex
     {% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %}
     FOR linked_placex IN
       SELECT placex.* from placex

-      WHERE make_standard_name(name->'name') = bnd_name
+      WHERE lower(name->'name') = bnd_name

         AND ((bnd.rank_address > 0
               and bnd.rank_address = (compute_place_rank(placex.country_code,
                                                          'N', placex.class,
         AND ((bnd.rank_address > 0
               and bnd.rank_address = (compute_place_rank(placex.country_code,
                                                          'N', placex.class,


@@ -221,9 +223,11 @@ BEGIN
                                                          false, placex.postcode)).address_rank)
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'
                                                          false, placex.postcode)).address_rank)
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'

+        AND placex.class = 'place'

         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index
         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index

-        AND _st_covers(bnd.geometry, placex.geometry)
+        AND placex.type != 'postcode'
+        AND ST_Covers(bnd.geometry, placex.geometry)

     LOOP
       {% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
     LOOP
       {% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index c121a9631b84b5d758dc552f9d45e84695bdc8be..a6f7cf95fcb6c7e0346ee0cdc8ca54fe2a77be81 100644 (file)
--- a/lib-sql/indices.sql
+++ b/lib-sql/indices.sql
@@ -23,12 +23,6 @@ CREATE INDEX {{sql.if_index_not_exists}} idx_placex_geometry_reverse_lookupPolyg
     AND rank_address between 4 and 25 AND type != 'postcode'
     AND name is not null AND indexed_status = 0 AND linked_place_id is null;
 
     AND rank_address between 4 and 25 AND type != 'postcode'
     AND name is not null AND indexed_status = 0 AND linked_place_id is null;
 

-CREATE INDEX {{sql.if_index_not_exists}} idx_placex_geometry_reverse_placeNode
-  ON placex USING gist (geometry) {{db.tablespace.search_index}}
-  WHERE osm_type = 'N' AND rank_search between 5 and 25
-    AND class = 'place' AND type != 'postcode'
-    AND name is not null AND indexed_status = 0 AND linked_place_id is null;
-

 CREATE INDEX {{sql.if_index_not_exists}} idx_osmline_parent_place_id
   ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}};
 
 CREATE INDEX {{sql.if_index_not_exists}} idx_osmline_parent_place_id
   ON location_property_osmline USING BTREE (parent_place_id) {{db.tablespace.search_index}};
 

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 329eb7a1ab9491f8fb063b92747dd0abfcf9c2d2..aa213dbaccb4ebd49436fc68853ed0ab8d9c6789 100644 (file)
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -184,7 +184,10 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {{db.tabl
 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {{db.tablespace.address_index}} WHERE linked_place_id IS NOT NULL;
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {{db.tablespace.address_index}} WHERE linked_place_id IS NOT NULL;
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};

-CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {{db.tablespace.address_index}} WHERE osm_type='N' and rank_search < 26;
+CREATE INDEX idx_placex_geometry_placenode ON placex
+  USING GIST (geometry) {{db.tablespace.search_index}}
+  WHERE osm_type = 'N' and rank_search < 26
+        and class = 'place' and type != 'postcode' and linked_place_id is null;

 CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
 
 DROP SEQUENCE IF EXISTS seq_place;
 CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
 
 DROP SEQUENCE IF EXISTS seq_place;