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
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);
$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";
// 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'";
$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();
$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(
// 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');
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
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;
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');
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
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 $$
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;
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;
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;
-- 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
--- /dev/null
+[behave]
+show_skipped=False
+tags=~@Fail
| 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
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
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 |
+
def write_nominatim_config(self, dbname):
f = open(self.local_settings_file, 'w')
f.write("<?php\n @define('CONST_Database_DSN', 'pgsql://@/%s');\n" % dbname)
+ f.write("@define('CONST_Osm2pgsql_Flatnode_File', null);\n")
f.close()
def cleanup(self):
self.scene_path = os.environ.get('SCENE_PATH',
os.path.join(scriptpath, '..', 'scenes', 'data'))
self.scene_cache = {}
+ self.clear_grid()
def parse_geometry(self, geom, scene):
if geom.find(':') >= 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 = []
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
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)' % (
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')
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:
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')
@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',
}
// 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));
$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) {
projects / nominatim.git / commitdiff