OUT nameaddress_vector INT[])
AS $$
DECLARE
- current_rank_address INTEGER := 0;
- location_distance FLOAT := 0;
- location_parent GEOMETRY := NULL;
- parent_place_id_rank SMALLINT := 0;
+ address_havelevel BOOLEAN[];
location_isaddress BOOLEAN;
-
- address_havelevel BOOLEAN[];
- location_keywords INT[];
+ current_boundary GEOMETRY := NULL;
+ current_node_area GEOMETRY := NULL;
location RECORD;
addr_item RECORD;
isin_tokens INT[];
- isin TEXT[];
BEGIN
parent_place_id := 0;
nameaddress_vector := '{}'::int[];
END IF;
---- now compute the address terms
- FOR i IN 1..28 LOOP
+ FOR i IN 1..maxrank LOOP
address_havelevel[i] := false;
END LOOP;
FOR location IN
- SELECT * FROM getNearFeatures(partition, geometry, maxrank, isin_tokens)
+ SELECT * FROM getNearFeatures(partition, geometry, maxrank)
+ ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
+ distance *
+ CASE WHEN rank_address = 16 AND rank_search = 15 THEN 0.2
+ WHEN rank_address = 16 AND rank_search = 16 THEN 0.25
+ WHEN rank_address = 16 AND rank_search = 18 THEN 0.5
+ ELSE 1 END ASC
LOOP
- IF location.rank_address != current_rank_address THEN
- current_rank_address := location.rank_address;
- IF location.isguess THEN
- location_distance := location.distance * 1.5;
- ELSE
- IF location.rank_address <= 12 THEN
- -- for county and above, if we have an area consider that exact
- -- (It would be nice to relax the constraint for places close to
- -- the boundary but we'd need the exact geometry for that. Too
- -- expensive.)
- location_distance = 0;
- ELSE
- -- Below county level remain slightly fuzzy.
- location_distance := location.distance * 0.5;
- END IF;
+ -- Ignore all place nodes that do not fit in a lower level boundary.
+ CONTINUE WHEN location.isguess
+ and current_boundary is not NULL
+ and not ST_Contains(current_boundary, location.centroid);
+
+ -- If this is the first item in the rank, then assume it is the address.
+ location_isaddress := not address_havelevel[location.rank_address];
+
+ -- Further sanity checks to ensure that the address forms a sane hierarchy.
+ IF location_isaddress THEN
+ IF location.isguess and current_node_area is not NULL THEN
+ location_isaddress := ST_Contains(current_node_area, location.centroid);
+ END IF;
+ IF not location.isguess and current_boundary is not NULL
+ and location.rank_address != 11 AND location.rank_address != 5 THEN
+ location_isaddress := ST_Contains(current_boundary, location.centroid);
END IF;
- ELSE
- CONTINUE WHEN location.keywords <@ location_keywords;
END IF;
- IF location.distance < location_distance OR NOT location.isguess THEN
- location_keywords := location.keywords;
+ IF location_isaddress THEN
+ address_havelevel[location.rank_address] := true;
+ parent_place_id := location.place_id;
- location_isaddress := NOT address_havelevel[location.rank_address];
- --DEBUG: RAISE WARNING 'should be address: %, is guess: %, rank: %', location_isaddress, location.isguess, location.rank_address;
- IF location_isaddress AND location.isguess AND location_parent IS NOT NULL THEN
- location_isaddress := ST_Contains(location_parent, location.centroid);
+ -- Set postcode if we have one.
+ -- (Returned will be the highest ranking one.)
+ IF location.postcode is not NULL THEN
+ postcode = location.postcode;
END IF;
- --DEBUG: RAISE WARNING '% isaddress: %', location.place_id, location_isaddress;
- -- Add it to the list of search terms
- IF NOT %REVERSE-ONLY% THEN
- nameaddress_vector := array_merge(nameaddress_vector,
- location.keywords::integer[]);
- END IF;
-
- INSERT INTO place_addressline (place_id, address_place_id, fromarea,
- isaddress, distance, cached_rank_address)
- VALUES (obj_place_id, location.place_id, true,
- location_isaddress, location.distance, location.rank_address);
-
- IF location_isaddress THEN
- -- add postcode if we have one
- -- (If multiple postcodes are available, we end up with the highest ranking one.)
- IF location.postcode is not null THEN
- postcode = location.postcode;
- END IF;
-
- address_havelevel[location.rank_address] := true;
- -- add a hack against postcode ranks
- IF NOT location.isguess
- AND location.rank_address != 11 AND location.rank_address != 5
- THEN
+ -- Recompute the areas we need for hierarchy sanity checks.
+ IF location.rank_address != 11 AND location.rank_address != 5 THEN
+ IF location.isguess THEN
+ current_node_area := place_node_fuzzy_area(location.centroid,
+ location.rank_search);
+ ELSE
+ current_node_area := NULL;
SELECT p.geometry FROM placex p
- WHERE p.place_id = location.place_id INTO location_parent;
- END IF;
-
- IF location.rank_address > parent_place_id_rank THEN
- parent_place_id = location.place_id;
- parent_place_id_rank = location.rank_address;
+ WHERE p.place_id = location.place_id INTO current_boundary;
END IF;
END IF;
END IF;
+ -- Add it to the list of search terms
+ IF NOT %REVERSE-ONLY% THEN
+ nameaddress_vector := array_merge(nameaddress_vector,
+ location.keywords::integer[]);
+ END IF;
+
+ INSERT INTO place_addressline (place_id, address_place_id, fromarea,
+ isaddress, distance, cached_rank_address)
+ VALUES (obj_place_id, location.place_id, not location.isguess,
+ location_isaddress, location.distance, location.rank_address);
END LOOP;
END;
$$
FROM placex
WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
and admin_level < in_level
- and geometry && geom and ST_Covers(geometry, geom)
+ and geometry ~ geom and _ST_Covers(geometry, geom)
ORDER BY admin_level desc LIMIT 1;
END IF;
IF NEW.class = 'boundary' and NEW.type = 'administrative'
and NEW.osm_type = 'R' and NEW.rank_address > 0
THEN
+ -- First, check that admin boundaries do not overtake each other rank-wise.
parent_address_level := get_parent_address_level(NEW.centroid, NEW.admin_level);
IF parent_address_level >= NEW.rank_address THEN
IF parent_address_level >= 24 THEN
NEW.rank_address := parent_address_level + 2;
END IF;
END IF;
- -- If a place node is contained in a admin boundary with the same address level
- -- and has not been linked, then make the node a subpart by increasing the
- -- address rank (city level and above).
+ -- Second check that the boundary is not completely contained in a
+ -- place area with a higher address rank
+ FOR location IN
+ SELECT rank_address FROM placex
+ WHERE class = 'place' and rank_address < 24
+ and rank_address > NEW.rank_address
+ and geometry && NEW.geometry
+ and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
+ ORDER BY rank_address desc LIMIT 1
+ LOOP
+ NEW.rank_address := location.rank_address + 2;
+ END LOOP;
ELSEIF NEW.class = 'place' and NEW.osm_type = 'N'
and NEW.rank_address between 16 and 23
THEN
+ -- If a place node is contained in a admin boundary with the same address level
+ -- and has not been linked, then make the node a subpart by increasing the
+ -- address rank (city level and above).
FOR location IN
SELECT rank_address FROM placex
WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
result := add_location(NEW.place_id, NEW.country_code, NEW.partition,
name_vector, NEW.rank_search, NEW.rank_address,
- upper(trim(NEW.address->'postcode')), NEW.geometry);
+ upper(trim(NEW.address->'postcode')), NEW.geometry,
+ NEW.centroid);
--DEBUG: RAISE WARNING 'Place added to location table';
END IF;
END IF;
SELECT * FROM insert_addresslines(NEW.place_id, NEW.partition,
- CASE WHEN NEW.rank_address = 0
- THEN NEW.rank_search ELSE NEW.rank_address END,
+ CASE WHEN NEW.rank_address = 0 THEN NEW.rank_search
+ WHEN NEW.rank_address > 25 THEN 25::smallint
+ ELSE NEW.rank_address END,
NEW.address,
CASE WHEN NEW.rank_search >= 26
AND NEW.rank_search < 30
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.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry);
+ result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry, NEW.centroid);
--DEBUG: RAISE WARNING 'added to location (full)';
END IF;
NEW.parent_place_id = 0;
FOR location IN
SELECT place_id
- FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search, '{}'::int[])
- WHERE NOT isguess ORDER BY rank_address DESC LIMIT 1
+ FROM getNearFeatures(partition, NEW.geometry, NEW.rank_search)
+ WHERE NOT isguess ORDER BY rank_address DESC, distance asc LIMIT 1
LOOP
NEW.parent_place_id = location.place_id;
END LOOP;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION near_feature_rank_distance(rank_search INTEGER)
- RETURNS FLOAT
+-- Create a bounding box with an extent computed from the radius (in meters)
+-- which in turn is derived from the given search rank.
+CREATE OR REPLACE FUNCTION place_node_fuzzy_area(geom GEOMETRY, rank_search INTEGER)
+ RETURNS GEOMETRY
AS $$
+DECLARE
+ radius FLOAT := 500;
BEGIN
IF rank_search <= 16 THEN -- city
- RETURN 15000;
+ radius := 15000;
ELSIF rank_search <= 18 THEN -- town
- RETURN 4000;
+ radius := 4000;
ELSIF rank_search <= 19 THEN -- village
- RETURN 2000;
+ radius := 2000;
ELSIF rank_search <= 20 THEN -- hamlet
- RETURN 1000;
+ radius := 1000;
END IF;
- RETURN 500;
+ RETURN ST_Envelope(ST_Collect(
+ ST_Project(geom, radius, 0.785398)::geometry,
+ ST_Project(geom, radius, 3.9269908)::geometry));
END;
$$
LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION add_location(place_id BIGINT, country_code varchar(2),
partition INTEGER, keywords INTEGER[],
rank_search INTEGER, rank_address INTEGER,
- in_postcode TEXT, geometry GEOMETRY)
+ in_postcode TEXT, geometry GEOMETRY,
+ centroid GEOMETRY)
RETURNS BOOLEAN
AS $$
DECLARE
locationid INTEGER;
- centroid GEOMETRY;
- radius FLOAT;
secgeo GEOMETRY;
postcode TEXT;
BEGIN
END IF;
IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
- centroid := ST_Centroid(geometry);
-
FOR secgeo IN select split_geometry(geometry) AS geom LOOP
PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, false, postcode, centroid, secgeo);
END LOOP;
ELSEIF ST_GeometryType(geometry) = 'ST_Point' THEN
- radius := near_feature_rank_distance(rank_search);
- --DEBUG: RAISE WARNING 'adding % radius %', place_id, radius;
-
- -- Create a bounding box with an extent computed from the radius (in meters).
- secgeo := ST_Envelope(ST_Collect(
- ST_Project(geometry, radius, 0.785398)::geometry,
- ST_Project(geometry, radius, 3.9269908)::geometry));
- PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, geometry, secgeo);
+ secgeo := place_node_fuzzy_area(geometry, rank_search);
+ PERFORM insertLocationAreaLarge(partition, place_id, country_code, keywords, rank_search, rank_address, true, postcode, centroid, secgeo);
END IF;
END
$$ LANGUAGE plpgsql IMMUTABLE;
-create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeaturecentr AS $$
+create or replace function getNearFeatures(in_partition INTEGER, feature GEOMETRY, maxrank INTEGER) RETURNS setof nearfeaturecentr AS $$
DECLARE
r nearfeaturecentr%rowtype;
BEGIN
AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature))
AND rank_address < maxrank
GROUP BY place_id, keywords, rank_address, rank_search, isguess, postcode, centroid
- ORDER BY rank_address, isin_tokens && keywords desc, isguess asc,
- ST_Distance(feature, centroid) *
- CASE
- WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city
- WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city
- WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town
- ELSE 1 END ASC -- everything else
LOOP
RETURN NEXT r;
END LOOP;
Feature: Address computation
Tests for filling of place_addressline
+ Scenario: place nodes are added to the address when they are close enough
+ Given the 0.002 grid
+ | 2 | | | | | | 1 | | 3 |
+ And the named places
+ | osm | class | type | geometry |
+ | N1 | place | square | 1 |
+ | N2 | place | hamlet | 2 |
+ | N3 | place | hamlet | 3 |
+ When importing
+ Then place_addressline contains
+ | object | address | fromarea |
+ | N1 | N3 | False |
+ Then place_addressline doesn't contain
+ | object | address |
+ | N1 | N2 |
+
+ Scenario: given two place nodes, the closer one wins for the address
+ Given the grid
+ | 2 | | | 1 | | 3 |
+ And the named places
+ | osm | class | type | geometry |
+ | N1 | place | square | 1 |
+ | N2 | place | hamlet | 2 |
+ | N3 | place | hamlet | 3 |
+ When importing
+ Then place_addressline contains
+ | object | address | fromarea | isaddress |
+ | N1 | N3 | False | True |
+ | N1 | N2 | False | False |
+
+ Scenario: boundaries around the place are added to the address
+ Given the grid
+ | 1 | | 4 | | 7 | 10 |
+ | 2 | | 5 | | 8 | 11 |
+ | | | | | | |
+ | | | | | | |
+ | | | 6 | | 9 | |
+ | | 99 | | | | |
+ | 3 | | | | | 12 |
+ And the named places
+ | osm | class | type | admin | geometry |
+ | R1 | boundary | administrative | 3 | (1,2,3,12,11,10,7,8,9,6,5,4,1) |
+ | R2 | boundary | administrative | 4 | (2,3,12,11,8,9,6,5,2) |
+ | N1 | place | square | 15 | 99 |
+ When importing
+ Then place_addressline contains
+ | object | address | isaddress |
+ | N1 | R1 | True |
+ | N1 | R2 | True |
+
+ Scenario: with boundaries of same rank the one with the closer centroid is prefered
+ Given the grid
+ | 1 | | | 3 | | 5 |
+ | | 9 | | | | |
+ | 2 | | | 4 | | 6 |
+ And the named places
+ | osm | class | type | admin | geometry |
+ | R1 | boundary | administrative | 8 | (1,2,4,3,1) |
+ | R2 | boundary | administrative | 8 | (1,2,6,5,1) |
+ | N1 | place | square | 15 | 9 |
+ When importing
+ Then place_addressline contains
+ | object | address | isaddress |
+ | N1 | R1 | True |
+ | N1 | R2 | False |
+
+ Scenario: boundary areas are preferred over place nodes in the address
+ Given the grid
+ | 1 | | | | | | 3 |
+ | | 5 | | | | | |
+ | | 6 | | | | | |
+ | 2 | | | | | | 4 |
+ And the named places
+ | osm | class | type | admin | geometry |
+ | N1 | place | square | 15 | 5 |
+ | N2 | place | city | 15 | 6 |
+ | R1 | place | city | 8 | (1,2,4,3,1) |
+ When importing
+ Then place_addressline contains
+ | object | address | isaddress | cached_rank_address |
+ | N1 | R1 | True | 16 |
+ | N1 | N2 | False | 16 |
+
+ Scenario: place nodes outside a smaller ranked area are ignored
+ Given the grid
+ | 1 | | 2 | |
+ | | 7 | | 9 |
+ | 4 | | 3 | |
+ And the named places
+ | osm | class | type | admin | geometry |
+ | N1 | place | square | 15 | 7 |
+ | N2 | place | city | 15 | 9 |
+ | R1 | place | city | 8 | (1,2,3,4,1) |
+ When importing
+ Then place_addressline contains
+ | object | address | isaddress | cached_rank_address |
+ | N1 | R1 | True | 16 |
+ And place_addressline doesn't contain
+ | object | address |
+ | N1 | N2 |
+
+
+ Scenario: place nodes close enough to smaller ranked place nodes are included
+ Given the 0.002 grid
+ | 2 | | 3 | 1 |
+ And the named places
+ | osm | class | type | geometry |
+ | N1 | place | square | 1 |
+ | N2 | place | hamlet | 2 |
+ | N3 | place | quarter | 3 |
+ When importing
+ Then place_addressline contains
+ | object | address | fromarea | isaddress |
+ | N1 | N2 | False | True |
+ | N1 | N3 | False | True |
+
+
+ Scenario: place nodes too far away from a smaller ranked place nodes are marked non-address
+ Given the 0.002 grid
+ | 2 | | | 1 | | 3 |
+ And the named places
+ | osm | class | type | geometry |
+ | N1 | place | square | 1 |
+ | N2 | place | hamlet | 2 |
+ | N3 | place | quarter | 3 |
+ When importing
+ Then place_addressline contains
+ | object | address | fromarea | isaddress |
+ | N1 | N2 | False | True |
+ | N1 | N3 | False | False |
+
+
# github #121
Scenario: Roads crossing boundaries should contain both states
Given the grid
| object | address |
| W1 | W11 |
- Scenario: Roads should not contain boundaries they touch in a end point
+ Scenario: Roads should not contain boundaries they touch in a middle point
Given the grid
| 1 | | | 2 | | 3 |
| | 7 | | 8 | | |
Then place_addressline contains
| object | address |
| W93 | R4 |
+
+ Scenario: squares do not appear in the address of a street
+ Given the grid
+ | | 1 | | 2 | |
+ | 8 | | | | 9 |
+ | | 4 | | 3 | |
+ And the named places
+ | osm | class | type | geometry |
+ | W1 | highway | residential | 8, 9 |
+ | W2 | place | square | (1, 2, 3 ,4, 1) |
+ When importing
+ Then place_addressline doesn't contain
+ | object | address |
+ | W1 | W2 |
+
Feature: Rank assignment
Tests for assignment of search and address ranks.
- Scenario: Ranks for place nodes are assinged according to thier type
+ Scenario: Ranks for place nodes are assigned according to their type
Given the named places
| osm | class | type |
| N1 | foo | bar |
| R20 | 12 | 22 |
| R21 | 14 | 24 |
| R22 | 16 | 25 |
+
+ Scenario: admin levels contained in a place area must not overtake address ranks
+ Given the named places
+ | osm | class | type | admin | geometry |
+ | R10 | place | city | 15 | (0 0, 0 2, 2 0, 0 0) |
+ | R20 | boundary | administrative | 6 | (0 0, 0 1, 1 0, 0 0) |
+ When importing
+ Then placex contains
+ | object | rank_search | rank_address |
+ | R10 | 16 | 16 |
+ | R20 | 12 | 18 |
+
+ Scenario: admin levels overlapping a place area are not demoted
+ Given the named places
+ | osm | class | type | admin | geometry |
+ | R10 | place | city | 15 | (0 0, 0 2, 2 0, 0 0) |
+ | R20 | boundary | administrative | 6 | (-1 0, 0 1, 1 0, -1 0) |
+ When importing
+ Then placex contains
+ | object | rank_search | rank_address |
+ | R10 | 16 | 16 |
+ | R20 | 12 | 12 |
+
+ Scenario: admin levels with equal area as a place area are not demoted
+ Given the named places
+ | osm | class | type | admin | geometry |
+ | R10 | place | city | 15 | (0 0, 0 2, 2 0, 0 0) |
+ | R20 | boundary | administrative | 6 | (0 0, 0 2, 2 0, 0 0) |
+ When importing
+ Then placex contains
+ | object | rank_search | rank_address |
+ | R10 | 16 | 16 |
+ | R20 | 12 | 12 |