$sSQL .= " rank_address,";
$sSQL .= " min(place_id) AS place_id, ";
$sSQL .= " min(parent_place_id) AS parent_place_id, ";
- $sSQL .= " calculated_country_code AS country_code, ";
+ $sSQL .= " country_code, ";
$sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) AS langaddress,";
$sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL) AS placename,";
$sSQL .= " get_name_by_language(name, ARRAY['ref']) AS ref,";
$sSQL .= " admin_level, ";
$sSQL .= " rank_search, ";
$sSQL .= " rank_address, ";
- $sSQL .= " calculated_country_code, ";
+ $sSQL .= " country_code, ";
$sSQL .= " importance, ";
if (!$this->bDeDupe) $sSQL .= "place_id,";
$sSQL .= " langaddress, ";
$sSQL .= " 30 AS rank_address, ";
$sSQL .= " min(place_id) as place_id, ";
$sSQL .= " min(parent_place_id) AS parent_place_id, ";
- $sSQL .= " calculated_country_code AS country_code, ";
+ $sSQL .= " country_code, ";
$sSQL .= " get_address_by_language(place_id, housenumber_for_place, $sLanguagePrefArraySQL) AS langaddress, ";
$sSQL .= " null AS placename, ";
$sSQL .= " null AS ref, ";
$sSQL .= " SELECT ";
$sSQL .= " osm_id, ";
$sSQL .= " place_id, ";
- $sSQL .= " calculated_country_code, ";
+ $sSQL .= " country_code, ";
$sSQL .= " CASE "; // interpolate the housenumbers here
$sSQL .= " WHEN startnumber != endnumber ";
$sSQL .= " THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ";
$sSQL .= " osm_id, ";
$sSQL .= " place_id, ";
$sSQL .= " housenumber_for_place, ";
- $sSQL .= " calculated_country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique
+ $sSQL .= " country_code "; //is this group by really needed?, place_id + housenumber (in combination) are unique
if (!$this->bDeDupe) $sSQL .= ", place_id ";
if (CONST_Use_Aux_Location_data) {
if ($aSearch['sCountryCode'] && !$aSearch['sClass'] && !$aSearch['sHouseNumber']) {
// Just looking for a country by code - look it up
if (4 >= $this->iMinAddressRank && 4 <= $this->iMaxAddressRank) {
- $sSQL = "SELECT place_id FROM placex WHERE calculated_country_code='".$aSearch['sCountryCode']."' AND rank_search = 4";
- if ($sCountryCodesSQL) $sSQL .= " AND calculated_country_code in ($sCountryCodesSQL)";
+ $sSQL = "SELECT place_id FROM placex WHERE country_code='".$aSearch['sCountryCode']."' AND rank_search = 4";
+ if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)";
if ($bBoundingBoxSearch)
$sSQL .= " AND _st_intersects($this->sViewboxSmallSQL, geometry)";
$sSQL .= " ORDER BY st_area(geometry) DESC LIMIT 1";
$sSQL = "SELECT place_id FROM place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct";
if ($sCountryCodesSQL) $sSQL .= " JOIN placex USING (place_id)";
$sSQL .= " WHERE st_contains($this->sViewboxSmallSQL, ct.centroid)";
- if ($sCountryCodesSQL) $sSQL .= " AND calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)";
if (sizeof($this->aExcludePlaceIDs)) {
$sSQL .= " AND place_id not in (".join(',', $this->aExcludePlaceIDs).")";
}
$sSQL = "SELECT place_id FROM place_classtype_".$aSearch['sClass']."_".$aSearch['sType']." ct";
if ($sCountryCodesSQL) $sSQL .= " join placex using (place_id)";
$sSQL .= " WHERE ST_Contains($this->sViewboxLargeSQL, ct.centroid)";
- if ($sCountryCodesSQL) $sSQL .= " AND calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)";
if ($this->sViewboxCentreSQL) $sSQL .= " ORDER BY ST_Distance($this->sViewboxCentreSQL, ct.centroid) ASC";
$sSQL .= " LIMIT $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
$sSQL .= " AND type='".$aSearch['sType']."'";
$sSQL .= " AND ST_Contains($this->sViewboxSmallSQL, geometry) ";
$sSQL .= " AND linked_place_id is null";
- if ($sCountryCodesSQL) $sSQL .= " AND calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)";
if ($this->sViewboxCentreSQL) $sSQL .= " ORDER BY ST_Distance($this->sViewboxCentreSQL, centroid) ASC";
$sSQL .= " LIMIT $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
$sSQL .= " AND class='".$aSearch['sClass']."' ";
$sSQL .= " AND type='".$aSearch['sType']."'";
$sSQL .= " AND linked_place_id is null";
- if ($sCountryCodesSQL) $sSQL .= " AND calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " AND country_code in ($sCountryCodesSQL)";
$sSQL .= " ORDER BY rank_search ASC ";
$sSQL .= " LIMIT $this->iLimit";
if (CONST_Debug) var_dump($sSQL);
if (sizeof($this->aExcludePlaceIDs)) {
$sSQL .= " and l.place_id not in (".join(',', $this->aExcludePlaceIDs).")";
}
- if ($sCountryCodesSQL) $sSQL .= " and lp.calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " and lp.country_code in ($sCountryCodesSQL)";
if ($sOrderBySQL) $sSQL .= "order by ".$sOrderBySQL." asc";
if ($this->iOffset) $sSQL .= " offset $this->iOffset";
$sSQL .= " limit $this->iLimit";
if (sizeof($this->aExcludePlaceIDs)) {
$sSQL .= " AND l.place_id not in (".join(',', $this->aExcludePlaceIDs).")";
}
- if ($sCountryCodesSQL) $sSQL .= " AND l.calculated_country_code in ($sCountryCodesSQL)";
+ if ($sCountryCodesSQL) $sSQL .= " AND l.country_code in ($sCountryCodesSQL)";
if ($sOrderBy) $sSQL .= "ORDER BY ".$OrderBysSQL." ASC";
if ($this->iOffset) $sSQL .= " OFFSET $this->iOffset";
$sSQL .= " limit $this->iLimit";
$bIsInterpolation = $sType == 'interpolation';
if ($bIsTiger) {
- $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, null as street, null as isin, postcode,";
+ $sSQL = "select place_id,partition, 'T' as osm_type, place_id as osm_id, 'place' as class, 'house' as type, null as admin_level, housenumber, postcode,";
$sSQL .= " 'us' as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
- $sSQL .= " coalesce(null,0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, 'us' as calculated_country_code, ";
+ $sSQL .= " coalesce(null,0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, 'us' as country_code, ";
$sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";
$sSQL .= " null as placename,";
$sSQL .= " null as ref,";
$sSQL .= " END as housenumber";
$sSQL .= " from location_property_tiger where place_id = ".$iPlaceID.") as blub1) as blub2";
} elseif ($bIsInterpolation) {
- $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, null as street, null as isin, postcode,";
- $sSQL .= " calculated_country_code as country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
- $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, calculated_country_code, ";
+ $sSQL = "select place_id, partition, 'W' as osm_type, osm_id, 'place' as class, 'house' as type, null admin_level, housenumber, postcode,";
+ $sSQL .= " country_code, parent_place_id, null as linked_place_id, 30 as rank_address, 30 as rank_search,";
+ $sSQL .= " (0.75-(30::float/40)) as importance, null as indexed_status, null as indexed_date, null as wikipedia, country_code, ";
$sSQL .= " get_address_by_language(place_id, housenumber, $sLanguagePrefArraySQL) as langaddress,";
$sSQL .= " null as placename,";
$sSQL .= " null as ref,";
// and not interpolated
} else {
$sSQL = "select placex.place_id, partition, osm_type, osm_id, class,";
- $sSQL .= " type, admin_level, housenumber, street, isin, postcode, country_code,";
+ $sSQL .= " type, admin_level, housenumber, postcode, country_code,";
$sSQL .= " parent_place_id, linked_place_id, rank_address, rank_search, ";
- $sSQL .= " coalesce(importance,0.75-(rank_search::float/40)) as importance, indexed_status, indexed_date, wikipedia, calculated_country_code, ";
+ $sSQL .= " coalesce(importance,0.75-(rank_search::float/40)) as importance, indexed_status, indexed_date, wikipedia, country_code, ";
$sSQL .= " get_address_by_language(place_id, -1, $sLanguagePrefArraySQL) as langaddress,";
$sSQL .= " get_name_by_language(name, $sLanguagePrefArraySQL) as placename,";
$sSQL .= " get_name_by_language(name, ARRAY['ref']) as ref,";
$iMaxRank = 26;
}
- $sSQL = 'select place_id,parent_place_id,rank_search,calculated_country_code';
+ $sSQL = 'select place_id,parent_place_id,rank_search,country_code';
$sSQL .= ' FROM placex';
$sSQL .= ' WHERE ST_DWithin('.$sPointSQL.', geometry, '.$fSearchDiam.')';
$sSQL .= ' and rank_search != 28 and rank_search >= '.$iMaxRank;
);
$iPlaceID = $aPlace['place_id'];
$iParentPlaceID = $aPlace['parent_place_id'];
- $bIsInUnitedStates = ($aPlace['calculated_country_code'] == 'us');
+ $bIsInUnitedStates = ($aPlace['country_code'] == 'us');
}
// If a house was found make sure there isn't an interpolation line
-Subproject commit 45b5ce62f0b31bd4e8ffcb854e7cd0bba88f3496
+Subproject commit 00841fc1b884b3b29fa3e661f7ac884464a81b26
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.address 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 placex.address is not null
+ and (placex.address ? 'street' or placex.address ? 'place')
and indexed_status < 100
limit 1 LOOP
- addr_street = location.street;
- addr_place = location.addr_place;
+ addr_street = location.address->'street';
+ addr_place = location.address->'place';
END LOOP;
END IF;
NEW.indexed_date := now();
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;
+ IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
+ OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN
+ -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
+ RETURN NULL;
END IF;
NEW.indexed_status := 1; --STATUS_NEW
+ NEW.country_code := lower(get_country_code(NEW.linegeo));
- NEW.calculated_country_code := lower(get_country_code(NEW.linegeo));
-
- NEW.partition := get_partition(NEW.calculated_country_code);
+ NEW.partition := get_partition(NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.linegeo);
END IF;
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
- NEW.calculated_country_code := lower(get_country_code(NEW.geometry));
+ NEW.country_code := lower(get_country_code(NEW.geometry));
- NEW.partition := get_partition(NEW.calculated_country_code);
+ NEW.partition := get_partition(NEW.country_code);
NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
-- copy 'name' to or from the default language (if there is a default language)
IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
- default_language := get_country_language_code(NEW.calculated_country_code);
+ default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
END IF;
END IF;
- IF NEW.admin_level > 15 THEN
- NEW.admin_level := 15;
- END IF;
-
- IF NEW.housenumber IS NOT NULL THEN
- i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
- END IF;
-
IF NEW.osm_type = 'X' THEN
-- E'X'ternal records should already be in the right format so do nothing
ELSE
-- By doing in postgres we have the country available to us - currently only used for postcode
IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
- IF NEW.postcode IS NULL THEN
+ IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN
-- most likely just a part of a multipolygon postcode boundary, throw it away
RETURN NULL;
END IF;
+ NEW.postcode := NEW.address->'postcode';
NEW.name := hstore('ref', NEW.postcode);
- IF NEW.calculated_country_code = 'gb' THEN
+ IF NEW.country_code = 'gb' THEN
IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
NEW.rank_search := 25;
NEW.rank_address := 5;
END IF;
- ELSEIF NEW.calculated_country_code = 'sg' THEN
+ ELSEIF NEW.country_code = 'sg' THEN
IF NEW.postcode ~ '^([0-9]{6})$' THEN
NEW.rank_search := 25;
NEW.rank_address := 11;
END IF;
- ELSEIF NEW.calculated_country_code = 'de' THEN
+ ELSEIF NEW.country_code = 'de' THEN
IF NEW.postcode ~ '^([0-9]{5})$' THEN
NEW.rank_search := 21;
IF NEW.type in ('continent') THEN
NEW.rank_search := 2;
NEW.rank_address := NEW.rank_search;
- NEW.calculated_country_code := NULL;
+ NEW.country_code := NULL;
ELSEIF NEW.type in ('sea') THEN
NEW.rank_search := 2;
NEW.rank_address := 0;
- NEW.calculated_country_code := NULL;
+ NEW.country_code := NULL;
ELSEIF NEW.type in ('country') THEN
NEW.rank_search := 4;
NEW.rank_address := NEW.rank_search;
-- a country code make no sense below rank 4 (country)
IF NEW.rank_search < 4 THEN
- NEW.calculated_country_code := NULL;
+ NEW.country_code := NULL;
END IF;
-- Block import below rank 22
-- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null));
+ AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));
update placex set indexed_status = 2 where (st_covers(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
- AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and addr_place is not null));
+ AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (NEW.rank_search >= 16 and address ? 'place'));
END IF;
ELSE
-- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
update location_property_osmline set indexed_status = 2 where indexed_status = 0 and ST_DWithin(location_property_osmline.linegeo, NEW.geometry, diameter);
ELSEIF NEW.rank_search >= 16 THEN
-- up to rank 16, street-less addresses may need reparenting
- update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or addr_place is not null);
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null or address ? 'place');
ELSE
-- for all other places the search terms may change as well
update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
linegeo GEOMETRY;
splitline GEOMETRY;
sectiongeo GEOMETRY;
- street TEXT;
- addr_place TEXT;
postcode TEXT;
+ seg_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)
- -- => index.c: sets index_status back to 0
- -- => triggers this function)
+ NEW.interpolationtype = NEW.address->'interpolation';
+
place_centroid := ST_PointOnSurface(NEW.linegeo);
- -- 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.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.address->'street',
+ NEW.address->'place',
NEW.partition, place_centroid, NEW.linegeo);
- -- 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 NEW;
+ IF NEW.address is not NULL and NEW.address ? 'postcode' THEN
+ NEW.postcode = NEW.address->'postcode';
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;
+ -- if the line was newly inserted, split the line as necessary
+ IF OLD.indexed_status = 1 THEN
+ select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
+
+ IF array_upper(waynodes, 1) IS NULL THEN
+ RETURN NEW;
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
+ linegeo := NEW.linegeo;
+ startnumber := NULL;
+ postcode := NEW.postcode;
+
+ FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
+
+ select osm_id, address, geometry
+ from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
+ and address is not NULL and address ? 'housenumber' 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.address->'housenumber','[0-9]+')::integer;
- IF (startnumber > endnumber) THEN
- housenum := endnumber;
- endnumber := startnumber;
- startnumber := housenum;
- sectiongeo := ST_Reverse(sectiongeo);
- END IF;
+ IF startnumber IS NOT NULL AND endnumber IS NOT NULL
+ AND startnumber != endnumber
+ AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
- 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;
+ IF (startnumber > endnumber) THEN
+ housenum := endnumber;
+ endnumber := startnumber;
+ startnumber := housenum;
+ sectiongeo := ST_Reverse(sectiongeo);
+ 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;
+ seg_postcode := coalesce(postcode,
+ prevnode.address->'postcode',
+ nextnode.address->'postcode');
+
+ IF NEW.startnumber IS NULL THEN
+ NEW.startnumber := startnumber;
+ NEW.endnumber := endnumber;
+ NEW.linegeo := sectiongeo;
+ NEW.postcode := seg_postcode;
+ ELSE
+ insert into location_property_osmline
+ (linegeo, partition, osm_id, parent_place_id,
+ startnumber, endnumber, interpolationtype,
+ address, postcode, country_code,
+ geometry_sector, indexed_status)
+ values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
+ startnumber, endnumber, NEW.interpolationtype,
+ NEW.address, seg_postcode,
+ NEW.country_code, NEW.geometry_sector, 0);
+ END IF;
+ END IF;
- startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
- prevnode := nextnode;
- END IF;
- END LOOP;
+ -- 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.address->'housenumber','[0-9]+')::integer;
+ prevnode := nextnode;
+ END IF;
+ END LOOP;
+ END IF;
+
+ -- marking descendants for reparenting is not needed, because there are
+ -- actually no descendants for interpolation lines
RETURN NEW;
END;
$$
address_street_word_id INTEGER;
address_street_word_ids INTEGER[];
parent_place_id_rank BIGINT;
-
+
+ addr_street TEXT;
+ addr_place TEXT;
+
isin TEXT[];
isin_tokens INT[];
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;
RETURN NEW;
END IF;
+ IF NEW.address is not NULL THEN
+ IF NEW.address ? 'conscriptionnumber' THEN
+ i := getorcreate_housenumber_id(make_standard_name(NEW.address->'conscriptionnumber'));
+ IF NEW.address ? 'streetnumber' THEN
+ i := getorcreate_housenumber_id(make_standard_name(NEW.address->'streetnumber'));
+ NEW.housenumber := (NEW.address->'conscriptionnumber') || '/' || (NEW.address->'streetnumber');
+ ELSE
+ NEW.housenumber := NEW.address->'conscriptionnumber';
+ END IF;
+ ELSEIF NEW.address ? 'streetnumber' THEN
+ NEW.housenumber := NEW.address->'streetnumber';
+ i := getorcreate_housenumber_id(make_standard_name(NEW.address->'streetnumber'));
+ ELSEIF NEW.address ? 'housenumber' THEN
+ NEW.housenumber := NEW.address->'housenumber';
+ i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
+ END IF;
+
+ addr_street = NEW.address->'street';
+ addr_place = NEW.address->'place';
+
+ NEW.postcode = NEW.address->'postcode';
+ 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;
-- recalculate country and partition
- IF NEW.rank_search = 4 THEN
+ IF NEW.rank_search = 4 AND NEW.address is not NULL AND NEW.address ? 'country' THEN
-- for countries, believe the mapped country code,
-- so that we remain in the right partition if the boundaries
-- suddenly expand.
+ NEW.country_code := lower(NEW.address->'country');
NEW.partition := get_partition(lower(NEW.country_code));
IF NEW.partition = 0 THEN
- NEW.calculated_country_code := lower(get_country_code(place_centroid));
- NEW.partition := get_partition(NEW.calculated_country_code);
- ELSE
- NEW.calculated_country_code := lower(NEW.country_code);
+ NEW.country_code := lower(get_country_code(place_centroid));
+ NEW.partition := get_partition(NEW.country_code);
END IF;
ELSE
- IF NEW.rank_search > 4 THEN
- --NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
- NEW.calculated_country_code := lower(get_country_code(place_centroid));
+ IF NEW.rank_search >= 4 THEN
+ NEW.country_code := lower(get_country_code(place_centroid));
ELSE
- NEW.calculated_country_code := NULL;
+ NEW.country_code := NULL;
END IF;
- NEW.partition := get_partition(NEW.calculated_country_code);
+ NEW.partition := get_partition(NEW.country_code);
END IF;
-- waterway ways are linked when they are part of a relation and have the same class/type
-- Thought this wasn't needed but when we add new languages to the country_name table
-- we need to update the existing names
IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
- default_language := get_country_language_code(NEW.calculated_country_code);
+ default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
END LOOP;
NEW.importance := null;
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
IF NEW.importance IS NULL THEN
select language||':'||title,importance from wikipedia_article where osm_type = NEW.osm_type and osm_id = NEW.osm_id order by importance desc limit 1 INTO NEW.wikipedia,NEW.importance;
END IF;
-- 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
+ IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
AND NEW.housenumber IS NULL THEN
FOR location IN select * 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 address is not null
+ and (address ? 'housenumber' or address ? 'street' or address ? 'place')
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;
+ NEW.housenumber := location.address->'housenumber';
+ addr_street := location.address->'street';
+ addr_place := location.address->'place';
END LOOP;
END IF;
-- 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 NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(addr_street));
IF address_street_word_ids IS NOT NULL THEN
FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
NEW.parent_place_id := location.place_id;
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 NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN
+ address_street_word_ids := get_name_ids(make_standard_name(addr_place));
IF address_street_word_ids IS NOT NULL THEN
FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
NEW.parent_place_id := location.place_id;
-- Is this node part of a way?
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
- FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.street, p.addr_place from placex p, planet_osm_ways w
+ FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.address from placex p, planet_osm_ways w
where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes)
LOOP
END IF;
-- 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 NEW.parent_place_id IS NULL AND location.address ? 'street' THEN
+ address_street_word_ids := get_name_ids(make_standard_name(location.address->'street'));
IF address_street_word_ids IS NOT NULL THEN
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 IF;
END IF;
- 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 NEW.parent_place_id IS NULL AND location.address ? 'place' THEN
+ address_street_word_ids := get_name_ids(make_standard_name(location.address->'place'));
IF address_street_word_ids IS NOT NULL THEN
FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
NEW.parent_place_id := linkedplacex.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;
+ NEW.country_code := location.country_code;
- -- Merge the postcode into the parent's address if necessary XXXX
+ -- Merge the postcode into the parent's address if necessary
IF NEW.postcode IS NOT NULL THEN
isin_tokens := '{}'::int[];
address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
-- Just be happy with inheriting from parent road only
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);
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
return NEW;
END IF;
-- from the linked place. Make sure a name tag for the default language exists in
-- this case.
IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
- default_language := get_country_language_code(NEW.calculated_country_code);
+ default_language := get_country_language_code(NEW.country_code);
IF default_language IS NOT NULL THEN
IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name'));
-- Did we gain a wikipedia tag in the process? then we need to recalculate our importance
IF NEW.importance is null THEN
- select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.calculated_country_code) INTO NEW.wikipedia,NEW.importance;
+ select language||':'||title,importance from get_wikipedia_match(NEW.extratags, NEW.country_code) INTO NEW.wikipedia,NEW.importance;
END IF;
-- Still null? how about looking it up by the node id
IF NEW.importance IS NULL THEN
-- convert isin to array of tokenids
isin_tokens := '{}'::int[];
- IF NEW.isin IS NOT NULL THEN
- isin := regexp_split_to_array(NEW.isin, E'[;,]');
+ IF NEW.address IS NOT NULL THEN
+ isin := avals(NEW.address);
IF array_upper(isin, 1) IS NOT NULL THEN
FOR i IN 1..array_upper(isin, 1) LOOP
address_street_word_id := get_name_id(make_standard_name(isin[i]));
-- %NOTIGERDATA% IF 0 THEN
-- for the USA we have an additional address table. Merge in zip codes from there too
- IF NEW.rank_search = 26 AND NEW.calculated_country_code = 'us' THEN
+ IF NEW.rank_search = 26 AND NEW.country_code = 'us' THEN
FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
address_street_word_id := get_name_id(make_standard_name(location.postcode));
nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
IF NEW.name IS NOT NULL 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);
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
END IF;
IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
- result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.calculated_country_code, NEW.geometry);
+ result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
END IF;
- result := insertSearchName(NEW.partition, NEW.place_id, NEW.calculated_country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
+ result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry);
END IF;
--DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
-- filter wrong tupels
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,
+ INSERT INTO import_polygon_error values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type,
+ NEW.name, NEW.address->'country',
now(), ST_IsValidReason(NEW.geometry), null, NEW.geometry);
-- RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
RETURN null;
where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
- 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);
+ INSERT INTO location_property_osmline (osm_id, address, linegeo)
+ VALUES (NEW.osm_id, NEW.address, NEW.geometry);
IF existing.osm_type IS NULL THEN
return NEW;
END IF;
- IF 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, '')
+ IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+ OR (coalesce(existing.extratags, ''::hstore) != coalesce(NEW.extratags, ''::hstore))
OR existing.geometry::text != NEW.geometry::text
THEN
update place set
name = NEW.name,
- housenumber = NEW.housenumber,
- street = NEW.street,
- addr_place = NEW.addr_place,
- isin = NEW.isin,
- postcode = NEW.postcode,
- country_code = NEW.country_code,
+ address = NEW.address,
extratags = NEW.extratags,
admin_level = NEW.admin_level,
geometry = NEW.geometry
ELSE -- insert to placex
-- Patch in additional country names
- IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN
- SELECT name FROM country_name WHERE country_code = lower(NEW.country_code) INTO existing;
+ IF NEW.admin_level = 2 AND NEW.type = 'administrative'
+ AND NEW.address is not NULL AND NEW.address ? 'country' THEN
+ SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') INTO existing;
IF existing.name IS NOT NULL THEN
NEW.name = existing.name || NEW.name;
END IF;
END IF;
-- No - process it as a new insertion (hopefully of low rank or it will be slow)
- insert into placex (osm_type, osm_id, class, type, name, admin_level, housenumber,
- street, addr_place, isin, postcode, country_code, extratags, geometry)
- values (NEW.osm_type
- ,NEW.osm_id
- ,NEW.class
- ,NEW.type
- ,NEW.name
- ,NEW.admin_level
- ,NEW.housenumber
- ,NEW.street
- ,NEW.addr_place
- ,NEW.isin
- ,NEW.postcode
- ,NEW.country_code
- ,NEW.extratags
- ,NEW.geometry
- );
+ insert into placex (osm_type, osm_id, class, type, name,
+ admin_level, address, extratags, geometry)
+ values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
+ NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
--DEBUG: RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;
RETURN NEW;
END IF;
- -- Various ways to do the update
-
- -- Debug, what's changed?
- IF FALSE THEN
- IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '') THEN
- RAISE WARNING 'update details, name: % % % %',NEW.osm_type,NEW.osm_id,existing.name::text,NEW.name::text;
- END IF;
- IF coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '') THEN
- RAISE WARNING 'update details, housenumber: % % % %',NEW.osm_type,NEW.osm_id,existing.housenumber,NEW.housenumber;
- END IF;
- IF coalesce(existing.street, '') != coalesce(NEW.street, '') THEN
- RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.street,NEW.street;
- END IF;
- IF coalesce(existing.addr_place, '') != coalesce(NEW.addr_place, '') THEN
- RAISE WARNING 'update details, street: % % % %',NEW.osm_type,NEW.osm_id,existing.addr_place,NEW.addr_place;
- END IF;
- IF coalesce(existing.isin, '') != coalesce(NEW.isin, '') THEN
- RAISE WARNING 'update details, isin: % % % %',NEW.osm_type,NEW.osm_id,existing.isin,NEW.isin;
- END IF;
- IF coalesce(existing.postcode, '') != coalesce(NEW.postcode, '') THEN
- RAISE WARNING 'update details, postcode: % % % %',NEW.osm_type,NEW.osm_id,existing.postcode,NEW.postcode;
- END IF;
- IF coalesce(existing.country_code, '') != coalesce(NEW.country_code, '') THEN
- RAISE WARNING 'update details, country_code: % % % %',NEW.osm_type,NEW.osm_id,existing.country_code,NEW.country_code;
- END IF;
- END IF;
-
-- Special case for polygon shape changes because they tend to be large and we can be a bit clever about how we handle them
IF existing.geometry::text != NEW.geometry::text
AND ST_GeometryType(existing.geometry) in ('ST_Polygon','ST_MultiPolygon')
IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::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, '')
+ OR coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
OR existing.geometry::text != NEW.geometry::text
THEN
update place set
name = NEW.name,
- housenumber = NEW.housenumber,
- street = NEW.street,
- addr_place = NEW.addr_place,
- isin = NEW.isin,
- postcode = NEW.postcode,
- country_code = NEW.country_code,
+ address = NEW.address,
extratags = NEW.extratags,
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;
-
+
IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
- IF NEW.postcode IS NULL THEN
+ IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN
-- postcode was deleted, no longer retain in placex
DELETE FROM placex where place_id = existingplacex.place_id;
RETURN NULL;
END IF;
- NEW.name := hstore('ref', NEW.postcode);
+ NEW.name := hstore('ref', NEW.address->'postcode');
END IF;
-
+
update placex set
name = NEW.name,
- housenumber = NEW.housenumber,
- street = NEW.street,
- addr_place = NEW.addr_place,
- isin = NEW.isin,
- postcode = NEW.postcode,
- country_code = NEW.country_code,
+ address = NEW.address,
parent_place_id = null,
extratags = NEW.extratags,
- admin_level = CASE WHEN NEW.admin_level > 15 THEN 15 ELSE NEW.admin_level END,
- indexed_status = 2,
+ admin_level = NEW.admin_level,
+ indexed_status = 2,
geometry = NEW.geometry
where place_id = existingplacex.place_id;
-
+
-- if a node(=>house), which is part of a interpolation line, changes (e.g. the street attribute) => mark this line for reparenting
-- (already here, because interpolation lines are reindexed before nodes, so in the second call it would be too late)
IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN
hadcountry BOOLEAN;
BEGIN
-- first query osmline (interpolation lines)
- select parent_place_id, calculated_country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
+ select parent_place_id, country_code, 30, postcode, null, 'place', 'house' from location_property_osmline
WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
IF for_place_id IS NOT NULL THEN
-- %NOAUXDATA% END IF;
IF for_place_id IS NULL THEN
- select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex
+ select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex
WHERE place_id = in_place_id and rank_search > 27
INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
IF for_place_id IS NULL THEN
- select coalesce(linked_place_id, place_id), calculated_country_code,
+ select coalesce(linked_place_id, place_id), country_code,
housenumber, rank_search, postcode, null
from placex where place_id = in_place_id
INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename;
CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
class, type, admin_level, true as fromarea, true as isaddress,
CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
- 0 as distance, calculated_country_code, postcode
+ 0 as distance, country_code, postcode
from placex
where place_id = for_place_id
LOOP
--RAISE WARNING '%',location;
- IF searchcountrycode IS NULL AND location.calculated_country_code IS NOT NULL THEN
- searchcountrycode := location.calculated_country_code;
+ IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
+ searchcountrycode := location.country_code;
END IF;
IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
location.isaddress := FALSE;
CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress,
CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
- distance,calculated_country_code,postcode
+ distance,country_code,postcode
from place_addressline join placex on (address_place_id = placex.place_id)
where place_addressline.place_id = for_place_id
and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
and address_place_id != for_place_id
- and (placex.calculated_country_code IS NULL OR searchcountrycode IS NULL OR placex.calculated_country_code = searchcountrycode)
+ and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode)
order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
LOOP
--RAISE WARNING '%',location;
- IF searchcountrycode IS NULL AND location.calculated_country_code IS NOT NULL THEN
- searchcountrycode := location.calculated_country_code;
+ IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
+ searchcountrycode := location.country_code;
END IF;
IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
location.isaddress := FALSE;
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
- AS $$
-DECLARE
- numfeatures integer;
-BEGIN
- update placex set
- name = place.name,
- housenumber = place.housenumber,
- street = place.street,
- addr_place = place.addr_place,
- isin = place.isin,
- postcode = place.postcode,
- country_code = place.country_code,
- parent_place_id = null
- from place
- where placex.place_id = search_place_id
- and place.osm_type = placex.osm_type and place.osm_id = placex.osm_id
- and place.class = placex.class and place.type = placex.type;
- update placex set indexed_status = 2 where place_id = search_place_id;
- update placex set indexed_status = 0 where place_id = search_place_id;
- return true;
-END;
-$$
-LANGUAGE plpgsql;
-
CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT
AS $$
DECLARE
IF out_parent_place_id IS NULL THEN
FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
out_parent_place_id := location.place_id;
- END LOOP;
+ END LOOP;
END IF;
out_postcode := in_postcode;
IF ST_GeometryType(placegeom) in ('ST_Polygon','ST_MultiPolygon') THEN
FOR geom IN select split_geometry(placegeom) FROM placex WHERE place_id = placeid LOOP
update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
- AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and addr_place is not null));
+ AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address > 'place'));
update placex set indexed_status = 2 where (st_covers(geom, placex.geometry) OR ST_Intersects(geom, placex.geometry))
- AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and addr_place is not null));
+ AND rank_search > rank and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null or (rank >= 16 and address ? 'place'));
END LOOP;
ELSE
diameter := 0;
update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter);
ELSEIF rank >= 16 THEN
-- up to rank 16, street-less addresses may need reparenting
- update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or addr_place is not null);
+ update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null or address ? 'place');
ELSE
-- for all other places the search terms may change as well
update placex set indexed_status = 2 where indexed_status = 0 and rank_search > rank and ST_DWithin(placex.geometry, placegeom, diameter) and (rank_search < 28 or name is not null);
startnumber INTEGER,
endnumber INTEGER,
interpolationtype TEXT,
- street TEXT,
- addr_place TEXT,
+ address HSTORE,
postcode TEXT,
- calculated_country_code VARCHAR(2),
+ country_code VARCHAR(2),
geometry_sector INTEGER,
indexed_status INTEGER,
indexed_date TIMESTAMP){ts:search-data};
indexed_date TIMESTAMP,
wikipedia TEXT, -- calculated wikipedia article name (language:title)
geometry_sector INTEGER,
- calculated_country_code varchar(2)
+ country_code varchar(2),
+ housenumber TEXT,
+ postcode TEXT
) {ts:search-data};
SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {ts:search-index};
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 6 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 6 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1.001, 1 1 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 1 | 1 1 |
| N2 | place | house | 11 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | odd | 1 1, 1 1.001 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 1 | 1 1 |
| N2 | place | house | 3 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | all | 1 1, 1 1.001 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 10 | 1.001 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001, 1.001 1.001 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 10 | 1.001 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001, 1.001 1.001 |
And the ways
| id | nodes |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 14 | 1.001 1.001 |
| N3 | place | house | 10 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001, 1.001 1.001 |
And the ways
| id | nodes |
| N2 | place | house | 14 | 1.001 1.001 |
| N3 | place | house | 10 | 1 1.001 |
| N4 | place | house | 18 | 1.001 1.002 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001, 1.001 1.001, 1.001 1.002 |
And the ways
| id | nodes |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 14 | 1.001 1.001 |
| N3 | place | house | 10 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1.001 1.001, 1 1.001, 1 1 |
And the ways
| id | nodes |
| N1 | place | house | 2 | 1 1 |
| N2 | place | house | 8 | 1.001 1.001 |
| N3 | place | house | 7 | 1 1.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 1 1, 1 1.001, 1.001 1.001 |
And the ways
| id | nodes |
| N1 | place | house | 2 | 0 0 |
| N2 | place | house | 6 | 0 0.001 |
| N3 | place | house | 10 | 0 0.002 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 0 0, 0 0.001, 0 0.002, 0 0.001 |
And the ways
| id | nodes |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | 0 0 |
| N2 | place | house | 6 | 0 0.001 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | 0 0, 0 0.001, 0 0.002, 0 0.001 |
And the ways
| id | nodes |
| N3 | place | house | 12 | :n-middle-w |
| N4 | place | house | 16 | :n-middle-e |
And the places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W10 | place | houses | even | | :w-middle |
| W11 | place | houses | even | Cloud Street | :w-middle |
And the places
| N3 | place | house | 12 | Cloud Street | :n-middle-w |
| N4 | place | house | 16 | Cloud Street | :n-middle-e |
And the places
- | osm | class | type | housenr | geometry |
- | W10 | place | houses | even | :w-middle |
- | W11 | place | houses | even | :w-middle |
+ | osm | class | type | addr+interpolation | geometry |
+ | W10 | place | houses | even | :w-middle |
+ | W11 | place | houses | even | :w-middle |
And the places
| osm | class | type | name | geometry |
| W2 | highway | tertiary | Sun Way | :w-north |
| N1 | place | house | 10 | 144.9632341 -37.76163 |
| N2 | place | house | 6 | 144.9630541 -37.7628174 |
| N3 | shop | supermarket | 2 | 144.9629794 -37.7630755 |
- | W1 | place | houses | even | 144.9632341 -37.76163,144.9630541 -37.7628172,144.9629794 -37.7630755 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
+ | W1 | place | houses | even | 144.9632341 -37.76163,144.9630541 -37.7628172,144.9629794 -37.7630755 |
And the ways
| id | nodes |
| 1 | 1,2,3 |
| 6 | 10 | 144.9630541 -37.7628174, 144.9632341 -37.76163 |
Scenario: Place with missing address information
- Given the places
- | osm | class | type | housenr | geometry |
- | N1 | place | house | 23 | 0.0001 0.0001 |
- | N2 | amenity | school | | 0.0001 0.0002 |
- | N3 | place | house | 29 | 0.0001 0.0004 |
- | W1 | place | houses | odd | 0.0001 0.0001,0.0001 0.0002,0.0001 0.0004 |
+ Given the grid
+ | 1 | | 2 | | | 3 |
+ And the places
+ | osm | class | type | housenr |
+ | N1 | place | house | 23 |
+ | N2 | amenity | school | |
+ | N3 | place | house | 29 |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
+ | W1 | place | houses | odd | 1,2,3 |
And the ways
| id | nodes |
| 1 | 1,2,3 |
When importing
Then W1 expands to interpolation
| start | end | geometry |
- | 23 | 29 | 0.0001 0.0001, 0.0001 0.0002, 0.0001 0.0004 |
+ | 23 | 29 | 1,2,3 |
Scenario: Ways without node entries are ignored
Given the places
| N1 | place | locality | german | country:de |
When importing
Then placex contains
- | object | calculated_country_code | name+name |
- | N1 | de | german |
+ | object | country_code | name+name |
+ | N1 | de | german |
Scenario: Copying name tag to default language if it does not exist
Given the places
| N1 | place | locality | german | finnish | country:de |
When importing
Then placex contains
- | object | calculated_country_code | name | name+name:fi | name+name:de |
- | N1 | de | german | finnish | german |
+ | object | country_code | name | name+name:fi | name+name:de |
+ | N1 | de | german | finnish | german |
Scenario: Copying default language name tag to name if it does not exist
Given the places
| N1 | place | locality | german | finnish | country:de |
When importing
Then placex contains
- | object | calculated_country_code | name | name+name:fi | name+name:de |
- | N1 | de | german | finnish | german |
+ | object | country_code | name | name+name:fi | name+name:de |
+ | N1 | de | german | finnish | german |
Scenario: Do not overwrite default language with name tag
Given the places
| N1 | place | locality | german | finnish | local | country:de |
When importing
Then placex contains
- | object | calculated_country_code | name | name+name:fi | name+name:de |
- | N1 | de | german | finnish | local |
+ | object | country_code | name | name+name:fi | name+name:de |
+ | N1 | de | german | finnish | local |
| W3 | highway | residential | foo | :w-NS |
When importing
Then placex contains
- | object | parent_place_id | street | addr_place | housenumber |
- | W1 | W3 | foo | nowhere | 3 |
- | N1 | W3 | foo | nowhere | 3 |
- | N2 | W3 | foo | nowhere | 3 |
- | N3 | W3 | foo | nowhere | 3 |
+ | object | parent_place_id | housenumber |
+ | W1 | W3 | 3 |
+ | N1 | W3 | 3 |
+ | N2 | W3 | 3 |
+ | N3 | W3 | 3 |
Scenario: POIs don't inherit from streets
Given the scene building-on-street-corner
| W3 | highway | residential | foo | :w-NS |
When importing
Then placex contains
- | object | parent_place_id | street | addr_place | housenumber |
- | N1 | W3 | None | None | None |
+ | object | parent_place_id | housenumber |
+ | N1 | W3 | None |
Scenario: POIs with own address do not inherit building address
Given the scene building-on-street-corner
| W3 | highway | residential | foo | :w-NS |
When importing
Then placex contains
- | object | parent_place_id | street | addr_place | housenumber |
- | W1 | N4 | None | theplace | 3 |
- | N1 | W2 | bar | None | None |
- | N2 | W3 | None | None | 4 |
- | N3 | W2 | None | nowhere | None |
+ | object | parent_place_id | housenumber |
+ | W1 | N4 | 3 |
+ | N1 | W2 | None |
+ | N2 | W3 | 4 |
+ | N3 | W2 | None |
Scenario: POIs parent a road if they are attached to it
Given the scene points-on-roads
| N1 | highway | primary | country:us |
When importing
Then placex contains
- | object | country_code | calculated_country_code |
- | N1 | None | us |
+ | object | addr+country | country_code |
+ | N1 | - | us |
Scenario: Location overwrites country code tag
Given the named places
| N1 | highway | primary | de | country:us |
When importing
Then placex contains
- | object | country_code | calculated_country_code |
- | N1 | de | us |
+ | object | addr+country | country_code |
+ | N1 | de | us |
Scenario: Country code tag overwrites location for countries
Given the named places
| R1 | boundary | administrative | 2 | de | (-100 40, -101 40, -101 41, -100 41, -100 40) |
When importing
Then placex contains
- | object | country_code | calculated_country_code |
- | R1 | de | de |
+ | object | addr+country | country_code |
+ | R1 | de | de |
Scenario: Illegal country code tag for countries is ignored
Given the named places
| R1 | boundary | administrative | 2 | xx | (-100 40, -101 40, -101 41, -100 41, -100 40) |
When importing
Then placex contains
- | object | country_code | calculated_country_code |
- | R1 | xx | us |
+ | object | addr+country | country_code |
+ | R1 | xx | us |
Scenario: admin level is copied over
Given the named places
| object | admin_level |
| N1 | 3 |
- Scenario: admin level is default 15
- Given the named places
- | osm | class | type |
- | N1 | amenity | prison |
- When importing
- Then placex contains
- | object | admin_level |
- | N1 | 15 |
-
- Scenario: admin level is never larger than 15
- Given the named places
- | osm | class | type | admin |
- | N1 | amenity | prison | 16 |
- When importing
- Then placex contains
- | object | admin_level |
- | N1 | 15 |
-
Scenario: postcode node without postcode is dropped
Given the places
| osm | class | type | name+ref |
| N3 | place | postcode | Y45 | country:gb |
When importing
Then placex contains
- | object | postcode | calculated_country_code | rank_search | rank_address |
- | N1 | E45 2CD | gb | 25 | 5 |
- | N2 | E45 2 | gb | 23 | 5 |
- | N3 | Y45 | gb | 21 | 5 |
+ | object | postcode | country_code | rank_search | rank_address |
+ | N1 | E45 2CD | gb | 25 | 5 |
+ | N2 | E45 2 | gb | 23 | 5 |
+ | N3 | Y45 | gb | 21 | 5 |
Scenario: wrongly formatted GB postcodes are down-ranked
Given the places
| N3 | place | postcode | y45 | country:gb |
When importing
Then placex contains
- | object | calculated_country_code | rank_search | rank_address |
- | N1 | gb | 30 | 30 |
- | N2 | gb | 30 | 30 |
- | N3 | gb | 30 | 30 |
+ | object | country_code | rank_search | rank_address |
+ | N1 | gb | 30 | 30 |
+ | N2 | gb | 30 | 30 |
+ | N3 | gb | 30 | 30 |
Scenario: search and address rank for DE postcodes correctly assigned
Given the places
| N4 | place | postcode | 564276 | country:de |
When importing
Then placex contains
- | object | calculated_country_code | rank_search | rank_address |
- | N1 | de | 21 | 11 |
- | N2 | de | 30 | 30 |
- | N3 | de | 30 | 30 |
- | N4 | de | 30 | 30 |
+ | object | country_code | rank_search | rank_address |
+ | N1 | de | 21 | 11 |
+ | N2 | de | 30 | 30 |
+ | N3 | de | 30 | 30 |
+ | N4 | de | 30 | 30 |
Scenario: search and address rank for other postcodes are correctly assigned
Given the places
| N9 | place | postcode | A1:bc10 | country:ca |
When importing
Then placex contains
- | object | calculated_country_code | rank_search | rank_address |
- | N1 | ca | 21 | 11 |
- | N2 | ca | 21 | 11 |
- | N3 | ca | 21 | 11 |
- | N4 | ca | 21 | 11 |
- | N5 | ca | 21 | 11 |
- | N6 | ca | 21 | 11 |
- | N7 | ca | 25 | 11 |
- | N8 | ca | 25 | 11 |
- | N9 | ca | 25 | 11 |
+ | object | country_code | rank_search | rank_address |
+ | N1 | ca | 21 | 11 |
+ | N2 | ca | 21 | 11 |
+ | N3 | ca | 21 | 11 |
+ | N4 | ca | 21 | 11 |
+ | N5 | ca | 21 | 11 |
+ | N6 | ca | 21 | 11 |
+ | N7 | ca | 25 | 11 |
+ | N8 | ca | 25 | 11 |
+ | N9 | ca | 25 | 11 |
Scenario: search and address ranks for places are correctly assigned
Given the named places
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
- | W10 | place | houses | even | :w-middle |
+ And updating places
+ | osm | class | type | addr+interpolation | geometry |
+ | W10 | place | houses | even | :w-middle |
Then placex contains
| object | parent_place_id |
| N1 | W2 |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W10 | place | houses | even | :w-middle |
And the places
| osm | class | type | name | geometry |
| parent_place_id | start | end |
| W2 | 2 | 6 |
When updating places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W10 | place | houses | even | Cloud Street | :w-middle |
Then placex contains
| object | parent_place_id |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W10 | place | houses | even | :w-middle |
And the places
| osm | class | type | name | geometry |
| osm | class | type | housenr | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
+ And the places
+ | osm | class | type | addr+interpolation | geometry |
| W10 | place | houses | even | :w-middle |
And the places
| osm | class | type | name | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
And the places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W10 | place | houses | even | Cloud Street| :w-middle |
And the places
| osm | class | type | name | geometry |
| N1 | place | house | 2 | :n-middle-w |
| N2 | place | house | 6 | :n-middle-e |
And the places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W10 | place | houses | even | Cloud Street| :w-middle |
And the places
| osm | class | type | name | geometry |
| N1 | place | house | 2 | :n-north-w |
| N2 | place | house | 6 | :n-north-e |
And updating places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W1 | place | houses | even | Cloud Street| :w-north |
Then placex has no entry for W1
And W1 expands to interpolation
| id | nodes |
| 1 | 1,100,101,102,2 |
And the places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W1 | place | houses | even | Cloud Street| :w-north |
When importing
Then placex has no entry for W1
| id | nodes |
| 1 | 1,100,101,102,2 |
And the places
- | osm | class | type | housenr | geometry |
+ | osm | class | type | addr+interpolation | geometry |
| W1 | place | houses | even | :w-north |
When importing
Then W1 expands to no interpolation
| N1 | place | house | 2 | :n-north-w |
| N2 | place | house | 6 | :n-north-e |
And updating places
- | osm | class | type | housenr | street | geometry |
+ | osm | class | type | addr+interpolation | street | geometry |
| W1 | place | houses | even | Cloud Street| :w-north |
Then W1 expands to interpolation
| parent_place_id | start | end |
| id | nodes |
| 2 | 3,4,5 |
And the places
- | osm | class | type | housenr | geometry |
+ | osm | class | type | addr+interpolation | geometry |
| W2 | place | houses | even | 3,4,5 |
And the places
| osm | class | type | housenr |
| id | nodes |
| 2 | 3,4,5 |
And the places
- | osm | class | type | housenr | geometry |
+ | osm | class | type | addr+interpolation | geometry |
| W2 | place | houses | even | 3,4,5 |
And the places
| osm | class | type | housenr |
| id | nodes |
| 2 | 3,4 |
And the places
- | osm | class | type | housenr | geometry |
+ | osm | class | type | addr+interpolation | geometry |
| W2 | place | houses | even | 3,4 |
And the places
| osm | class | type | housenr |
import psycopg2
import psycopg2.extras
import subprocess
+import tempfile
from nose.tools import * # for assert functions
from sys import version_info as python_version
conn.commit()
conn.close()
- # execute osm2pgsql on an empty file to get the right tables
- osm2pgsql = os.path.join(self.build_dir, 'osm2pgsql', 'osm2pgsql')
- proc = subprocess.Popen([osm2pgsql, '-lsc', '-r', 'xml',
- '-O', 'gazetteer', '-d', self.template_db, '-'],
- cwd=self.build_dir, stdin=subprocess.PIPE,
- stdout=subprocess.PIPE, stderr=subprocess.PIPE)
- [outstr, errstr] = proc.communicate(input=b'<osm version="0.6"></osm>')
- logger.debug("running osm2pgsql for template: %s\n%s\n%s" % (osm2pgsql, outstr, errstr))
- self.run_setup_script('create-functions', 'create-tables',
- 'create-partition-tables', 'create-partition-functions',
- 'load-data', 'create-search-indices')
+ # execute osm2pgsql import on an empty file to get the right tables
+ with tempfile.NamedTemporaryFile(dir='/tmp', suffix='.xml') as fd:
+ fd.write(b'<osm version="0.6"></osm>')
+ fd.flush()
+ self.run_setup_script('import-data',
+ 'ignore-errors',
+ 'create-functions',
+ 'create-tables',
+ 'create-partition-tables',
+ 'create-partition-functions',
+ 'load-data',
+ 'create-search-indices',
+ osm_file=fd.name,
+ osm2pgsql_cache='200')
def setup_api_db(self, context):
self.write_nominatim_config(self.api_test_db)
n1 T<key>=<value>,name=real
"""
Then place contains
- | object | name |
- | N1 | 'name' : 'real' |
+ | object | class | type | name |
+ | N1 | <key> | <value> | 'name' : 'real' |
Examples:
| key | value |
n11 T<key>=<value>
"""
Then place contains
- | object | class | type | postcode |
+ | object | class | type | addr+postcode |
| N10 | highway | secondary | <value> |
| N11 | place | postcode | <value> |
And place has no entry for N10:place
n20 Tamenity=hospital,addr:place=Foo%20%Town
"""
Then place contains
- | object | class | type | street | addr_place |
- | N10 | amenity | hospital | Foo St | None |
- | N20 | amenity | hospital | - | Foo Town |
+ | object | class | type | addr+street | addr+place |
+ | N10 | amenity | hospital | Foo St | - |
+ | N20 | amenity | hospital | - | Foo Town |
Scenario Outline: Import of country
n10 Tplace=village,<key>=<value>
"""
Then place contains
- | object | class | type | country_code |
+ | object | class | type | addr+country |
| N10 | place | village | <value> |
Examples:
- | key | value |
- | country_code | us |
- | ISO3166-1 | XX |
- | is_in:country_code | __ |
- | addr:country | .. |
- | addr:country_code | cv |
+ | key | value |
+ | country_code | us |
+ | ISO3166-1 | XX |
+ | is_in:country_code | __ |
+ | addr:country | .. |
+ | addr:country_code | cv |
Scenario Outline: Ignore country codes with wrong length
When loading osm data
n10 Tplace=village,country_code=<value>
"""
Then place contains
- | object | class | type | country_code |
+ | object | class | type | addr+country |
| N10 | place | village | - |
Examples:
n13 Tbuilding=yes,addr:conscriptionnumber=3,addr:streetnumber=111
"""
Then place contains
- | object | class | type | housenumber |
- | N10 | building | yes | 4b |
- | N11 | building | yes | 003 |
- | N12 | building | yes | 2345 |
- | N13 | building | yes | 3/111 |
-
- Scenario: Import of address interpolations
- When loading osm data
- """
- n10 Taddr:interpolation=odd
- n11 Taddr:housenumber=10,addr:interpolation=odd
- n12 Taddr:interpolation=odd,addr:housenumber=23
- """
- Then place contains
- | object | class | type | housenumber |
- | N10 | place | houses | odd |
- | N11 | place | houses | odd |
- | N12 | place | houses | odd |
+ | object | class | type | address |
+ | N10 | building | yes | 'housenumber' : '4b' |
+ | N11 | building | yes | 'conscriptionnumber' : '003' |
+ | N12 | building | yes | 'streetnumber' : '2345' |
+ | N13 | building | yes | 'conscriptionnumber' : '3', 'streetnumber' : '111' |
Scenario: Shorten tiger:county tags
When loading osm data
n12 Tplace=village,tiger:county=Feebourgh
"""
Then place contains
- | object | class | type | isin |
+ | object | class | type | addr+tiger:county |
| N10 | place | village | Feebourgh county |
- | N11 | place | village | Alabama,Feebourgh county |
+ | N11 | place | village | Feebourgh county |
| N12 | place | village | Feebourgh county |
Scenario Outline: Import of address tags
When loading osm data
"""
- n10 Tplace=village,<key>=<value>
+ n10 Tplace=village,addr:<key>=<value>
+ n11 Tplace=village,is_in:<key>=<value>
"""
Then place contains
- | object | class | type | isin |
- | N10 | place | village | <value> |
+ | object | class | type | address |
+ | N10 | place | village | '<key>' : '<value>' |
Examples:
- | key | value |
- | is_in:country | Xanadu |
- | addr:suburb | hinein |
- | addr:city | Sydney |
- | addr:state | Jura |
+ | key | value |
+ | suburb | hinein |
+ | city | Sydney |
+ | state | Jura |
Scenario: Import of isin tags with space
When loading osm data
n11 Tplace=village,addr:county=le%20%havre
"""
Then place contains
- | object | class | type | isin |
- | N10 | place | village | Stockholm, Sweden |
- | N11 | place | village | le havre |
+ | object | class | type | address |
+ | N10 | place | village | 'is_in' : 'Stockholm, Sweden' |
+ | N11 | place | village | 'county' : 'le havre' |
Scenario: Import of admin level
When loading osm data
Then place contains
| object | class | type | admin_level |
| N10 | amenity | hospital | 3 |
- | N11 | amenity | hospital | 100 |
- | N12 | amenity | hospital | 100 |
+ | N11 | amenity | hospital | 15 |
+ | N12 | amenity | hospital | 15 |
| N13 | amenity | hospital | 3 |
Scenario Outline: Import of extra tags
n290393920 Taddr:city=Perpignan,addr:country=FR,addr:housenumber=43\,addr:postcode=66000,addr:street=Rue%20%Pierre%20%Constant%20%d`Ivry,source=cadastre-dgi-fr%20%source%20%:%20%Direction%20%Générale%20%des%20%Impôts%20%-%20%Cadastre%20%;%20%mise%20%à%20%jour%20%:2008
"""
Then place contains
- | object | class | type | housenumber |
- | N290393920 | place | house| 43\ |
+ | object | class | type | address |
+ | N290393920 | place | house| 'city' : 'Perpignan', 'country' : 'FR', 'housenumber' : '43\\', 'postcode' : '66000', 'street' : 'Rue Pierre Constant d`Ivry' |
r1 Ttype=boundary,boundary=administrative,name=Foo,country_code=XX,admin_level=2 Mw1@
"""
Then place contains
- | object | country_code | name |
+ | object | addr+country | name |
| R1 | XX | 'name' : 'Foo' |
Scenario: Country boundary names are extended when country_code known
r1 Ttype=boundary,boundary=administrative,name=Foo,country_code=ch,admin_level=2 Mw1@
"""
Then place contains
- | object | country_code | name+name:de | name+name |
+ | object | addr+country | name+name:de | name+name |
| R1 | ch | Schweiz | Foo |
class PlaceColumn:
def __init__(self, context, force_name):
- self.columns = { 'admin_level' : 100}
+ self.columns = { 'admin_level' : 15}
self.force_name = force_name
self.context = context
self.geometry = None
self.add_hstore('name', key[5:], value)
elif key.startswith('extra+'):
self.add_hstore('extratags', key[6:], value)
+ elif key.startswith('addr+'):
+ self.add_hstore('address', key[5:], value)
else:
- assert_in(key, ('class', 'type', 'street', 'addr_place',
- 'isin', 'postcode'))
+ assert_in(key, ('class', 'type'))
self.columns[key] = None if value == '' else value
def set_key_name(self, value):
self.columns['admin_level'] = int(value)
def set_key_housenr(self, value):
- self.columns['housenumber'] = None if value == '' else value
+ if value:
+ self.add_hstore('address', 'housenumber', value)
+
+ def set_key_postcode(self, value):
+ if value:
+ self.add_hstore('address', 'postcode', value)
+
+ def set_key_street(self, value):
+ if value:
+ self.add_hstore('address', 'street', value)
+
+ def set_key_addr_place(self, value):
+ if value:
+ self.add_hstore('address', 'place', value)
def set_key_country(self, value):
- self.columns['country_code'] = None if value == '' else value
+ if value:
+ self.add_hstore('address', 'country', value)
def set_key_geometry(self, value):
self.geometry = self.context.osm.parse_geometry(value, self.context.scene)
self.geometry)
cursor.execute(query, list(self.columns.values()))
+class LazyFmt(object):
+
+ def __init__(self, fmtstr, *args):
+ self.fmt = fmtstr
+ self.args = args
+
+ def __str__(self):
+ return self.fmt % self.args
+
+class PlaceObjName(object):
+
+ def __init__(self, placeid, conn):
+ self.pid = placeid
+ self.conn = conn
+
+ def __str__(self):
+ if self.pid is None:
+ return "<null>"
+
+ cur = self.conn.cursor()
+ cur.execute("""SELECT osm_type, osm_id, class
+ FROM placex WHERE place_id = %s""",
+ (self.pid, ))
+ eq_(1, cur.rowcount, "No entry found for place id %s" % self.pid)
+
+ return "%s%s:%s" % cur.fetchone()
+
+def compare_place_id(expected, result, column, context):
+ if expected == '0':
+ eq_(0, result,
+ LazyFmt("Bad place id in column %s. Expected: 0, got: %s.",
+ column, PlaceObjName(result, context.db)))
+ elif expected == '-':
+ assert_is_none(result,
+ LazyFmt("bad place id in column %s: %s.",
+ column, PlaceObjName(result, context.db)))
+ else:
+ eq_(NominatimID(expected).get_place_id(context.db.cursor()), result,
+ LazyFmt("Bad place id in column %s. Expected: %s, got: %s.",
+ column, expected, PlaceObjName(result, context.db)))
+
class NominatimID:
""" Splits a unique identifier for places into its components.
As place_ids cannot be used for testing, we use a unique
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)
+ address, extratags, geometry)
select * from place where not (class='place' and type='houses' and osm_type='W')""")
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
+ """insert into location_property_osmline (osm_id, address, linegeo)
+ SELECT osm_id, address, geometry from place
WHERE class='place' and type='houses' and osm_type='W'
and ST_GeometryType(geometry) = 'ST_LineString'""")
context.db.commit()
eq_(res['name'][name], row[h])
elif h.startswith('extratags+'):
eq_(res['extratags'][h[10:]], row[h])
- elif h in ('linked_place_id', 'parent_place_id'):
- if row[h] == '0':
- eq_(0, res[h])
- elif row[h] == '-':
- assert_is_none(res[h])
+ elif h.startswith('addr+'):
+ if row[h] == '-':
+ if res['address'] is not None:
+ assert_not_in(h[5:], res['address'])
else:
- eq_(NominatimID(row[h]).get_place_id(context.db.cursor()),
- res[h])
+ assert_in(h[5:], res['address'], "column " + h)
+ assert_equals(res['address'][h[5:]], row[h],
+ "column %s" % h)
+ elif h in ('linked_place_id', 'parent_place_id'):
+ compare_place_id(row[h], res[h], h, context)
else:
assert_db_column(res, h, row[h], context)
expected_content.add((res['osm_type'], res['osm_id'], res['class']))
for h in row.headings:
msg = "%s: %s" % (row['object'], h)
- if h in ('name', 'extratags'):
+ if h in ('name', 'extratags', 'address'):
if row[h] == '-':
assert_is_none(res[h], msg)
else:
assert_equals(res['name'][h[5:]], row[h], msg)
elif h.startswith('extratags+'):
assert_equals(res['extratags'][h[10:]], row[h], msg)
- elif h in ('linked_place_id', 'parent_place_id'):
- if row[h] == '0':
- assert_equals(0, res[h], msg)
- elif row[h] == '-':
- assert_is_none(res[h], msg)
+ elif h.startswith('addr+'):
+ if row[h] == '-':
+ if res['address'] is not None:
+ assert_not_in(h[5:], res['address'])
else:
- assert_equals(NominatimID(row[h]).get_place_id(context.db.cursor()),
- res[h], msg)
+ assert_equals(res['address'][h[5:]], row[h], msg)
+ elif h in ('linked_place_id', 'parent_place_id'):
+ compare_place_id(row[h], res[h], h, context)
else:
assert_db_column(res, h, row[h], context)
FROM word, (SELECT unnest(%s) as term) t
WHERE word_token = make_standard_name(t.term)""",
(terms,))
- ok_(subcur.rowcount >= len(terms))
+ ok_(subcur.rowcount >= len(terms),
+ "No word entry found for " + row[h])
for wid in subcur:
assert_in(wid[0], res[h],
"Missing term for %s/%s: %s" % (pid, h, wid[1]))
if h in ('start', 'end'):
continue
elif h == 'parent_place_id':
- if row[h] == '0':
- eq_(0, res[h])
- elif row[h] == '-':
- assert_is_none(res[h])
- else:
- eq_(NominatimID(row[h]).get_place_id(context.db.cursor()),
- res[h])
+ compare_place_id(row[h], res[h], h, context)
else:
assert_db_column(res, h, row[h], context)
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""")
+ admin_level, address, 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
+ """insert into location_property_osmline (osm_id, address, linegeo)
+ SELECT osm_id, address, geometry from place
WHERE class='place' and type='houses' and osm_type='W'
and ST_GeometryType(geometry) = 'ST_LineString'""")
context.db.commit()
passthruCheckReturn($osm2pgsql);
$oDB =& getDB();
- if (!chksql($oDB->getRow('select * from place limit 1'))) {
+ if (!$aCMDResult['ignore-errors'] && !chksql($oDB->getRow('select * from place limit 1'))) {
fail('No Data');
}
}
for ($i = 0; $i < $iLoadThreads; $i++) {
$aDBInstances[$i] =& getDB(true);
$sSQL = 'insert into placex (osm_type, osm_id, class, type, name, admin_level, ';
- $sSQL .= 'housenumber, street, addr_place, isin, postcode, country_code, extratags, ';
- $sSQL .= 'geometry) select * from place where osm_id % '.$iLoadThreads.' = '.$i;
+ $sSQL .= ' address, extratags, geometry) ';
+ $sSQL .= 'select * from place where osm_id % '.$iLoadThreads.' = '.$i;
$sSQL .= " and not (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));
// last thread for interpolation lines
$aDBInstances[$iLoadThreads] =& getDB(true);
$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 .= ' (osm_id, address, linegeo)';
+ $sSQL .= ' SELECT osm_id, address, 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));
$bDidSomething = true;
$oDB =& getDB();
if (!pg_query($oDB->connection, 'DELETE from placex where osm_type=\'P\'')) fail(pg_last_error($oDB->connection));
- $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
- $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 = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
+ $sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,country_code,";
+ $sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from (select 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 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) {
- $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,calculated_country_code,geometry) ";
+ $sSQL = "insert into placex (osm_type,osm_id,class,type,postcode,country_code,geometry) ";
$sSQL .= "select 'P',nextval('seq_postcodes'),'place','postcode',postcode,'us',";
$sSQL .= "ST_SetSRID(ST_Point(x,y),4326) as geometry from us_postcode";
if (!pg_query($oDB->connection, $sSQL)) fail(pg_last_error($oDB->connection));
$oDB =& getDB();
-$sSQL = "select placex.place_id, calculated_country_code as country_code,";
+$sSQL = "select placex.place_id, country_code,";
$sSQL .= " name->'name' as name, i.* from placex, import_polygon_delete i";
$sSQL .= " where placex.osm_id = i.osm_id and placex.osm_type = i.osm_type";
$sSQL .= " and placex.class = i.class and placex.type = i.type";
// Get the details for this point
$sSQL = "SELECT place_id, osm_type, osm_id, class, type, name, admin_level,";
-$sSQL .= " housenumber, street, isin, postcode, calculated_country_code AS country_code,";
+$sSQL .= " housenumber, postcode, country_code,";
$sSQL .= " importance, wikipedia,";
$sSQL .= " to_char(indexed_date, 'YYYY-MM-DD HH24:MI') AS indexed_date,";
$sSQL .= " parent_place_id, ";
projects / nominatim.git / commitdiff