From: Sarah Hoffmann Date: Fri, 24 Jan 2020 20:55:19 +0000 (+0100) Subject: Merge pull request #1647 from lonvia/split-out-linking X-Git-Tag: v3.5.0~96 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/9abb96fa6b87ba2086b0bf32eed0310690931cfe?hp=4856f56d61e312bf946aa527cc79fe7034441cb3 Merge pull request #1647 from lonvia/split-out-linking Split up placex update trigger code --- diff --git a/lib/setup/SetupClass.php b/lib/setup/SetupClass.php index b96b186b..bf44e04b 100755 --- a/lib/setup/SetupClass.php +++ b/lib/setup/SetupClass.php @@ -235,6 +235,16 @@ class SetupFunctions $oAlParser->createTable($this->oDB, 'address_levels'); } + public function createTableTriggers() + { + info('Create Tables'); + + $sTemplate = file_get_contents(CONST_BasePath.'/sql/table-triggers.sql'); + $sTemplate = $this->replaceSqlPatterns($sTemplate); + + $this->pgsqlRunScript($sTemplate, false); + } + public function createPartitionTables() { info('Create Partition Tables'); @@ -654,9 +664,15 @@ class SetupFunctions $sTemplate .= file_get_contents($sBasePath.'importance.sql'); $sTemplate .= file_get_contents($sBasePath.'address_lookup.sql'); $sTemplate .= file_get_contents($sBasePath.'interpolation.sql'); - $sTemplate .= file_get_contents($sBasePath.'place_triggers.sql'); - $sTemplate .= file_get_contents($sBasePath.'placex_triggers.sql'); - $sTemplate .= file_get_contents($sBasePath.'postcode_triggers.sql'); + if ($this->oDB->tableExists('place')) { + $sTemplate .= file_get_contents($sBasePath.'place_triggers.sql'); + } + if ($this->oDB->tableExists('placex')) { + $sTemplate .= file_get_contents($sBasePath.'placex_triggers.sql'); + } + if ($this->oDB->tableExists('location_postcode')) { + $sTemplate .= file_get_contents($sBasePath.'postcode_triggers.sql'); + } $sTemplate = str_replace('{modulepath}', $this->sModulePath, $sTemplate); if ($this->bEnableDiffUpdates) { $sTemplate = str_replace('RETURN NEW; -- %DIFFUPDATES%', '--', $sTemplate); diff --git a/sql/functions/aux_property.sql b/sql/functions/aux_property.sql index b622f78b..cee5cfb2 100644 --- a/sql/functions/aux_property.sql +++ b/sql/functions/aux_property.sql @@ -12,7 +12,7 @@ DECLARE out_partition INTEGER; out_parent_place_id BIGINT; location RECORD; - address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; out_postcode TEXT; BEGIN @@ -21,11 +21,10 @@ BEGIN out_partition := get_partition(in_countrycode); out_parent_place_id := null; - address_street_word_id := get_name_id(make_standard_name(in_street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP - out_parent_place_id := location.place_id; - END LOOP; + address_street_word_ids := word_ids_from_name(in_street); + IF address_street_word_ids IS NOT NULL THEN + out_parent_place_id := getNearestNamedRoadPlaceId(out_partition, place_centroid, + address_street_word_ids); END IF; IF out_parent_place_id IS NULL THEN diff --git a/sql/functions/interpolation.sql b/sql/functions/interpolation.sql index 8e6751b4..a797cad3 100644 --- a/sql/functions/interpolation.sql +++ b/sql/functions/interpolation.sql @@ -14,7 +14,7 @@ LANGUAGE plpgsql IMMUTABLE; -- find the parent road of the cut road parts CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, - place TEXT, partition INTEGER, + place TEXT, partition SMALLINT, centroid GEOMETRY, geom GEOMETRY) RETURNS BIGINT AS $$ @@ -22,7 +22,6 @@ DECLARE addr_street TEXT; addr_place TEXT; parent_place_id BIGINT; - address_street_word_ids INTEGER[]; waynodes BIGINT[]; @@ -44,23 +43,8 @@ BEGIN END LOOP; END IF; - IF addr_street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT place_id from getNearestNamedRoadFeature(partition, centroid, address_street_word_ids) LOOP - parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; - - IF parent_place_id IS NULL AND addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT place_id from getNearestNamedPlaceFeature(partition, centroid, address_street_word_ids) LOOP - parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; + parent_place_id := find_parent_for_address(addr_street, addr_place, + partition, centroid); IF parent_place_id is null THEN FOR location IN SELECT place_id FROM placex diff --git a/sql/functions/normalization.sql b/sql/functions/normalization.sql index 53a81588..66d0214a 100644 --- a/sql/functions/normalization.sql +++ b/sql/functions/normalization.sql @@ -201,33 +201,16 @@ END; $$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_word_id(lookup_word TEXT) - RETURNS INTEGER - AS $$ -DECLARE - lookup_token TEXT; - return_word_id INTEGER; -BEGIN - lookup_token := trim(lookup_word); - SELECT min(word_id) FROM word - WHERE word_token = lookup_token and class is null and type is null - INTO return_word_id; - RETURN return_word_id; -END; -$$ -LANGUAGE plpgsql STABLE; - - -CREATE OR REPLACE FUNCTION get_name_id(lookup_word TEXT) - RETURNS INTEGER +-- Normalize a string and lookup its word ids (partial words). +CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT) + RETURNS INTEGER[] AS $$ DECLARE lookup_token TEXT; - return_word_id INTEGER; + return_word_id INTEGER[]; BEGIN - lookup_token := ' '||trim(lookup_word); - SELECT min(word_id) FROM word + lookup_token := make_standard_name(lookup_word); + SELECT array_agg(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null INTO return_word_id; RETURN return_word_id; @@ -236,21 +219,22 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT) +-- Normalize a string and look up its name ids (full words). +CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT) RETURNS INTEGER[] AS $$ DECLARE lookup_token TEXT; return_word_ids INTEGER[]; BEGIN - lookup_token := ' '||trim(lookup_word); + lookup_token := ' '|| make_standard_name(lookup_word); SELECT array_agg(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null INTO return_word_ids; RETURN return_word_ids; END; $$ -LANGUAGE plpgsql STABLE; +LANGUAGE plpgsql STABLE STRICT; CREATE OR REPLACE FUNCTION create_country(src HSTORE, country_code varchar(2)) diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index 03a00805..9803e815 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -1,5 +1,243 @@ -- Trigger functions for the placex table. +CREATE OR REPLACE FUNCTION get_rel_node_members(members TEXT[], memberLabels TEXT[]) + RETURNS SETOF BIGINT + AS $$ +DECLARE + i INTEGER; +BEGIN + FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP + IF members[i+1] = ANY(memberLabels) + AND upper(substring(members[i], 1, 1))::char(1) = 'N' + THEN + RETURN NEXT substring(members[i], 2)::bigint; + END IF; + END LOOP; + + RETURN; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + +-- copy 'name' to or from the default language (if there is a default language) +CREATE OR REPLACE FUNCTION add_default_place_name(country_code VARCHAR(2), + INOUT name HSTORE) + AS $$ +DECLARE + default_language VARCHAR(10); +BEGIN + IF name is not null AND array_upper(akeys(name),1) > 1 THEN + default_language := get_country_language_code(country_code); + IF default_language IS NOT NULL THEN + IF name ? 'name' AND NOT name ? ('name:'||default_language) THEN + name := name || hstore(('name:'||default_language), (name -> 'name')); + ELSEIF name ? ('name:'||default_language) AND NOT name ? 'name' THEN + name := name || hstore('name', (name -> ('name:'||default_language))); + END IF; + END IF; + END IF; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + +-- Find the parent road of a POI. +-- +-- \returns Place ID of parent object or NULL if none +-- +-- Copy data from linked items (POIs on ways, addr:street links, relations). +-- +CREATE OR REPLACE FUNCTION find_parent_place_for_poi(poi_osm_type CHAR(1), + poi_osm_id BIGINT, + poi_partition SMALLINT, + near_centroid GEOMETRY, + addr_street TEXT, + addr_place TEXT, + fallback BOOL = true) + RETURNS BIGINT + AS $$ +DECLARE + parent_place_id BIGINT DEFAULT NULL; + location RECORD; + parent RECORD; +BEGIN + --DEBUG: RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id; + + -- Is this object part of an associatedStreet relation? + FOR location IN + SELECT members FROM planet_osm_rels + WHERE parts @> ARRAY[poi_osm_id] + and members @> ARRAY[lower(poi_osm_type) || poi_osm_id] + and tags @> ARRAY['associatedStreet'] + LOOP + FOR i IN 1..array_upper(location.members, 1) BY 2 LOOP + IF location.members[i+1] = 'street' THEN + --DEBUG: RAISE WARNING 'node in relation %',relation; + FOR parent IN + SELECT place_id from placex + WHERE osm_type = 'W' and osm_id = substring(location.members[i],2)::bigint + and name is not null + and rank_search between 26 and 27 + LOOP + RETURN parent.place_id; + END LOOP; + END IF; + END LOOP; + END LOOP; + + parent_place_id := find_parent_for_address(addr_street, addr_place, + poi_partition, near_centroid); + IF parent_place_id is not null THEN + RETURN parent_place_id; + END IF; + + IF poi_osm_type = 'N' THEN + -- Is this node part of an interpolation? + FOR parent IN + SELECT q.parent_place_id + FROM location_property_osmline q, planet_osm_ways x + WHERE q.linegeo && near_centroid and x.id = q.osm_id + and poi_osm_id = any(x.nodes) + LIMIT 1 + LOOP + --DEBUG: RAISE WARNING 'Get parent from interpolation: %', parent.parent_place_id; + RETURN parent.parent_place_id; + END LOOP; + + -- Is this node part of any other way? + FOR location IN + SELECT p.place_id, p.osm_id, p.rank_search, p.address, + coalesce(p.centroid, ST_Centroid(p.geometry)) as centroid + FROM placex p, planet_osm_ways w + WHERE p.osm_type = 'W' and p.rank_search >= 26 + and p.geometry && near_centroid + and w.id = p.osm_id and poi_osm_id = any(w.nodes) + LOOP + --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id; + + -- Way IS a road then we are on it - that must be our road + IF location.rank_search < 28 THEN + --DEBUG: RAISE WARNING 'node in way that is a street %',location; + return location.place_id; + END IF; + + SELECT find_parent_place_for_poi('W', location.osm_id, poi_partition, + location.centroid, + location.address->'street', + location.address->'place', + false) + INTO parent_place_id; + IF parent_place_id is not null THEN + RETURN parent_place_id; + END IF; + END LOOP; + END IF; + + -- Still nothing, just use the nearest road + IF fallback THEN + SELECT place_id FROM getNearestRoadFeature(poi_partition, near_centroid) INTO parent_place_id; + --DEBUG: RAISE WARNING 'Checked for nearest way (%)', parent_place_id; + END IF; + + RETURN parent_place_id; +END; +$$ +LANGUAGE plpgsql STABLE; + +-- Try to find a linked place for the given object. +CREATE OR REPLACE FUNCTION find_linked_place(bnd placex) + RETURNS placex + AS $$ +DECLARE + relation_members TEXT[]; + rel_member RECORD; + linked_placex placex%ROWTYPE; + bnd_name TEXT; +BEGIN + IF bnd.rank_search >= 26 or bnd.rank_address = 0 + or ST_GeometryType(bnd.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') + THEN + RETURN NULL; + END IF; + + IF bnd.osm_type = 'R' THEN + -- see if we have any special relation members + SELECT members FROM planet_osm_rels WHERE id = bnd.osm_id INTO relation_members; + --DEBUG: RAISE WARNING 'Got relation members'; + + -- Search for relation members with role 'lable'. + IF relation_members IS NOT NULL THEN + FOR rel_member IN + SELECT get_rel_node_members(relation_members, ARRAY['label']) as member + LOOP + --DEBUG: RAISE WARNING 'Found label member %', rel_member.member; + + FOR linked_placex IN + SELECT * from placex + WHERE osm_type = 'N' and osm_id = rel_member.member + and class = 'place' + LOOP + --DEBUG: RAISE WARNING 'Linked label member'; + RETURN linked_placex; + END LOOP; + + END LOOP; + END IF; + END IF; + + IF bnd.name ? 'name' THEN + bnd_name := make_standard_name(bnd.name->'name'); + IF bnd_name = '' THEN + bnd_name := NULL; + END IF; + END IF; + + -- Search for relation members with role admin_center. + IF bnd.osm_type = 'R' and bnd_name is not null + and relation_members is not null THEN + FOR rel_member IN + SELECT get_rel_node_members(relation_members, + ARRAY['admin_center','admin_centre']) as member + LOOP + --DEBUG: RAISE WARNING 'Found admin_center member %', rel_member.member; + FOR linked_placex IN + SELECT * from placex + WHERE osm_type = 'N' and osm_id = rel_member.member + and class = 'place' + LOOP + -- 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 bnd_name = make_standard_name(linked_placex.name->'name') + AND bnd.rank_address = linked_placex.rank_address + THEN + RETURN linked_placex; + END IF; + --DEBUG: RAISE WARNING 'Linked admin_center'; + END LOOP; + END LOOP; + END IF; + + -- Name searches can be done for ways as well as relations + IF bnd.osm_type in ('W','R') and bnd_name is not null THEN + --DEBUG: RAISE WARNING 'Looking for nodes with matching names'; + FOR linked_placex IN + SELECT placex.* from placex + WHERE make_standard_name(name->'name') = bnd_name + AND placex.rank_address = bnd.rank_address + AND placex.osm_type = 'N' + AND placex.rank_search < 26 -- needed to select the right index + AND st_covers(geometry, placex.geometry) + LOOP + --DEBUG: RAISE WARNING 'Found matching place node %', linkedPlacex.osm_id; + RETURN linked_placex; + END LOOP; + END IF; + + RETURN NULL; +END; +$$ +LANGUAGE plpgsql STABLE; + CREATE OR REPLACE FUNCTION placex_insert() RETURNS TRIGGER AS $$ @@ -9,7 +247,6 @@ DECLARE result BOOLEAN; is_area BOOLEAN; country_code VARCHAR(2); - default_language VARCHAR(10); diameter FLOAT; classtable TEXT; classtype TEXT; @@ -24,18 +261,6 @@ BEGIN NEW.partition := get_partition(NEW.country_code); NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry); - -- copy 'name' to or from the default language (if there is a default language) - IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN - default_language := get_country_language_code(NEW.country_code); - IF default_language IS NOT NULL THEN - IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN - NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); - ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN - NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); - END IF; - END IF; - END IF; - IF NEW.osm_type = 'X' THEN -- E'X'ternal records should already be in the right format so do nothing ELSE @@ -189,8 +414,6 @@ CREATE OR REPLACE FUNCTION placex_update() RETURNS TRIGGER AS $$ DECLARE - - place_centroid GEOMETRY; near_centroid GEOMETRY; search_maxdistance FLOAT[]; @@ -198,19 +421,13 @@ DECLARE address_havelevel BOOLEAN[]; i INTEGER; - iMax FLOAT; location RECORD; - way RECORD; - relation RECORD; relation_members TEXT[]; - relMember RECORD; - linkedplacex RECORD; addr_item RECORD; search_diameter FLOAT; search_prevdiameter FLOAT; search_maxrank INTEGER; address_maxrank INTEGER; - address_street_word_id INTEGER; address_street_word_ids INTEGER[]; parent_place_id_rank BIGINT; @@ -226,7 +443,6 @@ DECLARE location_isaddress BOOLEAN; location_keywords INTEGER[]; - default_language TEXT; name_vector INTEGER[]; nameaddress_vector INTEGER[]; @@ -297,12 +513,11 @@ BEGIN -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate - place_centroid := ST_PointOnSurface(NEW.geometry); + NEW.centroid := ST_PointOnSurface(NEW.geometry); -- For searching near features rather use the centroid near_centroid := ST_Envelope(NEW.geometry); - NEW.centroid := null; NEW.postcode := null; - --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid); + --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(NEW.centroid); -- recalculate country and partition IF NEW.rank_search = 4 AND NEW.address is not NULL AND NEW.address ? 'country' THEN @@ -312,12 +527,12 @@ BEGIN NEW.country_code := lower(NEW.address->'country'); NEW.partition := get_partition(lower(NEW.country_code)); IF NEW.partition = 0 THEN - NEW.country_code := lower(get_country_code(place_centroid)); + NEW.country_code := lower(get_country_code(NEW.centroid)); NEW.partition := get_partition(NEW.country_code); END IF; ELSE IF NEW.rank_search >= 4 THEN - NEW.country_code := lower(get_country_code(place_centroid)); + NEW.country_code := lower(get_country_code(NEW.centroid)); ELSE NEW.country_code := NULL; END IF; @@ -345,31 +560,6 @@ BEGIN --DEBUG: RAISE WARNING 'Waterway processed'; END IF; - -- What level are we searching from - search_maxrank := NEW.rank_search; - - -- Thought this wasn't needed but when we add new languages to the country_name table - -- we need to update the existing names - IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN - default_language := get_country_language_code(NEW.country_code); - IF default_language IS NOT NULL THEN - IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN - NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); - ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN - NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); - END IF; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Local names updated'; - - -- Initialise the name vector using our name - name_vector := make_keywords(NEW.name); - nameaddress_vector := '{}'::int[]; - - FOR i IN 1..28 LOOP - address_havelevel[i] := false; - END LOOP; - NEW.importance := null; SELECT wikipedia, importance FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id) @@ -382,18 +572,14 @@ BEGIN IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN --DEBUG: RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id; - - -- We won't get a better centroid, besides these places are too small to care - NEW.centroid := place_centroid; - NEW.parent_place_id := null; -- if we have a POI and there is no address information, -- see if we can get it from a surrounding building IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL AND NEW.housenumber IS NULL THEN - FOR location IN select address from placex where ST_Covers(geometry, place_centroid) - and address is not null + FOR location IN + SELECT address from placex where ST_Covers(geometry, NEW.centroid) and (address ? 'housenumber' or address ? 'street' or address ? 'place') and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') limit 1 @@ -406,117 +592,14 @@ BEGIN END IF; -- We have to find our parent road. - -- Copy data from linked items (points on ways, addr:street links, relations) + NEW.parent_place_id := find_parent_place_for_poi(NEW.osm_type, NEW.osm_id, + NEW.partition, + near_centroid, addr_street, + addr_place); - -- Is this object part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY[lower(NEW.osm_type)||NEW.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN ---RAISE WARNING 'node in relation %',relation; - SELECT place_id from placex where osm_type = 'W' - and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; - END IF; - END LOOP; - --DEBUG: RAISE WARNING 'Checked for street relation (%)', NEW.parent_place_id; - - -- Note that addr:street links can only be indexed once the street itself is indexed - IF NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_street)); - IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Checked for addr:street (%)', NEW.parent_place_id; - - IF NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_place)); - IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Checked for addr:place (%)', NEW.parent_place_id; - - -- Is this node part of an interpolation? - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN - SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x - WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes) - LIMIT 1 INTO NEW.parent_place_id; - END IF; - --DEBUG: RAISE WARNING 'Checked for interpolation (%)', NEW.parent_place_id; - - -- Is this node part of a way? - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN - - FOR location IN - SELECT p.place_id, p.osm_id, p.rank_search, p.address from placex p, planet_osm_ways w - WHERE p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) - LOOP - --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id; - - -- Way IS a road then we are on it - that must be our road - IF location.rank_search < 28 THEN ---RAISE WARNING 'node in way that is a street %',location; - NEW.parent_place_id := location.place_id; - EXIT; - END IF; - --DEBUG: RAISE WARNING 'Checked if way is street (%)', NEW.parent_place_id; - - -- If the way mentions a street or place address, try that for parenting. - IF location.address is not null THEN - IF location.address ? 'street' THEN - address_street_word_ids := get_name_ids(make_standard_name(location.address->'street')); - IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; - EXIT WHEN NEW.parent_place_id is not NULL; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id; - - IF location.address ? 'place' THEN - address_street_word_ids := get_name_ids(make_standard_name(location.address->'place')); - IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; - EXIT WHEN NEW.parent_place_id is not NULL; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Checked for addr:place in way (%)', NEW.parent_place_id; - END IF; - - -- Is the WAY part of a relation - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN ---RAISE WARNING 'node in way that is in a relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; - END IF; - END LOOP; - EXIT WHEN NEW.parent_place_id is not null; - --DEBUG: RAISE WARNING 'Checked for street relation in way (%)', NEW.parent_place_id; - - END LOOP; - END IF; - - -- Still nothing, just use the nearest road - IF NEW.parent_place_id IS NULL THEN - SELECT place_id FROM getNearestRoadFeature(NEW.partition, near_centroid) INTO NEW.parent_place_id; - END IF; - --DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id; - - - -- If we didn't find any road fallback to standard method - IF NEW.parent_place_id IS NOT NULL THEN + -- If we found the road take a shortcut here. + -- Otherwise fall back to the full address getting method below. + IF NEW.parent_place_id is not null THEN -- Get the details of the parent road SELECT p.country_code, p.postcode FROM placex p @@ -526,15 +609,13 @@ BEGIN --DEBUG: RAISE WARNING 'Got parent details from search name'; -- determine postcode - IF NEW.rank_search > 4 THEN - IF NEW.address is not null AND NEW.address ? 'postcode' THEN - NEW.postcode = upper(trim(NEW.address->'postcode')); - ELSE - NEW.postcode := location.postcode; - END IF; - IF NEW.postcode is null THEN - NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry); - END IF; + IF NEW.address is not null AND NEW.address ? 'postcode' THEN + NEW.postcode = upper(trim(NEW.address->'postcode')); + ELSE + NEW.postcode := location.postcode; + END IF; + IF NEW.postcode is null THEN + NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry); END IF; -- If there is no name it isn't searchable, don't bother to create a search record @@ -543,6 +624,9 @@ BEGIN return NEW; END IF; + NEW.name := add_default_place_name(NEW.country_code, NEW.name); + name_vector := make_keywords(NEW.name); + -- Performance, it would be more acurate to do all the rest of the import -- process but it takes too long -- Just be happy with inheriting from parent road only @@ -556,19 +640,17 @@ BEGIN IF NOT %REVERSE-ONLY% THEN -- Merge address from parent - SELECT s.name_vector, s.nameaddress_vector FROM search_name s - WHERE s.place_id = NEW.parent_place_id INTO location; - - nameaddress_vector := array_merge(nameaddress_vector, - location.nameaddress_vector); - nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); + SELECT array_merge(s.name_vector, s.nameaddress_vector) + INTO nameaddress_vector + FROM search_name s + WHERE s.place_id = NEW.parent_place_id; INSERT INTO search_name (place_id, search_rank, address_rank, importance, country_code, name_vector, nameaddress_vector, centroid) VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, NEW.importance, NEW.country_code, name_vector, - nameaddress_vector, place_centroid); + nameaddress_vector, NEW.centroid); --DEBUG: RAISE WARNING 'Place added to search table'; END IF; @@ -580,163 +662,62 @@ BEGIN -- --------------------------------------------------------------------------- -- Full indexing --DEBUG: RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id; + SELECT * INTO location FROM find_linked_place(NEW); + IF location.place_id is not null THEN + --DEBUG: RAISE WARNING 'Linked %', location; - IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN - - -- see if we have any special relation members - select members from planet_osm_rels where id = NEW.osm_id INTO relation_members; - --DEBUG: RAISE WARNING 'Got relation members'; - - IF relation_members IS NOT NULL THEN - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP - --DEBUG: RAISE WARNING 'Found label member %', relMember.member; + -- Use this as the centre point of the geometry + NEW.centroid := coalesce(location.centroid, + ST_Centroid(location.geometry)); - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) - and osm_id = substring(relMember.member,2,10000)::bigint - and class = 'place' order by rank_search desc limit 1 LOOP - - -- 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)); - END IF; - - -- merge in the label name, re-init word vector - IF NOT linkedPlacex.name IS NULL THEN - NEW.name := linkedPlacex.name || NEW.name; - name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name)); - END IF; - - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); - - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - - select wikipedia, importance - FROM compute_importance(linkedPlacex.extratags, NEW.country_code, - 'N', linkedPlacex.osm_id) - INTO linked_wikipedia,linked_importance; - --DEBUG: RAISE WARNING 'Linked label member'; - END LOOP; - - END LOOP; - - IF NEW.centroid IS NULL THEN - - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - --DEBUG: RAISE WARNING 'Found admin_center member %', relMember.member; - - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) - and osm_id = substring(relMember.member,2,10000)::bigint - and class = 'place' 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' - -- 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_address = linkedPlacex.rank_address 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)); - END IF; - - -- merge in the name, re-init word vector - IF NOT linkedPlacex.name IS NULL THEN - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); - END IF; - - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); - - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - - select wikipedia, importance - FROM compute_importance(linkedPlacex.extratags, NEW.country_code, - 'N', linkedPlacex.osm_id) - INTO linked_wikipedia,linked_importance; - --DEBUG: RAISE WARNING 'Linked admin_center'; - END IF; - - END LOOP; - - END LOOP; - - END IF; + -- merge in the label name + IF NOT location.name IS NULL THEN + NEW.name := location.name || NEW.name; END IF; - END IF; - - -- Name searches can be done for ways as well as relations - IF NEW.osm_type in ('W','R') AND NEW.rank_search < 26 AND NEW.rank_address > 0 THEN - - -- not found one yet? how about doing a name search - IF NEW.centroid IS NULL AND (NEW.name->'name') is not null and make_standard_name(NEW.name->'name') != '' THEN - - --DEBUG: RAISE WARNING 'Looking for nodes with matching names'; - FOR linkedPlacex IN select placex.* from placex WHERE - 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 st_covers(NEW.geometry, placex.geometry) - LOOP - --DEBUG: RAISE WARNING 'Found matching place node %', linkedPlacex.osm_id; - -- 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)); - END IF; - - -- merge in the name, re-init word vector - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); + -- merge in extra tags + NEW.extratags := hstore(location.class, location.type) + || coalesce(location.extratags, ''::hstore) + || coalesce(NEW.extratags, ''::hstore); - -- merge in extra tags - NEW.extratags := hstore(linkedPlacex.class, linkedPlacex.type) || coalesce(linkedPlacex.extratags, ''::hstore) || coalesce(NEW.extratags, ''::hstore); + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id + WHERE place_id = location.place_id; - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - - select wikipedia, importance - FROM compute_importance(linkedPlacex.extratags, NEW.country_code, - 'N', linkedPlacex.osm_id) - INTO linked_wikipedia,linked_importance; - --DEBUG: RAISE WARNING 'Linked named place'; - END LOOP; - END IF; - - IF NEW.centroid IS NOT NULL THEN - place_centroid := NEW.centroid; - -- Place might have had only a name tag before but has now received translations - -- from the linked place. Make sure a name tag for the default language exists in - -- this case. - IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN - default_language := get_country_language_code(NEW.country_code); - IF default_language IS NOT NULL THEN - IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN - NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); - ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN - NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); - END IF; - END IF; - END IF; - --DEBUG: RAISE WARNING 'Names updated from linked places'; - END IF; + SELECT wikipedia, importance + FROM compute_importance(location.extratags, NEW.country_code, + 'N', location.osm_id) + INTO linked_wikipedia,linked_importance; - -- Use the maximum importance if a one could be computed from the linked object. + -- Use the maximum importance if one could be computed from the linked object. IF linked_importance is not null AND - (NEW.importance is null or NEW.importance < linked_importance) THEN - NEW.importance = linked_importance; + (NEW.importance is null or NEW.importance < linked_importance) + THEN + NEW.importance = linked_importance; END IF; END IF; + -- What level are we searching from + search_maxrank := NEW.rank_search; + + -- Initialise the name vector using our name + NEW.name := add_default_place_name(NEW.country_code, NEW.name); + name_vector := make_keywords(NEW.name); + nameaddress_vector := '{}'::int[]; + -- make sure all names are in the word table - IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN - perform create_country(NEW.name, lower(NEW.country_code)); + IF NEW.admin_level = 2 + AND NEW.class = 'boundary' AND NEW.type = 'administrative' + AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' + THEN + PERFORM create_country(NEW.name, lower(NEW.country_code)); --DEBUG: RAISE WARNING 'Country names updated'; END IF; + FOR i IN 1..28 LOOP + address_havelevel[i] := false; + END LOOP; + NEW.parent_place_id = 0; parent_place_id_rank = 0; @@ -747,15 +728,20 @@ BEGIN IF NEW.address IS NOT NULL THEN FOR addr_item IN SELECT * FROM each(NEW.address) LOOP - IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', 'district', 'region', 'county', 'municipality', 'hamlet', 'village', 'subdistrict', 'town', 'neighbourhood', 'quarter', 'parish') THEN - address_street_word_id := get_name_id(make_standard_name(addr_item.value)); - IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN - isin_tokens := isin_tokens || address_street_word_id; + IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', + 'district', 'region', 'county', 'municipality', + 'hamlet', 'village', 'subdistrict', 'town', + 'neighbourhood', 'quarter', 'parish') + THEN + address_street_word_ids := word_ids_from_name(addr_item.value); + IF address_street_word_ids is not null THEN + isin_tokens := array_merge(isin_tokens, address_street_word_ids); END IF; IF NOT %REVERSE-ONLY% THEN - address_street_word_id := get_word_id(make_standard_name(addr_item.value)); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + address_street_word_ids := addr_ids_from_name(addr_item.value); + IF address_street_word_ids is not null THEN + nameaddress_vector := array_merge(nameaddress_vector, + address_street_word_ids); END IF; END IF; END IF; @@ -764,16 +750,17 @@ BEGIN isin := regexp_split_to_array(addr_item.value, E'[;,]'); IF array_upper(isin, 1) IS NOT NULL THEN FOR i IN 1..array_upper(isin, 1) LOOP - address_street_word_id := get_name_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN - isin_tokens := isin_tokens || address_street_word_id; + address_street_word_ids := word_ids_from_name(isin[i]); + IF address_street_word_ids is not null THEN + isin_tokens := array_merge(isin_tokens, address_street_word_ids); END IF; -- merge word into address vector IF NOT %REVERSE-ONLY% THEN - address_street_word_id := get_word_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + address_street_word_ids := addr_ids_from_name(isin[i]); + IF address_street_word_ids is not null THEN + nameaddress_vector := array_merge(nameaddress_vector, + address_street_word_ids); END IF; END IF; END LOOP; @@ -793,13 +780,13 @@ BEGIN location_parent := NULL; -- added ourself as address already address_havelevel[NEW.rank_address] := true; - --DEBUG: RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens; + --DEBUG: RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, NEW.centroid, search_maxrank, isin_tokens; FOR location IN SELECT * from getNearFeatures(NEW.partition, CASE WHEN NEW.rank_search >= 26 AND NEW.rank_search < 30 THEN NEW.geometry - ELSE place_centroid END, + ELSE NEW.centroid END, search_maxrank, isin_tokens) LOOP IF location.rank_address != location_rank_search THEN @@ -896,16 +883,11 @@ BEGIN nameaddress_vector, centroid) VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, NEW.importance, NEW.country_code, name_vector, - nameaddress_vector, place_centroid); + nameaddress_vector, NEW.centroid); END IF; END IF; - -- If we've not managed to pick up a better one - default centroid - IF NEW.centroid IS NULL THEN - NEW.centroid := place_centroid; - END IF; - --DEBUG: RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id; RETURN NEW; diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index 6980a583..6371e9ad 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -223,41 +223,70 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_country_language_codes(search_country_code VARCHAR(2)) - RETURNS TEXT[] +CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) + RETURNS INTEGER AS $$ DECLARE nearcountry RECORD; BEGIN FOR nearcountry IN - SELECT country_default_language_codes from country_name - WHERE country_code = search_country_code limit 1 + SELECT partition from country_name where country_code = in_country_code LOOP - RETURN lower(nearcountry.country_default_language_codes); + RETURN nearcountry.partition; END LOOP; - RETURN NULL; + RETURN 0; END; $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) - RETURNS INTEGER +-- Find the parent of an address with addr:street/addr:place tag. +-- +-- \param street Value of addr:street or NULL if tag is missing. +-- \param place Value of addr:place or NULL if tag is missing. +-- \param partition Partition where to search the parent. +-- \param centroid Location of the address. +-- +-- \return Place ID of the parent if one was found, NULL otherwise. +CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT, + partition SMALLINT, + centroid GEOMETRY) + RETURNS BIGINT AS $$ DECLARE - nearcountry RECORD; + parent_place_id BIGINT; + word_ids INTEGER[]; BEGIN - FOR nearcountry IN - SELECT partition from country_name where country_code = in_country_code - LOOP - RETURN nearcountry.partition; - END LOOP; - RETURN 0; + IF street is not null THEN + -- Check for addr:street attributes + -- Note that addr:street links can only be indexed, once the street itself is indexed + word_ids := word_ids_from_name(street); + IF word_ids is not null THEN + parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids); + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + -- Check for addr:place attributes. + IF place is not null THEN + word_ids := word_ids_from_name(place); + IF word_ids is not null THEN + parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids); + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + RETURN NULL; END; $$ LANGUAGE plpgsql STABLE; - CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN AS $$ @@ -338,45 +367,6 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) - RETURNS TEXT[] - AS $$ -DECLARE - result TEXT[]; - i INTEGER; -BEGIN - - FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP - IF members[i+1] = member THEN - result := result || members[i]; - END IF; - END LOOP; - - return result; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - - -CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) - RETURNS SETOF TEXT - AS $$ -DECLARE - i INTEGER; -BEGIN - - FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP - IF members[i+1] = ANY(memberLabels) THEN - RETURN NEXT members[i]; - END IF; - END LOOP; - - RETURN; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - - CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER) RETURNS SETOF GEOMETRY diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql index f770e83e..41758c83 100644 --- a/sql/partition-functions.src.sql +++ b/sql/partition-functions.src.sql @@ -82,54 +82,51 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) - RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedRoadPlaceId(in_partition INTEGER, + point GEOMETRY, + isin_token INTEGER[]) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; + parent BIGINT; BEGIN -- start IF in_partition = -partition- THEN - FOR r IN - SELECT place_id, name_vector, address_rank, search_rank, - ST_Distance(centroid, point) as distance, null as isguess - FROM search_name_-partition- - WHERE name_vector && isin_token - AND centroid && ST_Expand(point, 0.015) - AND search_rank between 26 and 27 - ORDER BY distance ASC limit 1 - LOOP - RETURN NEXT r; - END LOOP; - RETURN; + SELECT place_id FROM search_name_-partition- + INTO parent + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.015) + AND search_rank between 26 and 27 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end RAISE EXCEPTION 'Unknown partition %', in_partition; END $$ -LANGUAGE plpgsql; +LANGUAGE plpgsql STABLE; -create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) - RETURNS setof nearfeature AS $$ +CREATE OR REPLACE FUNCTION getNearestNamedPlacePlaceId(in_partition INTEGER, + point GEOMETRY, + isin_token INTEGER[]) + RETURNS BIGINT + AS $$ DECLARE - r nearfeature%rowtype; + parent BIGINT; BEGIN -- start IF in_partition = -partition- THEN - FOR r IN - SELECT place_id, name_vector, address_rank, search_rank, - ST_Distance(centroid, point) as distance, null as isguess - FROM search_name_-partition- - WHERE name_vector && isin_token - AND centroid && ST_Expand(point, 0.04) - AND search_rank between 16 and 22 - ORDER BY distance ASC limit 1 - LOOP - RETURN NEXT r; - END LOOP; - RETURN; + SELECT place_id + INTO parent + FROM search_name_-partition- + WHERE name_vector && isin_token + AND centroid && ST_Expand(point, 0.04) + AND search_rank between 16 and 22 + ORDER BY ST_Distance(centroid, point) ASC limit 1; + RETURN parent; END IF; -- end diff --git a/sql/table-triggers.sql b/sql/table-triggers.sql new file mode 100644 index 00000000..5bf895a1 --- /dev/null +++ b/sql/table-triggers.sql @@ -0,0 +1,22 @@ +-- insert creates the location tables, creates location indexes if indexed == true +CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex + FOR EACH ROW EXECUTE PROCEDURE placex_insert(); +CREATE TRIGGER osmline_before_insert BEFORE INSERT ON location_property_osmline + FOR EACH ROW EXECUTE PROCEDURE osmline_insert(); + +-- update insert creates the location tables +CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex + FOR EACH ROW EXECUTE PROCEDURE placex_update(); +CREATE TRIGGER osmline_before_update BEFORE UPDATE ON location_property_osmline + FOR EACH ROW EXECUTE PROCEDURE osmline_update(); + +-- diff update triggers +CREATE TRIGGER placex_before_delete AFTER DELETE ON placex + FOR EACH ROW EXECUTE PROCEDURE placex_delete(); +CREATE TRIGGER place_before_delete BEFORE DELETE ON place + FOR EACH ROW EXECUTE PROCEDURE place_delete(); +CREATE TRIGGER place_before_insert BEFORE INSERT ON place + FOR EACH ROW EXECUTE PROCEDURE place_insert(); + +CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode + FOR EACH ROW EXECUTE PROCEDURE postcode_update(); diff --git a/sql/tables.sql b/sql/tables.sql index 0245e3c3..28d664be 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -176,7 +176,7 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:searc CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {ts:address-index} WHERE linked_place_id IS NOT NULL; CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index}; CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index}; -CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) {ts:address-index} WHERE osm_type='N' and rank_search < 26; +CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {ts:address-index} WHERE osm_type='N' and rank_search < 26; DROP SEQUENCE IF EXISTS seq_place; CREATE SEQUENCE seq_place start 1; @@ -188,26 +188,6 @@ GRANT SELECT ON planet_osm_ways to "{www-user}" ; GRANT SELECT ON planet_osm_rels to "{www-user}" ; GRANT SELECT on location_area to "{www-user}" ; --- insert creates the location tables, creates location indexes if indexed == true -CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_insert(); -CREATE TRIGGER osmline_before_insert BEFORE INSERT ON location_property_osmline - FOR EACH ROW EXECUTE PROCEDURE osmline_insert(); - --- update insert creates the location tables -CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_update(); -CREATE TRIGGER osmline_before_update BEFORE UPDATE ON location_property_osmline - FOR EACH ROW EXECUTE PROCEDURE osmline_update(); - --- diff update triggers -CREATE TRIGGER placex_before_delete AFTER DELETE ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_delete(); -CREATE TRIGGER place_before_delete BEFORE DELETE ON place - FOR EACH ROW EXECUTE PROCEDURE place_delete(); -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 ( @@ -224,9 +204,6 @@ CREATE TABLE location_postcode ( CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index}; GRANT SELECT ON location_postcode TO "{www-user}" ; -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 ( osm_id BIGINT, diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql index ef55e11c..b9c4fcfc 100644 --- a/sql/tiger_import_start.sql +++ b/sql/tiger_import_start.sql @@ -2,7 +2,7 @@ DROP TABLE IF EXISTS location_property_tiger_import; CREATE TABLE location_property_tiger_import (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT); CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER, - in_endnumber INTEGER, interpolationtype TEXT, + in_endnumber INTEGER, interpolationtype TEXT, in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER AS $$ DECLARE @@ -14,7 +14,7 @@ DECLARE out_partition INTEGER; out_parent_place_id BIGINT; location RECORD; - address_street_word_id INTEGER; + address_street_word_ids INTEGER[]; BEGIN @@ -56,11 +56,10 @@ BEGIN out_partition := get_partition('us'); out_parent_place_id := null; - address_street_word_id := get_name_id(make_standard_name(in_street)); - IF address_street_word_id IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, ARRAY[address_street_word_id]) LOOP - out_parent_place_id := location.place_id; - END LOOP; + address_street_word_ids := word_ids_from_name(in_street); + IF address_street_word_ids IS NOT NULL THEN + out_parent_place_id := getNearestNamedRoadPlaceId(out_partition, place_centroid, + address_street_word_ids); END IF; IF out_parent_place_id IS NULL THEN diff --git a/utils/setup.php b/utils/setup.php index 8ad96a95..90df0835 100644 --- a/utils/setup.php +++ b/utils/setup.php @@ -100,6 +100,7 @@ if ($aCMDResult['create-tables'] || $aCMDResult['all']) { $bDidSomething = true; $oSetup->createTables($aCMDResult['reverse-only']); $oSetup->createFunctions(); + $oSetup->createTableTriggers(); } if ($aCMDResult['create-partition-tables'] || $aCMDResult['all']) {