X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2643225b72fe2d32ced5191511a059e88badac79..4498b89a7501f95a41113e8d4086f7d750c8e2c0:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 5c4cea05..898cdb69 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -1,50 +1,3 @@ ---DROP TRIGGER IF EXISTS place_before_insert on placex; ---DROP TRIGGER IF EXISTS place_before_update on placex; ---CREATE TYPE addresscalculationtype AS ( --- word text, --- score integer ---); - -CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT - AS $$ -DECLARE -BEGIN - RETURN c||'|'||t; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION isbrokengeometry(place geometry) RETURNS BOOLEAN - AS $$ -DECLARE - NEWgeometry geometry; -BEGIN - NEWgeometry := place; - IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN - RETURN true; - END IF; - RETURN false; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - -CREATE OR REPLACE FUNCTION clean_geometry(place geometry) RETURNS geometry - AS $$ -DECLARE - NEWgeometry geometry; -BEGIN - NEWgeometry := place; - IF ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN - NEWgeometry := ST_buffer(NEWgeometry,0); - IF ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN - RETURN ST_SetSRID(ST_Point(0,0),4326); - END IF; - END IF; - RETURN NEWgeometry; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER AS $$ DECLARE @@ -52,12 +5,6 @@ DECLARE BEGIN -- RAISE WARNING '%',place; NEWgeometry := ST_PointOnSurface(place); --- IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN --- NEWgeometry := ST_buffer(NEWgeometry,0); --- IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity') THEN --- RETURN 0; --- END IF; --- END IF; RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer); END; $$ @@ -160,38 +107,6 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION getorcreate_tagpair(lookup_class text, lookup_type text) - RETURNS INTEGER - AS $$ -DECLARE - lookup_token TEXT; - return_word_id INTEGER; -BEGIN - lookup_token := lookup_class||'='||lookup_type; - SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id; - IF return_word_id IS NULL THEN - return_word_id := nextval('seq_word'); - INSERT INTO word VALUES (return_word_id, lookup_token, null, null, null, null, 0); - END IF; - RETURN return_word_id; -END; -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_tagpair(lookup_class text, lookup_type text) - RETURNS INTEGER - AS $$ -DECLARE - lookup_token TEXT; - return_word_id INTEGER; -BEGIN - lookup_token := lookup_class||'='||lookup_type; - SELECT min(word_id) FROM word WHERE word_token = lookup_token into return_word_id; - RETURN return_word_id; -END; -$$ -LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION getorcreate_amenityoperator(lookup_word TEXT, lookup_class text, lookup_type text, op text) RETURNS INTEGER AS $$ @@ -474,31 +389,6 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION get_word_score(wordscores wordscore[], words text[]) RETURNS integer - AS $$ -DECLARE - idxword integer; - idxscores integer; - result integer; -BEGIN - IF (wordscores is null OR words is null) THEN - RETURN 0; - END IF; - - result := 0; - FOR idxword in 1 .. array_upper(words, 1) LOOP - FOR idxscores in 1 .. array_upper(wordscores, 1) LOOP - IF wordscores[idxscores].word = words[idxword] THEN - result := result + wordscores[idxscores].score; - END IF; - END LOOP; - END LOOP; - - RETURN result; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; - CREATE OR REPLACE FUNCTION get_country_code(place geometry) RETURNS TEXT AS $$ DECLARE @@ -548,21 +438,6 @@ BEGIN RETURN nearcountry.country_code; END LOOP; - -- WorldBoundaries data (second fallback - think there might be something broken in this data) --- FOR nearcountry IN select country_code from country where st_covers(geometry, place_centre) limit 1 --- LOOP --- RETURN nearcountry.country_code; --- END LOOP; - ---RAISE WARNING 'near country: %', ST_AsText(place_centre); - - -- Still not in a country - try nearest within ~12 miles of a country --- FOR nearcountry IN select country_code from country where st_distance(geometry, place_centre) < 0.5 --- order by st_distance(geometry, place) limit 1 --- LOOP --- RETURN nearcountry.country_code; --- END LOOP; - RETURN NULL; END; $$ @@ -660,7 +535,7 @@ BEGIN x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, centroid, secgeo); END LOOP; - ELSEIF rank_search < 26 THEN + ELSE diameter := 0.02; IF rank_address = 0 THEN @@ -684,16 +559,6 @@ BEGIN secgeo := ST_Buffer(geometry, diameter); x := insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo); - ELSE - - -- ~ 20meters - secgeo := ST_Buffer(geometry, 0.0002); - x := insertLocationAreaRoadNear(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo); - - -- ~ 100meters - secgeo := ST_Buffer(geometry, 0.001); - x := insertLocationAreaRoadFar(partition, place_id, country_code, keywords, rank_search, rank_address, true, ST_Centroid(geometry), secgeo); - END IF; RETURN true; @@ -701,97 +566,82 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION update_location( - partition INTEGER, - place_id BIGINT, - place_country_code varchar(2), - name hstore, - rank_search INTEGER, - rank_address INTEGER, - geometry GEOMETRY - ) - RETURNS BOOLEAN - AS $$ -DECLARE - b BOOLEAN; -BEGIN - b := deleteLocationArea(partition, place_id, rank_search); --- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); - RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry); -END; -$$ -LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id BIGINT, to_add INTEGER[]) - RETURNS BOOLEAN - AS $$ + +-- find the parant road of an interpolation +CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, place TEXT, + partition INTEGER, centroid GEOMETRY, geom GEOMETRY) +RETURNS BIGINT AS $$ DECLARE - childplace RECORD; + addr_street TEXT; + addr_place TEXT; + parent_place_id BIGINT; + address_street_word_ids INTEGER[]; + + waynodes BIGINT[]; + + location RECORD; BEGIN + addr_street = street; + addr_place = place; - IF #to_add = 0 THEN - RETURN true; + IF addr_street is null and addr_place is null THEN + select nodes from planet_osm_ways where id = wayid INTO waynodes; + FOR location IN SELECT placex.street, placex.addr_place from placex + where osm_type = 'N' and osm_id = ANY(waynodes) + and (placex.street is not null or placex.addr_place is not null) + and indexed_status < 100 + limit 1 LOOP + addr_street = location.street; + addr_place = location.addr_place; + END LOOP; END IF; - -- this should just be an update, but it seems to do insane things to the index size (delete and insert doesn't) - FOR childplace IN select * from search_name,place_addressline - where address_place_id = parent_place_id - and search_name.place_id = place_addressline.place_id - LOOP - delete from search_name where place_id = childplace.place_id; - IF not (ARRAY[to_add] <@ childplace.nameaddress_vector) THEN - childplace.nameaddress_vector := childplace.nameaddress_vector || to_add; - END IF; - IF childplace.place_id = parent_place_id and not (ARRAY[to_add] <@ childplace.name_vector) THEN - childplace.name_vector := childplace.name_vector || to_add; + 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; - insert into search_name (place_id, search_rank, address_rank, country_code, name_vector, nameaddress_vector, centroid) - values (childplace.place_id, childplace.search_rank, childplace.address_rank, childplace.country_code, - childplace.name_vector, childplace.nameaddress_vector, childplace.centroid); - END LOOP; - - RETURN true; -END; -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id BIGINT, name hstore) RETURNS BOOLEAN - AS $$ -DECLARE - newkeywords INTEGER[]; - addedkeywords INTEGER[]; - removedkeywords INTEGER[]; -BEGIN - - -- what has changed? - newkeywords := make_keywords(name); - select coalesce(newkeywords,'{}'::INTEGER[]) - coalesce(location_point.keywords,'{}'::INTEGER[]), - coalesce(location_point.keywords,'{}'::INTEGER[]) - coalesce(newkeywords,'{}'::INTEGER[]) from location_point - where place_id = OLD_place_id into addedkeywords, removedkeywords; + END IF; --- RAISE WARNING 'update_location_nameonly for %: new:% added:% removed:%', OLD_place_id, newkeywords, addedkeywords, removedkeywords; + 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; - IF #removedkeywords > 0 THEN - -- abort due to tokens removed - RETURN false; + IF parent_place_id is null THEN + FOR location IN SELECT place_id FROM placex + WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26 + ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+ + ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1 + LOOP + parent_place_id := location.place_id; + END LOOP; END IF; - - IF #addedkeywords > 0 THEN - -- short circuit - no changes - RETURN true; + + IF parent_place_id is null THEN + RETURN 0; END IF; - UPDATE location_area set keywords = newkeywords where place_id = OLD_place_id; - RETURN search_name_add_words(OLD_place_id, addedkeywords); + RETURN parent_place_id; END; $$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER +CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT, + parent_id BIGINT, partition INTEGER, + country_code TEXT, geometry_sector INTEGER, + defpostalcode TEXT, geom GEOMETRY) RETURNS INTEGER AS $$ DECLARE - + newpoints INTEGER; waynodes BIGINT[]; nodeid BIGINT; @@ -804,117 +654,111 @@ DECLARE originalnumberrange INTEGER; housenum INTEGER; linegeo GEOMETRY; - search_place_id BIGINT; - defpostalcode TEXT; + splitline GEOMETRY; + sectiongeo GEOMETRY; + pointgeo GEOMETRY; - havefirstpoint BOOLEAN; - linestr TEXT; BEGIN - newpoints := 0; - IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN - - select postcode from placex where osm_type = 'W' and osm_id = wayid INTO defpostalcode; - select nodes from planet_osm_ways where id = wayid INTO waynodes; ---RAISE WARNING 'interpolation % % %',wayid,interpolationtype,waynodes; - IF array_upper(waynodes, 1) IS NOT NULL THEN - - havefirstpoint := false; - - FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP - - -- If there is a place of a type other than place/house, use that because - -- it is guaranteed to be the original node. For place/house types use the - -- one with the smallest id because the original node was created first. - -- Ignore all nodes marked for deletion. (Might happen when the type changes.) - select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id; - IF search_place_id IS NULL THEN - -- if no such node exists, create a record of the right type - select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode; - select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry; - IF nextnode.geometry IS NULL THEN - -- we don't have any information about this point, most likely - -- because an excerpt was updated and the node never imported - -- because the interpolation is outside the region of the excerpt. - -- Give up. - RETURN newpoints; - END IF; - ELSE - select * from placex where place_id = search_place_id INTO nextnode; - END IF; + delete from placex where osm_type = 'W' and osm_id = wayid + and class = 'place' and type = 'address'; + + IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN + stepsize := 2; + ELSEIF interpolationtype = 'all' THEN + stepsize := 1; + ELSEIF interpolationtype ~ '^\d+$' THEN + stepsize := interpolationtype::INTEGER; + ELSE + RETURN 0; + END IF; ---RAISE WARNING 'interpolation node % % % ',nextnode.housenumber,ST_X(nextnode.geometry),ST_Y(nextnode.geometry); - - IF havefirstpoint THEN + select nodes from planet_osm_ways where id = wayid INTO waynodes; - -- add point to the line string - linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry); - endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer; + IF array_upper(waynodes, 1) IS NULL THEN + RETURN 0; + END IF; - IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 AND @(startnumber - endnumber) < 1000 THEN + linegeo := geom; + startnumber := NULL; + newpoints := 0; ---RAISE WARNING 'interpolation end % % ',nextnode.place_id,endnumber; + FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP + + -- If there is a place of a type other than place/house, use that because + -- it is guaranteed to be the original node. For place/house types use the + -- one with the smallest id because the original node was created first. + -- Ignore all nodes marked for deletion. (Might happen when the type changes.) + select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT + and indexed_status < 100 and housenumber is not NULL + order by (type = 'address'),place_id limit 1 INTO nextnode; + IF nextnode.place_id IS NOT NULL THEN + + IF nodeidpos > 1 and nodeidpos < array_upper(waynodes, 1) THEN + -- Make sure that the point is actually on the line. That might + -- be a bit paranoid but ensures that the algorithm still works + -- should osm2pgsql attempt to repair geometries. + splitline := split_line_on_node(linegeo, nextnode.geometry); + sectiongeo := ST_GeometryN(splitline, 1); + linegeo := ST_GeometryN(splitline, 2); + ELSE + sectiongeo = linegeo; + END IF; + endnumber := substring(nextnode.housenumber,'[0-9]+')::integer; - IF startnumber != endnumber THEN + IF startnumber IS NOT NULL AND endnumber IS NOT NULL + AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber + AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN - linestr := linestr || ')'; ---RAISE WARNING 'linestr %',linestr; - linegeo := ST_GeomFromText(linestr,4326); - linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry); - IF (startnumber > endnumber) THEN - housenum := endnumber; - endnumber := startnumber; - startnumber := housenum; - linegeo := ST_Reverse(linegeo); - END IF; - orginalstartnumber := startnumber; - originalnumberrange := endnumber - startnumber; - --- Too much broken data worldwide for this test to be worth using --- IF originalnumberrange > 500 THEN --- RAISE WARNING 'Number block of % while processing % %', originalnumberrange, prevnode, nextnode; --- END IF; - - IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN - startnumber := startnumber + 1; - stepsize := 2; - ELSE - IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN - startnumber := startnumber + 2; - stepsize := 2; - ELSE -- everything else assumed to be 'all' - startnumber := startnumber + 1; - stepsize := 1; - END IF; - END IF; - endnumber := endnumber - 1; - delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id and type = 'house' and place_id != prevnode.place_id; - FOR housenum IN startnumber..endnumber BY stepsize LOOP - -- this should really copy postcodes but it puts a huge burdon on the system for no big benefit - -- ideally postcodes should move up to the way - insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode, - country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry) - values ('N',prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode), - prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_LineInterpolatePoint(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float)); - newpoints := newpoints + 1; ---RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum; - END LOOP; - END IF; - havefirstpoint := false; + IF (startnumber > endnumber) THEN + housenum := endnumber; + endnumber := startnumber; + startnumber := housenum; + sectiongeo := ST_Reverse(sectiongeo); + END IF; + orginalstartnumber := startnumber; + originalnumberrange := endnumber - startnumber; + + startnumber := startnumber + stepsize; + -- correct for odd/even + IF (interpolationtype = 'odd' AND startnumber%2 = 0) + OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN + startnumber := startnumber - 1; END IF; + endnumber := endnumber - 1; + + -- keep for compatibility with previous versions + delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id + and place_id != prevnode.place_id and class = 'place' + and type = 'house'; + FOR housenum IN startnumber..endnumber BY stepsize LOOP + pointgeo := ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float); + insert into placex (place_id, partition, osm_type, osm_id, + class, type, admin_level, housenumber, + postcode, + country_code, parent_place_id, rank_address, rank_search, + indexed_status, indexed_date, geometry_sector, + calculated_country_code, centroid, geometry) + values (nextval('seq_place'), partition, 'W', wayid, + 'place', 'address', prevnode.admin_level, housenum, + coalesce(prevnode.postcode, defpostalcode), + prevnode.country_code, parent_id, 30, 30, + 0, now(), geometry_sector, country_code, + pointgeo, pointgeo); + newpoints := newpoints + 1; +--RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum; + END LOOP; END IF; - IF NOT havefirstpoint THEN - startnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer; - IF startnumber IS NOT NULL AND startnumber > 0 THEN - havefirstpoint := true; - linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry); - prevnode := nextnode; - END IF; ---RAISE WARNING 'interpolation start % % ',nextnode.place_id,startnumber; + -- early break if we are out of line string, + -- might happen when a line string loops back on itself + IF ST_GeometryType(linegeo) != 'ST_LineString' THEN + RETURN newpoints; END IF; - END LOOP; + + startnumber := substring(nextnode.housenumber,'[0-9]+')::integer; + prevnode := nextnode; END IF; - END IF; + END LOOP; --RAISE WARNING 'interpolation points % ',newpoints; @@ -936,27 +780,15 @@ DECLARE BEGIN --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id; - -- just block these - IF NEW.class in ('landuse','natural') and NEW.name is null THEN --- RAISE WARNING 'empty landuse %',NEW.osm_id; - RETURN null; + -- ignore interpolated addresses + IF NEW.class = 'place' and NEW.type = 'address' THEN + RETURN NEW; END IF; IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN -- block all invalid geometary - just not worth the risk. seg faults are causing serious problems. RAISE WARNING 'invalid geometry %',NEW.osm_id; RETURN NULL; - - -- Dead code - IF NEW.osm_type = 'R' THEN - -- invalid multipolygons can crash postgis, don't even bother to try! - RETURN NULL; - END IF; - NEW.geometry := ST_buffer(NEW.geometry,0); - IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN - RAISE WARNING 'Invalid geometary, rejecting: % %', NEW.osm_type, NEW.osm_id; - RETURN NULL; - END IF; END IF; --DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; @@ -1018,6 +850,13 @@ BEGIN NEW.rank_address := 5; END IF; + ELSEIF NEW.calculated_country_code = 'sg' THEN + + IF NEW.postcode ~ '^([0-9]{6})$' THEN + NEW.rank_search := 25; + NEW.rank_address := 11; + END IF; + ELSEIF NEW.calculated_country_code = 'de' THEN IF NEW.postcode ~ '^([0-9]{5})$' THEN @@ -1095,8 +934,6 @@ BEGIN NEW.rank_address := NEW.rank_search; ELSEIF NEW.type in ('houses') THEN -- can't guarantee all required nodes loaded yet due to caching in osm2pgsql - -- insert new point into place for each derived building - --i := create_interpolation(NEW.osm_id, NEW.housenumber); NEW.rank_search := 28; NEW.rank_address := 0; END IF; @@ -1114,27 +951,23 @@ BEGIN END IF; ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN NEW.rank_search := 22; - NEW.rank_address := NEW.rank_search; + IF NEW.type in ('residential', 'farm', 'farmyard', 'industrial', 'commercial', 'allotments', 'retail') THEN + NEW.rank_address := NEW.rank_search; + ELSE + NEW.rank_address := 0; + END IF; ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN NEW.rank_search := 18; NEW.rank_address := 0; ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN NEW.rank_search := 4; NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN - RETURN NULL; -- any feature more than 5 square miles is probably worth indexing ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN NEW.rank_search := 22; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND - NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN --- RAISE WARNING 'unnamed minor feature %',NEW.osm_id; - RETURN NULL; + NEW.rank_address := 0; ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN RETURN NULL; - ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN - RETURN NULL; ELSEIF NEW.class = 'waterway' THEN IF NEW.osm_type = 'R' THEN NEW.rank_search := 16; @@ -1231,6 +1064,7 @@ BEGIN END IF; END IF; + -- add to tables for special search -- Note: won't work on initial import because the classtype tables -- do not yet exist. It won't hurt either. @@ -1241,11 +1075,6 @@ BEGIN USING NEW.place_id, ST_Centroid(NEW.geometry); END IF; - --- IF NEW.rank_search < 26 THEN --- RAISE WARNING 'placex insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; --- END IF; - RETURN NEW; END; @@ -1262,8 +1091,6 @@ DECLARE search_maxdistance FLOAT[]; search_mindistance FLOAT[]; address_havelevel BOOLEAN[]; --- search_scores wordscore[]; --- search_scores_pos INTEGER; i INTEGER; iMax FLOAT; @@ -1288,8 +1115,7 @@ DECLARE location_distance FLOAT; location_parent GEOMETRY; location_isaddress BOOLEAN; - - tagpairid INTEGER; + location_keywords INTEGER[]; default_language TEXT; name_vector INTEGER[]; @@ -1311,6 +1137,11 @@ BEGIN RETURN NEW; END IF; + -- ignore interpolated addresses + IF NEW.class = 'place' and NEW.type = 'address' THEN + RETURN NEW; + END IF; + --DEBUG: RAISE WARNING 'placex_update % %',NEW.osm_type,NEW.osm_id; --RAISE WARNING '%',NEW.place_id; @@ -1328,7 +1159,6 @@ BEGIN result := deleteSearchName(NEW.partition, NEW.place_id); DELETE FROM place_addressline WHERE place_id = NEW.place_id; - DELETE FROM place_boundingbox where place_id = NEW.place_id; result := deleteRoad(NEW.partition, NEW.place_id); result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search); UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id; @@ -1337,17 +1167,12 @@ BEGIN RETURN NEW; END IF; - IF NEW.class = 'place' AND NEW.type = 'houses' THEN - i := create_interpolation(NEW.osm_id, NEW.housenumber); - RETURN NEW; - END IF; - -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate place_centroid := ST_PointOnSurface(NEW.geometry); NEW.centroid := null; - -- reclaculate country and partition + -- recalculate country and partition IF NEW.rank_search = 4 THEN -- for countries, believe the mapped country code, -- so that we remain in the right partition if the boundaries @@ -1370,16 +1195,31 @@ BEGIN END IF; NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid); + -- interpolations + IF NEW.class = 'place' AND NEW.type = 'houses'THEN + IF NEW.osm_type = 'W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN + NEW.parent_place_id := get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place, + NEW.partition, place_centroid, NEW.geometry); + i := create_interpolation(NEW.osm_id, NEW.housenumber, NEW.parent_place_id, + NEW.partition, NEW.calculated_country_code, + NEW.geometry_sector, NEW.postcode, NEW.geometry); + END IF; + RETURN NEW; + END IF; + -- waterway ways are linked when they are part of a relation and have the same class/type IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN - FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] + FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] LOOP - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF relation.members[i+1] in ('', 'main_stream') AND substring(relation.members[i],1,1) = 'w' THEN - --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i]; - FOR location IN SELECT * FROM placex WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint and class = NEW.class and type = NEW.type + FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP + IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN + --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.members[i]; + FOR linked_node_id IN SELECT place_id FROM placex + WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint + and class = NEW.class and type = NEW.type + and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') LOOP - UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id; + UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; END LOOP; END IF; END LOOP; @@ -1409,12 +1249,6 @@ BEGIN name_vector := make_keywords(NEW.name); nameaddress_vector := '{}'::int[]; - -- some tag combinations add a special id for search - tagpairid := get_tagpair(NEW.class,NEW.type); - IF tagpairid IS NOT NULL THEN - name_vector := name_vector + tagpairid; - END IF; - FOR i IN 1..28 LOOP address_havelevel[i] := false; END LOOP; @@ -1437,41 +1271,67 @@ BEGIN NEW.parent_place_id := null; - -- to do that we have to find our parent road - -- Copy data from linked items (points on ways, addr:street links, relations) - -- Note that addr:street links can only be indexed once the street itself is indexed - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN + -- 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 NEW.street IS NULL AND NEW.addr_place IS NULL + AND NEW.housenumber IS NULL THEN + FOR location IN select * from placex where ST_Covers(geometry, place_centroid) + and (housenumber is not null or street is not null or addr_place is not null) + and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + limit 1 + LOOP + NEW.housenumber := location.housenumber; + NEW.street := location.street; + NEW.addr_place := location.addr_place; + END LOOP; + END IF; - -- if there is no address information, see if we can get it from a surrounding building - IF NEW.street IS NULL AND NEW.addr_place IS NULL AND NEW.housenumber IS NULL THEN - FOR location IN select * from placex where ST_Covers(geometry, place_centroid) and rank_search > 28 and (housenumber is not null or street is not null or addr_place is not null) AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') - LOOP - NEW.housenumber := location.housenumber; - NEW.street := location.street; - NEW.addr_place := location.addr_place; - END LOOP; - END IF; + -- We have to find our parent road. + -- Copy data from linked items (points on ways, addr:street links, relations) - -- Is this node part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id] + -- 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'] AND array_upper(relation.members, 1) IS NOT NULL THEN + 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 + 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; + END LOOP; + + + -- Note that addr:street links can only be indexed once the street itself is indexed + IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := location.place_id; + END LOOP; + END IF; + END IF; + + IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN --RAISE WARNING 'x1'; -- Is this node part of a way? - FOR way IN select id from planet_osm_ways where nodes @> ARRAY[NEW.osm_id] LOOP ---RAISE WARNING '%', way; - FOR location IN select * from placex where osm_type = 'W' and osm_id = way.id + FOR location IN select p.* from placex p, planet_osm_ways w + where p.osm_type = 'W' and p.rank_search >= 26 + and p.geometry && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes) LOOP --RAISE WARNING '%', location; -- Way IS a road then we are on it - that must be our road @@ -1480,6 +1340,11 @@ BEGIN NEW.parent_place_id := location.place_id; END IF; + -- If this way is a street interpolation line then it is probably as good as we are going to get + IF NEW.parent_place_id IS NULL AND location.class = 'place' and location.type='houses' THEN + NEW.parent_place_id := location.parent_place_id; + END IF; + -- Is the WAY part of a relation IF NEW.parent_place_id IS NULL THEN FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] @@ -1495,73 +1360,29 @@ BEGIN END LOOP; END IF; END LOOP; - END IF; - - -- If the way contains an explicit name of a street copy it - -- Slightly less strict then above because data is copied from any object. - IF NEW.street IS NULL AND NEW.addr_place IS NULL THEN ---RAISE WARNING 'node in way that has a streetname %',location; - NEW.street := location.street; - NEW.addr_place := location.addr_place; END IF; - -- If this way is a street interpolation line then it is probably as good as we are going to get - IF NEW.parent_place_id IS NULL AND NEW.street IS NULL AND NEW.addr_place IS NULL AND location.class = 'place' and location.type='houses' THEN - -- Try and find a way that is close roughly parellel to this line - FOR relation IN SELECT place_id FROM placex - WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26 - and st_geometrytype(location.geometry) in ('ST_LineString') - ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0.5))+ - ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,1))) ASC limit 1 - LOOP ---RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation; - NEW.parent_place_id := relation.place_id; - END LOOP; + -- If the way mentions a street or place address, try that for parenting. + IF NEW.parent_place_id IS NULL AND location.street IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.street)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END LOOP; - - END IF; - ---RAISE WARNING 'x2'; - - IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'W' THEN - -- Is this way part of a relation? - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['w'||NEW.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 '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; + IF NEW.parent_place_id IS NULL AND location.addr_place IS NOT NULL THEN + address_street_word_ids := get_name_ids(make_standard_name(location.addr_place)); + IF address_street_word_ids IS NOT NULL THEN + FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP + NEW.parent_place_id := linkedplacex.place_id; + END LOOP; + END IF; END IF; - END LOOP; - END IF; - ---RAISE WARNING 'x3 %',NEW.parent_place_id; - IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; + END LOOP; - IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; - END IF; END IF; --RAISE WARNING 'x4 %',NEW.parent_place_id; @@ -1578,15 +1399,6 @@ BEGIN -- If we didn't find any road fallback to standard method IF NEW.parent_place_id IS NOT NULL THEN - -- Add the street to the address as zero distance to force to front of list --- INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26); - address_havelevel[26] := true; - - -- Import address details from parent, reclculating distance in process --- INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address --- from place_addressline as x join placex on (address_place_id = placex.place_id) --- where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id; - -- Get the details of the parent road select * from search_name where place_id = NEW.parent_place_id INTO location; NEW.calculated_country_code := location.country_code; @@ -1600,11 +1412,12 @@ BEGIN -- Merge address from parent nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector); nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); ---return NEW; - -- Performance, it would be more acurate to do all the rest of the import process but it takes too long + + -- 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 - IF NEW.rank_search <= 25 THEN + IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; @@ -1696,7 +1509,7 @@ BEGIN 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 THEN + 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 @@ -1832,10 +1645,26 @@ BEGIN IF location.rank_address != location_rank_search THEN location_rank_search := location.rank_address; - location_distance := location.distance * 1.5; + IF location.isguess THEN + location_distance := location.distance * 1.5; + ELSE + IF location.rank_address <= 12 THEN + -- for county and above, if we have an area consider that exact + -- (It would be nice to relax the constraint for places close to + -- the boundary but we'd need the exact geometry for that. Too + -- expensive.) + location_distance = 0; + ELSE + -- Below county level remain slightly fuzzy. + location_distance := location.distance * 0.5; + END IF; + END IF; + ELSE + CONTINUE WHEN location.keywords <@ location_keywords; END IF; IF location.distance < location_distance OR NOT location.isguess THEN + location_keywords := location.keywords; location_isaddress := NOT address_havelevel[location.rank_address]; IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN @@ -1924,7 +1753,7 @@ BEGIN -- if we have a name add this to the name search table IF NEW.name IS NOT NULL THEN - IF NEW.rank_search <= 25 THEN + IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.calculated_country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); END IF; @@ -2033,6 +1862,11 @@ BEGIN -- mark for delete UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type; + -- interpolations are special + IF OLD.class = 'place' and OLD.type = 'houses' THEN + UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = 'place' and type = 'address'; + END IF; + RETURN OLD; END; @@ -2059,13 +1893,6 @@ BEGIN --DEBUG: RAISE WARNING '%', existingplacex; END IF; - -- Just block these - lots and pointless - IF NEW.class in ('landuse','natural') and NEW.name is null THEN - -- if the name tag was removed, older versions might still be lurking in the place table - DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type; - RETURN null; - END IF; - IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name, NEW.country_code, now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry); @@ -2087,8 +1914,8 @@ BEGIN -- Handle a place changing type by removing the old data -- My generated 'place' types are causing havok because they overlap with real keys -- TODO: move them to their own special purpose key/class to avoid collisions - IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN - DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses'); + IF existing.osm_type IS NULL THEN + DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class; END IF; --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id; @@ -2207,46 +2034,11 @@ BEGIN END IF; - -- Special case - if we are just adding extra words we hack them into the search_name table rather than reindexing - IF FALSE AND existingplacex.rank_search < 26 - AND coalesce(existing.housenumber, '') = coalesce(NEW.housenumber, '') - AND coalesce(existing.street, '') = coalesce(NEW.street, '') - AND coalesce(existing.addr_place, '') = coalesce(NEW.addr_place, '') - AND coalesce(existing.isin, '') = coalesce(NEW.isin, '') - AND coalesce(existing.postcode, '') = coalesce(NEW.postcode, '') - AND coalesce(existing.country_code, '') = coalesce(NEW.country_code, '') - AND coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') - THEN - - IF NOT update_location_nameonly(existingplacex.place_id, NEW.name) THEN - - IF st_area(NEW.geometry) < 0.5 THEN - UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id - and placex.place_id = place_addressline.place_id and indexed_status = 0 - and (rank_search < 28 or name is not null); - END IF; - - END IF; - - ELSE - - -- Anything else has changed - reindex the lot - IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') - OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') - OR coalesce(existing.street, '') != coalesce(NEW.street, '') - OR coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '') - OR coalesce(existing.isin, '') != coalesce(NEW.isin, '') - OR coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') - OR coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN - - -- performance, can't take the load of re-indexing a whole country / huge area - IF st_area(NEW.geometry) < 0.5 THEN --- UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id --- and placex.place_id = place_addressline.place_id and indexed_status = 0; - END IF; - - END IF; + -- refuse to update multiplpoygons with too many objects, too much of a performance hit + IF ST_NumGeometries(NEW.geometry) > 2000 THEN + RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id; + RETURN NULL; END IF; IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') @@ -2301,6 +2093,11 @@ BEGIN END IF; + -- for interpolations invalidate all nodes on the line + IF NEW.class = 'place' and NEW.type = 'houses' and NEW.osm_type = 'W' THEN + update placex p set indexed_status = 2 from planet_osm_ways w where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes); + END IF; + -- Abort the add (we modified the existing place instead) RETURN NULL; @@ -2333,29 +2130,6 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION get_connected_ways(way_ids INTEGER[]) RETURNS SETOF planet_osm_ways - AS $$ -DECLARE - searchnodes INTEGER[]; - location RECORD; - j INTEGER; -BEGIN - - searchnodes := '{}'; - FOR j IN 1..array_upper(way_ids, 1) LOOP - FOR location IN - select nodes from planet_osm_ways where id = way_ids[j] LIMIT 1 - LOOP - IF not (ARRAY[location.nodes] <@ searchnodes) THEN - searchnodes := searchnodes || location.nodes; - END IF; - END LOOP; - END LOOP; - - RETURN QUERY select * from planet_osm_ways where nodes && searchnodes and NOT ARRAY[id] <@ way_ids; -END; -$$ -LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT AS $$ @@ -2595,86 +2369,10 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox - AS $$ -DECLARE - result place_boundingbox; - numfeatures integer; -BEGIN - select * from place_boundingbox into result where place_id = search_place_id; - IF result.place_id IS NULL THEN --- remove isaddress = true because if there is a matching polygon it always wins - select count(*) from place_addressline where address_place_id = search_place_id into numfeatures; - insert into place_boundingbox select place_id, - ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)), - ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)), - numfeatures, ST_Area(geometry), - geometry as area from location_area where place_id = search_place_id; - select * from place_boundingbox into result where place_id = search_place_id; - END IF; - IF result.place_id IS NULL THEN --- TODO 0.0001 - insert into place_boundingbox select address_place_id, - min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon, - min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat, - count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area, - ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary - from (select * from place_addressline where address_place_id = search_place_id order by cached_rank_address limit 4000) as place_addressline join placex using (place_id) - where address_place_id = search_place_id --- and (isaddress = true OR place_id = search_place_id) - and (st_length(geometry) < 0.01 or place_id = search_place_id) - group by address_place_id limit 1; - select * from place_boundingbox into result where place_id = search_place_id; - END IF; - return result; -END; -$$ -LANGUAGE plpgsql; - --- don't do the operation if it would be slow -CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox - AS $$ -DECLARE - result place_boundingbox; - numfeatures integer; - rank integer; -BEGIN - select * from place_boundingbox into result where place_id = search_place_id; - IF result IS NULL AND rank > 14 THEN - select count(*) from place_addressline where address_place_id = search_place_id and isaddress = true into numfeatures; - insert into place_boundingbox select place_id, - ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),4)),ST_Y(ST_PointN(ExteriorRing(ST_Box2D(geometry)),2)), - ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),1)),ST_X(ST_PointN(ExteriorRing(ST_Box2D(geometry)),3)), - numfeatures, ST_Area(geometry), - geometry as area from location_area where place_id = search_place_id; - select * from place_boundingbox into result where place_id = search_place_id; - END IF; - IF result IS NULL THEN - select rank_search from placex where place_id = search_place_id into rank; - IF rank > 20 THEN --- TODO 0.0001 - insert into place_boundingbox select address_place_id, - min(ST_Y(ST_Centroid(geometry))) as minlon,max(ST_Y(ST_Centroid(geometry))) as maxlon, - min(ST_X(ST_Centroid(geometry))) as minlat,max(ST_X(ST_Centroid(geometry))) as maxlat, - count(*), ST_Area(ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001)) as area, - ST_Buffer(ST_Convexhull(ST_Collect(geometry)),0.0001) as boundary - from place_addressline join placex using (place_id) - where address_place_id = search_place_id - and (isaddress = true OR place_id = search_place_id) - and (st_length(geometry) < 0.01 or place_id = search_place_id) - group by address_place_id limit 1; - select * from place_boundingbox into result where place_id = search_place_id; - END IF; - END IF; - return result; -END; -$$ -LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN AS $$ DECLARE - result place_boundingbox; numfeatures integer; BEGIN update placex set @@ -2787,42 +2485,6 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_word_suggestion(srcword TEXT) RETURNS TEXT - AS $$ -DECLARE - trigramtoken TEXT; - result TEXT; -BEGIN - - trigramtoken := regexp_replace(make_standard_name(srcword),E'([^0-9])\\1+',E'\\1','g'); - SELECT word FROM word WHERE word_trigram like ' %' and word_trigram % trigramtoken ORDER BY similarity(word_trigram, trigramtoken) DESC, word limit 1 into result; - - return result; -END; -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_word_suggestions(srcword TEXT) RETURNS TEXT[] - AS $$ -DECLARE - trigramtoken TEXT; - result TEXT[]; - r RECORD; -BEGIN - - trigramtoken := regexp_replace(make_standard_name(srcword),E'([^0-9])\\1+',E'\\1','g'); - - FOR r IN SELECT word,similarity(word_trigram, trigramtoken) as score FROM word - WHERE word_trigram like ' %' and word_trigram % trigramtoken ORDER BY similarity(word_trigram, trigramtoken) DESC, word limit 4 - LOOP - result[coalesce(array_upper(result,1)+1,1)] := r.word; - END LOOP; - - return result; -END; -$$ -LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT, in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER AS $$ @@ -2954,14 +2616,14 @@ BEGIN wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:'); wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:'); wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:'); - wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:'); + --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:'); wiki_article := replace(wiki_article,' ','_'); - wiki_article_title := trim(split_part(wiki_article, ':', 2)); - IF wiki_article_title IS NULL OR wiki_article_title = '' THEN + IF strpos(wiki_article, ':') IN (3,4) THEN + wiki_article_language := lower(trim(split_part(wiki_article, ':', 1))); + wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1)); + ELSE wiki_article_title := trim(wiki_article); wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END; - ELSE - wiki_article_language := lower(trim(split_part(wiki_article, ':', 1))); END IF; select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance