From 2dd7f0156a84ef07b808244faa2d42c384e9da32 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sun, 4 Jun 2017 14:34:01 +0200 Subject: [PATCH] use "char" type when refering to osm_type column When using an explicit cast to char(1) when refering to the osm_type column postgres won't use the index in some cases. Observed only on postgres 9.5 from the original Postgres reporsitories. Fixes #741. --- sql/functions.sql | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/sql/functions.sql b/sql/functions.sql index b60ef97f..6cc42803 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1491,7 +1491,7 @@ BEGIN IF relation_members IS NOT NULL THEN FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::"char" and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP -- If we don't already have one use this as the centre point of the geometry @@ -1522,7 +1522,7 @@ BEGIN FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::"char" and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york' @@ -1571,7 +1571,7 @@ BEGIN make_standard_name(name->'name') = make_standard_name(NEW.name->'name') AND placex.rank_address = NEW.rank_address AND placex.place_id != NEW.place_id - AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26 + AND placex.osm_type = 'N' AND placex.rank_search < 26 AND st_covers(NEW.geometry, placex.geometry) LOOP @@ -1621,7 +1621,7 @@ BEGIN -- Still null? how about looking it up by the node id IF NEW.importance IS NULL THEN - select language||':'||title,importance from wikipedia_article where osm_type = 'N'::char(1) and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; + select language||':'||title,importance from wikipedia_article where osm_type = 'N' and osm_id = linked_node_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance; END IF; END IF; -- 2.39.5