From: Sarah Hoffmann Date: Mon, 27 Feb 2017 21:30:54 +0000 (+0100) Subject: Merge remote-tracking branch 'upstream/master' X-Git-Tag: deploy~402 X-Git-Url: https://git.openstreetmap.org/nominatim.git/commitdiff_plain/8bc3bb16a116f0152b49b00dff53fb1f37f0fb31?hp=8c3796826b9c3e35bba0b31f9de3ca22d2577870 Merge remote-tracking branch 'upstream/master' --- diff --git a/CMakeLists.txt b/CMakeLists.txt index 71075c82..89624069 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -42,6 +42,17 @@ unset(PostgreSQL_TYPE_INCLUDE_DIR CACHE) set(PostgreSQL_TYPE_INCLUDE_DIR "/usr/include/") find_package(PostgreSQL REQUIRED) include_directories(${PostgreSQL_INCLUDE_DIRS}) +link_directories(${PostgreSQL_LIBRARY_DIRS}) + +find_program(OSMOSIS osmosis) +if (NOT EXISTS "${OSMOSIS}") + set(OSMOSIS_PATH "/nonexistent") + message(WARNING "Osmosis not found (required for updates)") +else() + set(OSMOSIS_PATH "${OSMOSIS}") + message(STATUS "Using osmosis at ${OSMOSIS_PATH}") +endif() + find_program(PG_CONFIG pg_config) execute_process(COMMAND ${PG_CONFIG} --pgxs diff --git a/data/country_name.sql b/data/country_name.sql index 13efe7c1..dc86ef16 100644 --- a/data/country_name.sql +++ b/data/country_name.sql @@ -273,6 +273,7 @@ sr "name"=>"Suriname", "name:ar"=>"سورينام", "name:be"=>"Сурынам", ss "name"=>"South Sudan", "name:ar"=>"جنوب السودان", "name:br"=>"Soudan ar Su", "name:cs"=>"Jižní Súdán", "name:de"=>"Südsudan", "name:en"=>"South Sudan", "name:es"=>"Sudán del Sur", "name:et"=>"Lõuna-Sudaan", "name:fa"=>"سودان جنوبی", "name:fr"=>"Sud-Soudan", "name:hr"=>"Južni Sudan", "name:ku"=>"Sûdana Başûr", "name:ru"=>"Южный Судан", "name:sk"=>"Južný Sudán", "name:sl"=>"Južni Sudan", "name:tl"=>"Timog Sudan", "name:vi"=>"Nam Sudan", "name:zh"=>"南蘇丹", "int_name"=>"South Sudan", "name:ast"=>"Sudán del Sur", "official_name"=>"Republic of South Sudan", "official_name:br"=>"Republik Soudan ar Su", "official_name:cs"=>"Republika Jižní Súdán", "official_name:de"=>"Republik Südsudan", "official_name:en"=>"Republic of South Sudan", "official_name:es"=>"República de Sudán del Sur", "official_name:fr"=>"République du Sud-Soudan", "official_name:hr"=>"Republika Južni Sudan", "official_name:ku"=>"Komara Sûdana Başûr", "official_name:sk"=>"Juhosudánska republika", "official_name:tl"=>"Republika ng Timog Sudan" en 247 sy "name"=>"Sūriyya سوريا", "name:af"=>"Sirië", "name:ar"=>"سوريا", "name:be"=>"Сірыя", "name:br"=>"Siria", "name:ca"=>"Síria", "name:da"=>"Syrien", "name:de"=>"Syrien", "name:el"=>"Συρία", "name:en"=>"Syria", "name:eo"=>"Sirio", "name:es"=>"Siria", "name:fa"=>"سوریه", "name:fi"=>"Syyria", "name:fr"=>"Syrie", "name:fy"=>"Syrje", "name:ga"=>"An tSiria", "name:gd"=>"Siria", "name:he"=>"סוריה", "name:hr"=>"Sirija", "name:hu"=>"Szíria", "name:id"=>"Suriah", "name:is"=>"Sýrland", "name:it"=>"Siria", "name:ja"=>"シリア", "name:la"=>"Syria", "name:lb"=>"Syrien", "name:li"=>"Syrië", "name:lt"=>"Sirija", "name:lv"=>"Sīrija", "name:mn"=>"Сири", "name:nl"=>"Syrië", "name:pl"=>"Syria", "name:pt"=>"Síria", "name:ru"=>"Сирия", "name:sl"=>"Sirija", "name:sv"=>"Syrien", "name:th"=>"ประเทศซีเรีย", "name:tr"=>"Suriye", "name:uk"=>"Сирія", "name:vi"=>"Syria", "name:zh"=>"叙利亚", "name:haw"=>"Suria", "name:zh_py"=>"Xuliya", "name:zh_pyt"=>"Xùlìyà", "official_name:br"=>"Republik Arab Siria", "official_name:ca"=>"República Àrab Siriana", "official_name:el"=>"Αραβική Δημοκρατία της Συρίας", "official_name:gd"=>"Poblachd Arabach Shirianach", "official_name:id"=>"Republik Arab Suriah", "official_name:it"=>"Repubblica Araba di Siria", "official_name:lb"=>"Arabesch Republik Syrien", "official_name:lt"=>"Sirijos Arabų Respublika", "official_name:pl"=>"Syryjska Republika Arabska", "official_name:pt"=>"República Árabe da Síria", "official_name:ru"=>"Сирийская Арабская Республика", "official_name:sv"=>"Arabrepubliken Syrien" ar 104 vn "name"=>"Việt Nam", "name:af"=>"Viëtnam", "name:ar"=>"فييت نام", "name:be"=>"В'етнам", "name:br"=>"Viêt Nam", "name:ca"=>"Vietnam", "name:cy"=>"Fiet Nam", "name:da"=>"Vietnam", "name:de"=>"Vietnam", "name:el"=>"Βιετνάμ", "name:en"=>"Vietnam", "name:eo"=>"Vjetnamio", "name:es"=>"Vietnam", "name:et"=>"Vietnam", "name:fa"=>"ویتنام", "name:fi"=>"Vietnam", "name:fr"=>"Viêt Nam", "name:fy"=>"Fietnam", "name:ga"=>"Vítneam", "name:gd"=>"Bhiet-Nam", "name:he"=>"ויאטנם", "name:hr"=>"Vietnam", "name:hu"=>"Vietnam", "name:hy"=>"Վիետնամ", "name:id"=>"Vietnam", "name:is"=>"Víetnam", "name:it"=>"Vietnam", "name:ja"=>"ベトナム", "name:la"=>"Vietnamia", "name:lb"=>"Vietnam", "name:li"=>"Viëtnam", "name:lt"=>"Vietnamas", "name:lv"=>"Vjetnama", "name:mn"=>"Вьетнам", "name:nl"=>"Vietnam", "name:pl"=>"Wietnam", "name:pt"=>"Vietnã", "name:ru"=>"Вьетнам", "name:sl"=>"Vietnam", "name:sv"=>"Vietnam", "name:th"=>"ประเทศเวียดนาม", "name:tr"=>"Vietnam", "name:uk"=>"В'єтнам", "name:vi"=>"Việt Nam", "name:zh"=>"越南", "name:haw"=>"Wiekanama", "name:zh_py"=>"Yuenan", "name:zh_pyt"=>"Yuènán", "official_name"=>"Cộng Hòa Xã Hội Chủ Nghĩa Việt Nam", "official_name:be"=>"В'етнам ", "official_name:ca"=>"República Socialista del Vietnam", "official_name:el"=>"Σοσιαλιστική Δημοκρατία του Βιετνάμ", "official_name:et"=>"Vietnami Sotsialistlik Vabariik", "official_name:id"=>"Republik Sosialis Vietnam", "official_name:it"=>"Repubblica Socialista del Vietnam", "official_name:ja"=>"ベトナム社会主義共和国", "official_name:lb"=>"Sozialistesch Republik Vietnam", "official_name:pl"=>"Socjalistyczna Republika Wietnamu", "official_name:pt"=>"República Socialista do Vietnã", "official_name:sv"=>"Socialistiska republiken Vietnam" vi 75 +xk "name"=>"Kosova", "name:am"=>"ኮሶቮ", "name:ar"=>"كوسوفو", "name:az"=>"Kosovo", "name:bg"=>"Косово", "name:bn"=>"কসোভো", "name:bo"=>"ཁོ་སོ་ཝོ།", "name:bs"=>"Kosovo", "name:ca"=>"Kosovo", "name:ce"=>"Косово", "name:cv"=>"Косово", "name:da"=>"Kosovo", "name:de"=>"Kosovo", "name:el"=>"Κόσοβο", "name:en"=>"Kosovo", "name:eo"=>"Kosovo", "name:eu"=>"Kosovo", "name:fr"=>"Kosovo", "name:fy"=>"Kosovo", "name:ga"=>"An Chosaiv", "name:gl"=>"Kosovo", "name:he"=>"קוסובו", "name:hr"=>"Kosovo", "name:hu"=>"Koszovó", "name:hy"=>"Կոսովոն", "name:ja"=>"コソボ", "name:ka"=>"კოსოვო", "name:kn"=>"ಕೊಸೊವೊ", "name:ko"=>"코소보", "name:mk"=>"Косово", "name:my"=>"ကိုဆိုဗို", "name:nl"=>"Kosovo", "name:ny"=>"Kosovo", "name:pl"=>"Kosowo", "name:pt"=>"Kosovo", "name:ru"=>"Косово", "name:sk"=>"Kosovo", "name:sl"=>"Kosovo", "name:sq"=>"Kosova", "name:sr"=>"Косово", "name:ta"=>"கொசோவோ", "name:tr"=>"Kosova", "name:uk"=>"Косово", "name:ur"=>"کوسووہ", "name:zh"=>"科索沃", "alt_name"=>"Dardania", "int_name"=>"Kosovo", "name:ceb"=>"Kosovo", "name:kbd"=>"Косово", "name:tzl"=>"Kosove", "name:xmf"=>"კოსოვო", "short_name"=>"Kosova", "name:zh-yue"=>"科索沃", "name:sr-Latn"=>"Kosovo", "official_name"=>"Republika e Kosovës", "short_name:en"=>"Kosovo", "official_name:de"=>"Republik Kosovo", "official_name:eo"=>"Respubliko Kosovo", "official_name:pl"=>"Republika Kosowa", "official_name:sq"=>"Republika e Kosovës" \N 59 \. CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code); diff --git a/lib/Geocode.php b/lib/Geocode.php index 5fc3dc6e..1f4eebef 100644 --- a/lib/Geocode.php +++ b/lib/Geocode.php @@ -1326,6 +1326,7 @@ class Geocode $aOrder[0] .= " SELECT place_id "; $aOrder[0] .= " FROM location_property_osmline "; $aOrder[0] .= " WHERE parent_place_id = search_name.place_id"; + $aOrder[0] .= " AND startnumber is not NULL"; $aOrder[0] .= " AND ".intval($aSearch['sHouseNumber']).">=startnumber "; $aOrder[0] .= " AND ".intval($aSearch['sHouseNumber'])."<=endnumber "; $aOrder[0] .= " LIMIT 1"; @@ -1463,7 +1464,7 @@ class Geocode // do we need to use transliteration and the regex for housenumbers??? //new query for lines, not housenumbers anymore $sSQL = "SELECT distinct place_id FROM location_property_osmline"; - $sSQL .= " WHERE parent_place_id in (".$sPlaceIDs.") and ("; + $sSQL .= " WHERE startnumber is not NULL and parent_place_id in (".$sPlaceIDs.") and ("; if ($searchedHousenumber%2 == 0) { //if housenumber is even, look for housenumber in streets with interpolationtype even or all $sSQL .= "interpolationtype='even'"; @@ -1682,7 +1683,7 @@ class Geocode $sSQL .= " SELECT place_id "; $sSQL .= " FROM location_property_osmline "; $sSQL .= " WHERE place_id in (".join(',', array_keys($aResultPlaceIDs)).")"; - $sSQL .= " AND (30 between $this->iMinAddressRank and $this->iMaxAddressRank)"; + $sSQL .= " AND startnumber is not NULL AND (30 between $this->iMinAddressRank and $this->iMaxAddressRank)"; if (CONST_Debug) var_dump($sSQL); $aFilteredPlaceIDs = chksql($this->oDB->getCol($sSQL)); $tempIDs = array(); diff --git a/lib/ReverseGeocode.php b/lib/ReverseGeocode.php index 8fc22f69..d4e13875 100644 --- a/lib/ReverseGeocode.php +++ b/lib/ReverseGeocode.php @@ -57,7 +57,7 @@ class ReverseGeocode $sSQL .= ' , ST_Distance(linegeo,'.$sPointSQL.') as distance'; $sSQL .= ' FROM location_property_osmline'; $sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', linegeo, '.$fSearchDiam.')'; - $sSQL .= ' and indexed_status = 0 '; + $sSQL .= ' and indexed_status = 0 and startnumber is not NULL '; $sSQL .= ' ORDER BY ST_distance('.$sPointSQL.', linegeo) ASC limit 1'; return chksql( diff --git a/settings/defaults.php b/settings/defaults.php index e34f4db0..0b63a3bd 100644 --- a/settings/defaults.php +++ b/settings/defaults.php @@ -40,7 +40,7 @@ if (isset($_GET['debug']) && $_GET['debug']) @define('CONST_Debug', true); // Paths @define('CONST_Osm2pgsql_Binary', CONST_InstallPath.'/osm2pgsql/osm2pgsql'); -@define('CONST_Osmosis_Binary', '/usr/bin/osmosis'); +@define('CONST_Osmosis_Binary', '@OSMOSIS_PATH@'); @define('CONST_Tiger_Data_Path', CONST_BasePath.'/data/tiger'); @define('CONST_Wikipedia_Data_Path', CONST_BasePath.'/data'); diff --git a/sql/functions.sql b/sql/functions.sql index 4df13e67..dc259710 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -598,7 +598,7 @@ BEGIN 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 + 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 @@ -647,101 +647,27 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION insert_osmline(wayid BIGINT, interpolationtype TEXT, - street TEXT, addr_place TEXT, - defpostalcode TEXT, country_code TEXT, - geom GEOMETRY) -RETURNS INTEGER AS $$ -DECLARE - - newpoints INTEGER; - waynodes BIGINT[]; - nodeid BIGINT; - prevnode RECORD; - nextnode RECORD; - startnumber INTEGER; - endnumber INTEGER; - housenum INTEGER; - linegeo GEOMETRY; - splitline GEOMETRY; - sectiongeo GEOMETRY; - pointgeo GEOMETRY; - place_centroid GEOMETRY; - calculated_country_code VARCHAR(2); - partition INTEGER; - geometry_sector INTEGER; - +CREATE OR REPLACE FUNCTION osmline_insert() RETURNS TRIGGER + AS $$ BEGIN - place_centroid := ST_PointOnSurface(geom); - calculated_country_code := lower(get_country_code(place_centroid)); - partition := get_partition(calculated_country_code); - geometry_sector := geometry_sector(partition, place_centroid); - - IF interpolationtype != 'odd' AND interpolationtype != 'even' AND interpolationtype!='all' THEN - -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported - RETURN 0; - END IF; - - select nodes from planet_osm_ways where id = wayid INTO waynodes; - - IF array_upper(waynodes, 1) IS NULL THEN - RETURN 0; - END IF; - - linegeo := geom; - startnumber := NULL; - - FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP + NEW.place_id := nextval('seq_place'); + NEW.indexed_date := now(); - select * from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT - and housenumber is not NULL limit 1 INTO nextnode; - --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id; - IF nextnode.osm_id IS NOT NULL THEN - --RAISE NOTICE 'place_id is not null'; - 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; + IF NEW.indexed_status IS NULL THEN + IF NEW.interpolationtype NOT IN ('odd', 'even', 'all') THEN + -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported + RETURN NULL; END IF; - endnumber := substring(nextnode.housenumber,'[0-9]+')::integer; - - IF startnumber IS NOT NULL AND endnumber IS NOT NULL - AND startnumber != endnumber - AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN - IF (startnumber > endnumber) THEN - housenum := endnumber; - endnumber := startnumber; - startnumber := housenum; - sectiongeo := ST_Reverse(sectiongeo); - END IF; - - insert into location_property_osmline - values (sectiongeo, nextval('seq_place'), partition, wayid, NULL, - startnumber, endnumber, interpolationtype, - coalesce(street, prevnode.street, nextnode.street), - coalesce(addr_place, prevnode.addr_place, nextnode.addr_place), - coalesce(defpostalcode, prevnode.postcode, nextnode.postcode), - calculated_country_code, geometry_sector, 2, now()); - END IF; + NEW.indexed_status := 1; --STATUS_NEW - -- 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 0; - END IF; + NEW.calculated_country_code := lower(get_country_code(NEW.linegeo)); - startnumber := substring(nextnode.housenumber,'[0-9]+')::integer; - prevnode := nextnode; - END IF; - END LOOP; + NEW.partition := get_partition(NEW.calculated_country_code); + NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo); + END IF; - RETURN 1; + RETURN NEW; END; $$ LANGUAGE plpgsql; @@ -759,19 +685,6 @@ DECLARE classtable TEXT; line RECORD; BEGIN - --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id; - - -- ignore interpolated addresses, not necessary anymore, cause interpolated addresses are now in location_property_osmline - 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; - END IF; - --DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; NEW.place_id := nextval('seq_place'); @@ -1069,6 +982,18 @@ TRIGGER AS $$ DECLARE place_centroid GEOMETRY; + waynodes BIGINT[]; + prevnode RECORD; + nextnode RECORD; + startnumber INTEGER; + endnumber INTEGER; + housenum INTEGER; + linegeo GEOMETRY; + splitline GEOMETRY; + sectiongeo GEOMETRY; + street TEXT; + addr_place TEXT; + postcode TEXT; BEGIN -- deferred delete IF OLD.indexed_status = 100 THEN @@ -1080,19 +1005,106 @@ BEGIN RETURN NEW; END IF; - -- do the reparenting: (finally here, because ALL places in placex, that are needed for reparenting, need to be up to date) - -- (the osm interpolationline in location_property_osmline was marked for reparenting in placex_insert/placex_delete with index_status = 1 or 2 (1 inset, 2 delete) + -- do the reparenting: (finally here, because ALL places in placex, + -- that are needed for reparenting, need to be up to date) + -- (the osm interpolationline in location_property_osmline was marked for + -- reparenting in placex_insert/placex_delete with index_status = 1 or 2 (1 inset, 2 delete) -- => index.c: sets index_status back to 0 -- => triggers this function) place_centroid := ST_PointOnSurface(NEW.linegeo); - -- marking descendants for reparenting is not needed, because there are actually no descendants for interpolation lines + -- marking descendants for reparenting is not needed, because there are + -- actually no descendants for interpolation lines NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.street, NEW.addr_place, NEW.partition, place_centroid, NEW.linegeo); - return NEW; + + -- if we are just updating then our work is done + IF OLD.indexed_status != 1 THEN + return NEW; + END IF; + + -- otherwise split the line as necessary + select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes; + + IF array_upper(waynodes, 1) IS NULL THEN + RETURN 0; + END IF; + + linegeo := NEW.linegeo; + startnumber := NULL; + street := NEW.street; + addr_place := NEW.addr_place; + postcode := NEW.postcode; + + FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP + + select * from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT + and housenumber is not NULL limit 1 INTO nextnode; + --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id; + IF nextnode.osm_id IS NOT NULL THEN + --RAISE NOTICE 'place_id is not null'; + 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 IS NOT NULL AND endnumber IS NOT NULL + AND startnumber != endnumber + AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN + + IF (startnumber > endnumber) THEN + housenum := endnumber; + endnumber := startnumber; + startnumber := housenum; + sectiongeo := ST_Reverse(sectiongeo); + END IF; + + IF NEW.startnumber IS NULL THEN + NEW.startnumber := startnumber; + NEW.endnumber := endnumber; + NEW.linegeo := sectiongeo; + NEW.street := coalesce(street, prevnode.street, nextnode.street); + NEW.addr_place := coalesce(addr_place, prevnode.addr_place, nextnode.addr_place); + NEW.postcode := coalesce(postcode, prevnode.postcode, nextnode.postcode); + ELSE + insert into location_property_osmline + (linegeo, partition, osm_id, parent_place_id, + startnumber, endnumber, interpolationtype, + street, addr_place, postcode, calculated_country_code, + geometry_sector, indexed_status) + values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id, + startnumber, endnumber, NEW.interpolationtype, + coalesce(street, prevnode.street, nextnode.street), + coalesce(addr_place, prevnode.addr_place, nextnode.addr_place), + coalesce(postcode, prevnode.postcode, nextnode.postcode), + NEW.calculated_country_code, NEW.geometry_sector, 0); + END IF; + END IF; + + -- 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 NEW; + END IF; + + startnumber := substring(nextnode.housenumber,'[0-9]+')::integer; + prevnode := nextnode; + END IF; + END LOOP; + + RETURN NEW; END; $$ LANGUAGE plpgsql; + + CREATE OR REPLACE FUNCTION placex_update() RETURNS TRIGGER AS $$ @@ -1924,7 +1936,7 @@ BEGIN RETURN null; END IF; - -- decide, whether it is an osm interpolation line => insert_osmline, or else just insert into placex + -- decide, whether it is an osm interpolation line => insert intoosmline, or else just placex IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN -- Have we already done this place? select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing; @@ -1949,11 +1961,16 @@ BEGIN 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); - -- insert new line into location_property_osmline, use function insert_osmline + + + INSERT INTO location_property_osmline + (osm_id, interpolationtype, street, + addr_place, postcode, calculated_country_code, linegeo) + VALUES (NEW.osm_id, NEW.housenumber, NEW.street, + NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry); IF existing.osm_type IS NULL THEN - i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry); return NEW; END IF; @@ -1978,8 +1995,6 @@ BEGIN admin_level = NEW.admin_level, geometry = NEW.geometry where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type; - - i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry); END IF; RETURN NULL; diff --git a/sql/tables.sql b/sql/tables.sql index 19398eb5..8cc2c831 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -167,6 +167,8 @@ 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 diff --git a/test/bdd/.behaverc b/test/bdd/.behaverc new file mode 100644 index 00000000..32aa6dfa --- /dev/null +++ b/test/bdd/.behaverc @@ -0,0 +1,3 @@ +[behave] +show_skipped=False +tags=~@Fail diff --git a/test/bdd/api/search/queries.feature b/test/bdd/api/search/queries.feature index 0074e334..c9b33701 100644 --- a/test/bdd/api/search/queries.feature +++ b/test/bdd/api/search/queries.feature @@ -58,6 +58,7 @@ Feature: Search queries | 30 | # https://trac.openstreetmap.org/ticket/5094 + @Fail Scenario: housenumbers are ordered by complete match first When sending json search query "6395 geminis, montevideo" with address Then result addresses contain diff --git a/test/bdd/db/update/interpolation.feature b/test/bdd/db/update/interpolation.feature index 7dd5bdc0..0eaa97fd 100644 --- a/test/bdd/db/update/interpolation.feature +++ b/test/bdd/db/update/interpolation.feature @@ -2,7 +2,6 @@ Feature: Update of address interpolations Test the interpolated address are updated correctly - @wip Scenario: new interpolation added to existing street Given the scene parallel-road And the places @@ -267,3 +266,92 @@ Feature: Update of address interpolations Then W1 expands to interpolation | parent_place_id | start | end | | W2 | 2 | 6 | + + @Fail + Scenario: housenumber added in middle of interpolation + Given the grid + | 1 | | | | | 2 | + | 3 | | | 4 | | 5 | + And the places + | osm | class | type | name | geometry | + | W1 | highway | unclassified | Cloud Street | 1, 2 | + And the ways + | id | nodes | + | 2 | 3,4,5 | + And the places + | osm | class | type | housenr | geometry | + | W2 | place | houses | even | 3,4,5 | + And the places + | osm | class | type | housenr | + | N3 | place | house | 2 | + | N5 | place | house | 10 | + When importing + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 10 | 3,4,5 | + When updating places + | osm | class | type | housenr | + | N4 | place | house | 6 | + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 6 | 3,4 | + | W1 | 6 | 10 | 4,5 | + + @Fail + Scenario: housenumber removed in middle of interpolation + Given the grid + | 1 | | | | | 2 | + | 3 | | | 4 | | 5 | + And the places + | osm | class | type | name | geometry | + | W1 | highway | unclassified | Cloud Street | 1, 2 | + And the ways + | id | nodes | + | 2 | 3,4,5 | + And the places + | osm | class | type | housenr | geometry | + | W2 | place | houses | even | 3,4,5 | + And the places + | osm | class | type | housenr | + | N3 | place | house | 2 | + | N4 | place | house | 6 | + | N5 | place | house | 10 | + When importing + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 6 | 3,4 | + | W1 | 6 | 10 | 4,5 | + When marking for delete N4 + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 10 | 3,4,5 | + + @Fail + Scenario: Change the start housenumber + Given the grid + | 1 | | 2 | + | 3 | | 4 | + And the places + | osm | class | type | name | geometry | + | W1 | highway | unclassified | Cloud Street | 1, 2 | + And the ways + | id | nodes | + | 2 | 3,4 | + And the places + | osm | class | type | housenr | geometry | + | W2 | place | houses | even | 3,4 | + And the places + | osm | class | type | housenr | + | N3 | place | house | 2 | + | N4 | place | house | 6 | + When importing + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 6 | 3,4 | + When updating places + | osm | class | type | housenr | + | N4 | place | house | 8 | + Then W2 expands to interpolation + | parent_place_id | start | end | geometry | + | W1 | 2 | 8 | 3,4 | + diff --git a/test/bdd/environment.py b/test/bdd/environment.py index 6411d011..58494deb 100644 --- a/test/bdd/environment.py +++ b/test/bdd/environment.py @@ -40,6 +40,7 @@ class NominatimEnvironment(object): def write_nominatim_config(self, dbname): f = open(self.local_settings_file, 'w') f.write("= 0: - out = self.get_scene_geometry(scene, geom) - elif geom.find(',') < 0: - out = "'POINT(%s)'::geometry" % geom + return "ST_SetSRID(%s, 4326)" % self.get_scene_geometry(scene, geom) + + if geom.find(',') < 0: + out = "POINT(%s)" % self.mk_wkt_point(geom) elif geom.find('(') < 0: - out = "'LINESTRING(%s)'::geometry" % geom + line = ','.join([self.mk_wkt_point(x) for x in geom.split(',')]) + out = "LINESTRING(%s)" % line else: - out = "'POLYGON(%s)'::geometry" % geom + inner = geom.strip('() ') + line = ','.join([self.mk_wkt_point(x) for x in inner.split(',')]) + out = "POLYGON((%s))" % line + + return "ST_SetSRID('%s'::geometry, 4326)" % out - return "ST_SetSRID(%s, 4326)" % out + def mk_wkt_point(self, point): + geom = point.strip() + if geom.find(' ') >= 0: + return geom + else: + pt = self.grid_node(int(geom)) + assert_is_not_none(pt, "Point not found in grid") + return "%f %f" % pt def get_scene_geometry(self, default_scene, name): geoms = [] @@ -196,6 +211,15 @@ class OSMDataFactory(object): return scene + def clear_grid(self): + self.grid = {} + + def add_grid_node(self, nodeid, x, y): + self.grid[nodeid] = (x, y) + + def grid_node(self, nodeid): + return self.grid.get(nodeid) + def before_all(context): # logging setup diff --git a/test/bdd/steps/db_ops.py b/test/bdd/steps/db_ops.py index 3c5c5632..fa66cd4d 100644 --- a/test/bdd/steps/db_ops.py +++ b/test/bdd/steps/db_ops.py @@ -61,8 +61,11 @@ class PlaceColumn: for _ in range(int(random.random()*30)))) if self.columns['osm_type'] == 'N' and self.geometry is None: - self.geometry = "ST_SetSRID(ST_Point(%f, %f), 4326)" % ( - random.random()*360 - 180, random.random()*180 - 90) + pt = self.context.osm.grid_node(self.columns['osm_id']) + if pt is None: + pt = (random.random()*360 - 180, random.random()*180 - 90) + + self.geometry = "ST_SetSRID(ST_Point(%f, %f), 4326)" % pt else: assert_is_not_none(self.geometry, "Geometry missing") query = 'INSERT INTO place (%s, geometry) values(%s, %s)' % ( @@ -216,9 +219,13 @@ def import_and_index_data_from_place_table(context): geometry) select * from place where not (class='place' and type='houses' and osm_type='W')""") cur.execute( - """select insert_osmline (osm_id, housenumber, street, addr_place, - postcode, country_code, geometry) - from place where class='place' and type='houses' and osm_type='W'""") + """insert into location_property_osmline + (osm_id, interpolationtype, street, addr_place, + postcode, calculated_country_code, linegeo) + SELECT osm_id, housenumber, street, addr_place, + postcode, country_code, geometry from place + WHERE class='place' and type='houses' and osm_type='W' + and ST_GeometryType(geometry) = 'ST_LineString'""") context.db.commit() context.nominatim.run_setup_script('index', 'index-noanalyse') @@ -373,7 +380,8 @@ def check_location_property_osmline(context, oid, neg): eq_('W', nid.typ, "interpolation must be a way") cur.execute("""SELECT *, ST_AsText(linegeo) as geomtxt - FROM location_property_osmline WHERE osm_id = %s""", + FROM location_property_osmline + WHERE osm_id = %s AND startnumber IS NOT NULL""", (nid.oid, )) if neg: diff --git a/test/bdd/steps/osm_data.py b/test/bdd/steps/osm_data.py index 926fb9ab..d0d8d892 100644 --- a/test/bdd/steps/osm_data.py +++ b/test/bdd/steps/osm_data.py @@ -4,16 +4,52 @@ import random import os from nose.tools import * # for assert functions +@given(u'the (\d+ )?grid') +def define_node_grid(context, grid_step): + """ + Define a grid of node positions. + """ + if grid_step is not None: + grid_step = int(grd_step.strip()) + else: + grid_step = 0.00001 + + context.osm.clear_grid() + + i = 0 + for h in context.table.headings: + if h.isdigit(): + context.osm.add_grid_node(int(h), 0, i) + i += grid_step + + x = grid_step + for r in context.table: + y = 0 + for h in r: + if h.isdigit(): + context.osm.add_grid_node(int(h), x, y) + y += grid_step + x += grid_step + + @when(u'loading osm data') def load_osm_file(context): + """ + Load the given data into a freshly created test data using osm2pgsql. + No further indexing is done. + The data is expected as attached text in OPL format. + """ # create a OSM file in /tmp and import it with tempfile.NamedTemporaryFile(dir='/tmp', suffix='.opl', delete=False) as fd: fname = fd.name for line in context.text.splitlines(): if line.startswith('n') and line.find(' x') < 0: - line += " x%d y%d" % (random.random() * 360 - 180, - random.random() * 180 - 90) + coord = context.osm.grid_node(int(line[1:].split(' ')[0])) + if coord is None: + coord = (random.random() * 360 - 180, + random.random() * 180 - 90) + line += " x%f y%f" % coord fd.write(line.encode('utf-8')) fd.write(b'\n') @@ -33,12 +69,26 @@ def load_osm_file(context): @when(u'updating osm data') def update_from_osm_file(context): + """ + Update a database previously populated with 'loading osm data'. + Needs to run indexing on the existing data first to yield the correct result. + + The data is expected as attached text in OPL format. + """ context.nominatim.run_setup_script('create-functions', 'create-partition-functions') cur = context.db.cursor() cur.execute("""insert into placex (osm_type, osm_id, class, type, name, admin_level, housenumber, street, addr_place, isin, postcode, country_code, extratags, geometry) select * from place""") + cur.execute( + """insert into location_property_osmline + (osm_id, interpolationtype, street, addr_place, + postcode, calculated_country_code, linegeo) + SELECT osm_id, housenumber, street, addr_place, + postcode, country_code, geometry from place + WHERE class='place' and type='houses' and osm_type='W' + and ST_GeometryType(geometry) = 'ST_LineString'""") context.db.commit() context.nominatim.run_setup_script('index', 'index-noanalyse') context.nominatim.run_setup_script('create-functions', 'create-partition-functions', diff --git a/utils/setup.php b/utils/setup.php index e4e91674..87c1dd07 100755 --- a/utils/setup.php +++ b/utils/setup.php @@ -373,8 +373,11 @@ if ($aCMDResult['load-data'] || $aCMDResult['all']) { } // last thread for interpolation lines $aDBInstances[$iLoadThreads] =& getDB(true); - $sSQL = 'select insert_osmline (osm_id, housenumber, street, addr_place, postcode, country_code, '; - $sSQL .= 'geometry) from place where '; + $sSQL = 'insert into location_property_osmline'; + $sSQL .= ' (osm_id, interpolationtype, street, addr_place,'; + $sSQL .= ' postcode, calculated_country_code, linegeo)'; + $sSQL .= ' SELECT osm_id, housenumber, street, addr_place,'; + $sSQL .= ' postcode, country_code, geometry from place where '; $sSQL .= "class='place' and type='houses' and osm_type='W' and ST_GeometryType(geometry) = 'ST_LineString'"; if ($aCMDResult['verbose']) echo "$sSQL\n"; if (!pg_send_query($aDBInstances[$i]->connection, $sSQL)) fail(pg_last_error($oDB->connection)); @@ -475,7 +478,8 @@ if ($aCMDResult['calculate-postcodes'] || $aCMDResult['all']) { $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,calculated_country_code,"; $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select calculated_country_code,postcode,"; $sSQL .= "avg(st_x(st_centroid(geometry))) as x,avg(st_y(st_centroid(geometry))) as y "; - $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x"; + $sSQL .= "from placex where postcode is not null group by calculated_country_code,postcode) as x "; + $sSQL .= "where ST_Point(x,y) is not null"; if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection)); if (CONST_Use_Extra_US_Postcodes) {