From: Sarah Hoffmann Date: Thu, 29 Jun 2017 19:39:00 +0000 (+0200) Subject: add indexing of artificial postcodes X-Git-Tag: v3.1.0~88^2~39 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/80ef6cbaab0e503739d98c97cf2d6a80407bfb00?ds=sidebyside add indexing of artificial postcodes --- diff --git a/sql/functions.sql b/sql/functions.sql index 1cda2e98..5e16a175 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -236,6 +236,65 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; + +CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT, + OUT rank_search SMALLINT, OUT rank_address SMALLINT) +AS $$ +DECLARE + part TEXT; +BEGIN + rank_search := 30; + rank_address := 30; + + IF country_code = 'gb' THEN + IF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN + rank_search := 25; + rank_address := 5; + ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN + rank_search := 23; + rank_address := 5; + ELSEIF postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN + rank_search := 21; + rank_address := 5; + END IF; + + ELSEIF country_code = 'sg' THEN + IF postcode ~ '^([0-9]{6})$' THEN + rank_search := 25; + rank_address := 11; + END IF; + + ELSEIF country_code = 'de' THEN + IF postcode ~ '^([0-9]{5})$' THEN + rank_search := 21; + rank_address := 11; + END IF; + + ELSE + -- Guess at the postcode format and coverage (!) + IF postcode ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local + rank_search := 21; + rank_address := 11; + ELSE + -- Does it look splitable into and area and local code? + part := substring(postcode from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$'); + + IF part IS NOT NULL THEN + rank_search := 25; + rank_address := 11; + ELSEIF postcode ~ '^[- :A-Z0-9]{6,}$' THEN + rank_search := 21; + rank_address := 11; + END IF; + END IF; + END IF; + +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + + + CREATE OR REPLACE FUNCTION create_country(src HSTORE, lookup_country_code varchar(2)) RETURNS VOID AS $$ DECLARE @@ -725,51 +784,8 @@ BEGIN NEW.postcode := NEW.address->'postcode'; NEW.name := hstore('ref', NEW.postcode); - IF NEW.country_code = 'gb' THEN - - IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN - NEW.rank_search := 25; - NEW.rank_address := 5; - ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN - NEW.rank_search := 23; - NEW.rank_address := 5; - ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN - NEW.rank_search := 21; - NEW.rank_address := 5; - END IF; - - ELSEIF NEW.country_code = 'sg' THEN - - IF NEW.postcode ~ '^([0-9]{6})$' THEN - NEW.rank_search := 25; - NEW.rank_address := 11; - END IF; - - ELSEIF NEW.country_code = 'de' THEN - - IF NEW.postcode ~ '^([0-9]{5})$' THEN - NEW.rank_search := 21; - NEW.rank_address := 11; - END IF; - - ELSE - -- Guess at the postcode format and coverage (!) - IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local - NEW.rank_search := 21; - NEW.rank_address := 11; - ELSE - -- Does it look splitable into and area and local code? - postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$'); - - IF postcode IS NOT NULL THEN - NEW.rank_search := 25; - NEW.rank_address := 11; - ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN - NEW.rank_search := 21; - NEW.rank_address := 11; - END IF; - END IF; - END IF; + SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode) + INTO NEW.rank_search, NEW.rank_address; ELSEIF NEW.class = 'place' THEN IF NEW.type in ('continent') THEN @@ -1094,7 +1110,42 @@ END; $$ LANGUAGE plpgsql; +-- Trigger for updates of location_postcode +-- +-- Computes the parent object the postcode most likely refers to. +-- This will be the place that determines the address displayed when +-- searching for this postcode. +CREATE OR REPLACE FUNCTION postcode_update() RETURNS +TRIGGER + AS $$ +DECLARE + partition SMALLINT; + location RECORD; +BEGIN + IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN + RETURN NEW; + END IF; + + NEW.indexed_date = now(); + partition := get_partition(NEW.country_code); + + SELECT * FROM get_postcode_rank(NEW.country_code, NEW.postcode) + INTO NEW.rank_search, NEW.rank_address; + + NEW.parent_place_id = 0; + FOR location IN + SELECT place_id + FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[]) + WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1 + LOOP + NEW.parent_place_id = location.place_id; + END LOOP; + + RETURN NEW; +END; +$$ +LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION placex_update() RETURNS TRIGGER diff --git a/sql/indices.src.sql b/sql/indices.src.sql index bbd5a76a..9de2c97f 100644 --- a/sql/indices.src.sql +++ b/sql/indices.src.sql @@ -26,3 +26,7 @@ CREATE UNIQUE INDEX idx_place_osm_unique on place using btree(osm_id,osm_type,cl CREATE INDEX idx_gb_postcode_postcode ON gb_postcode USING BTREE (postcode) {ts:search-index}; + +CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {ts:search-index}; +CREATE INDEX idx_postcode_parent_id ON location_postcode USING BTREE (parent_place_id) {ts:search-index} where parent_place_id IS NOT NULL; +CREATE INDEX idx_postcode_postcode ON location_postcode USING BTREE (postcode, country_code) {ts:search-index}; diff --git a/sql/tables.sql b/sql/tables.sql index 07b0ada5..334fcbc0 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -197,10 +197,12 @@ CREATE TRIGGER place_before_delete BEFORE DELETE ON place CREATE TRIGGER place_before_insert BEFORE INSERT ON place FOR EACH ROW EXECUTE PROCEDURE place_insert(); +-- Table for synthetic postcodes. DROP TABLE IF EXISTS location_postcode; CREATE TABLE location_postcode ( place_id BIGINT, parent_place_id BIGINT, + rank_search SMALLINT, rank_address SMALLINT, indexed_status SMALLINT, indexed_date TIMESTAMP, @@ -208,6 +210,10 @@ CREATE TABLE location_postcode ( postcode TEXT, geometry GEOMETRY(Geometry, 4326) ); +CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index}; + +CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode + FOR EACH ROW EXECUTE PROCEDURE postcode_update(); DROP TABLE IF EXISTS import_polygon_error; CREATE TABLE import_polygon_error ( diff --git a/utils/setup.php b/utils/setup.php index 54ddf082..a9597da8 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -500,7 +500,7 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) { $sSQL = "INSERT INTO location_postcode"; $sSQL .= " (place_id, indexed_status, country_code, postcode, geometry) "; $sSQL .= "SELECT nextval('seq_place'), 1, country_code,"; - $sSQL .= " lower(trim (both ' ' from address->'postcode')) as pc,"; + $sSQL .= " upper(trim (both ' ' from address->'postcode')) as pc,"; $sSQL .= " ST_Centroid(ST_Collect(ST_Centroid(geometry)))"; $sSQL .= " FROM placex"; $sSQL .= " WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'"; @@ -523,6 +523,10 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) { if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); } + + echo "Indexing postcodes....\n"; + $sSQL = 'UPDATE location_postcode SET indexed_status = 0'; + if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); } if ($aCMDResult['osmosis-init']) {