endnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
IF startnumber IS NOT NULL AND endnumber IS NOT NULL
- AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber
+ AND startnumber != endnumber
AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
IF (startnumber > endnumber) THEN
END IF;
insert into location_property_osmline
- values (sectiongeo, nextval('seq_place'), partition, wayid, NULL, startnumber, endnumber,
- interpolationtype, street, coalesce(prevnode.postcode, defpostalcode),
- calculated_country_code, geometry_sector, 2, now());
+ values (sectiongeo, nextval('seq_place'), partition, wayid, NULL,
+ startnumber, endnumber, interpolationtype,
+ coalesce(street, prevnode.street, nextnode.street),
+ coalesce(addr_place, prevnode.addr_place, nextnode.addr_place),
+ coalesce(defpostalcode, prevnode.postcode, nextnode.postcode),
+ calculated_country_code, geometry_sector, 2, now());
END IF;
-- early break if we are out of line string,
delete from location_property_osmline where place_id = OLD.place_id;
RETURN NULL;
END IF;
-
+
IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
RETURN NEW;
END IF;
-
- IF OLD.indexed_status = 2 and NEW.indexed_status=0 THEN
- -- 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 = 2
- -- => index.c: sets index_status back to 0
- -- => triggers this function)
- place_centroid := ST_PointOnSurface(NEW.linegeo);
- -- marking descendants for reparenting is not needed, because there are actually no descendants for interpolation lines
- NEW.parent_place_id = get_interpolation_parent(NEW.osm_id, NEW.street, null, NEW.partition, place_centroid, NEW.linegeo); -- addr_place (3rd param) is not necessarily needed
- 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)
+ 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.partition, place_centroid, NEW.linegeo);
+ return NEW;
END;
$$
LANGUAGE plpgsql;
--RAISE WARNING 'before low level% %', NEW.place_id, NEW.rank_search;
+ -- ---------------------------------------------------------------------------
-- For low level elements we inherit from our parent road
IF (NEW.rank_search > 27 OR (NEW.type = 'postcode' AND NEW.rank_search = 25)) THEN
END IF;
END IF;
+ -- Is this node part of an interpolation?
+ IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
+ FOR location IN
+ SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x
+ WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
+ LIMIT 1
+ LOOP
+ NEW.parent_place_id := location.parent_place_id;
+ END LOOP;
+ END IF;
+
+ -- Is this node part of a way?
IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
---RAISE WARNING 'x1';
- -- Is this node part of a way? search for the way in placex AND location_property_osmline (for interpolation lines)
- FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.class, p.type, 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.street, p.addr_place 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)
- UNION
- select q.place_id, q.osm_id, q.parent_place_id, 'place' as class, 'houses' as type, 30 as rank_search, null as street,
- null as addr_place from location_property_osmline q, planet_osm_ways x
- where q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes)
LOOP
-
---RAISE WARNING '%', location;
+
-- Way IS a road then we are on it - that must be our road
- IF location.rank_search = 26 AND NEW.parent_place_id IS NULL THEN
+ IF location.rank_search < 28 AND NEW.parent_place_id IS NULL THEN
--RAISE WARNING 'node in way that is a street %',location;
NEW.parent_place_id := location.place_id;
END IF;
- -- If this way is a street interpolation line then it is probably as good as we are going to get
- IF NEW.parent_place_id IS NULL AND location.class = 'place' and location.type='houses' THEN
- NEW.parent_place_id := location.parent_place_id;
- END IF;
-
- -- Is the WAY part of a relation
- IF NEW.parent_place_id IS NULL THEN
- FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
- LOOP
- -- At the moment we only process one type of relation - associatedStreet
- IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
- FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
- IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
- --RAISE WARNING 'node in way that is in a relation %',relation;
- SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
- and rank_search = 26 and name is not null INTO NEW.parent_place_id;
- END IF;
- END LOOP;
- END IF;
- END 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));
END IF;
END IF;
+ -- Is the WAY part of a relation
+ IF NEW.parent_place_id IS NULL THEN
+ FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id]
+ LOOP
+ -- At the moment we only process one type of relation - associatedStreet
+ IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN
+ FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+ IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
+ --RAISE WARNING 'node in way that is in a relation %',relation;
+ SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
+ and rank_search = 26 and name is not null INTO NEW.parent_place_id;
+ END IF;
+ END LOOP;
+ END IF;
+ END LOOP;
+ END IF;
+
END LOOP;
END IF;
-- RAISE WARNING ' INDEXING Started:';
-- RAISE WARNING ' INDEXING: %',NEW;
+ -- ---------------------------------------------------------------------------
+ -- Full indexing
+
IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
-- see if we have any special relation members
-- RAISE WARNING 'Invalid Geometry: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
RETURN null;
END IF;
-
- -- decide, whether its an osm interpolation line => insert_osmline, or else just insert into placex
- IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
+
+ -- decide, whether it is an osm interpolation line => insert_osmline, or else just insert into placex
+ IF NEW.class='place' and NEW.type='houses' and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString' THEN
-- Have we already done this place?
select * from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existing;
-
+
-- Get the existing place_id
select * from location_property_osmline where osm_id = NEW.osm_id INTO existingline;
-
+
-- Handle a place changing type by removing the old data (this trigger is executed BEFORE INSERT of the NEW tupel)
- -- My generated 'place' types are causing havok because they overlap with real keys
- -- TODO: move them to their own special purpose key/class to avoid collisions
IF existing.osm_type IS NULL THEN
DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
END IF;
-
+
DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
-
- -- To paraphrase, if there isn't an existing item
- IF existingline.osm_id IS NULL THEN
- -- insert new line into location_property_osmline, use function insert_osmline
+
+ -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes)
+ IF existingline.osm_id IS NOT NULL THEN
+ delete from location_property_osmline where osm_id = NEW.osm_id;
+ END IF;
+
+ -- for interpolations invalidate all nodes on the line
+ update placex p set indexed_status = 2
+ from planet_osm_ways w
+ where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
+ -- insert new line into location_property_osmline, use function insert_osmline
+
+
+ IF existing.osm_type IS NULL THEN
i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry);
- RETURN NEW;
+ return NEW;
END IF;
- 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, '')
+
+ 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, '')
- OR coalesce(existing.admin_level, 15) != coalesce(NEW.admin_level, 15)
OR existing.geometry::text != NEW.geometry::text
THEN
geometry = NEW.geometry
where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
- -- update method for interpolation lines: delete all old interpolation lines with same osm_id (update on place) and insert the new one(s) (they can be split up, if they have > 2 nodes)
- delete from location_property_osmline where osm_id = NEW.osm_id;
i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry);
END IF;
-
- -- for interpolations invalidate all nodes on the line
- update placex p set indexed_status = 2 from planet_osm_ways w where w.id = NEW.osm_id and p.osm_type = 'N' and p.osm_id = any(w.nodes);
+
RETURN NULL;
-
+
ELSE -- insert to placex
-
- IF FALSE and NEW.osm_type = 'R' THEN
- select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
- --DEBUG: RAISE WARNING '%', existingplacex;
- END IF;
+
-- Patch in additional country names
IF NEW.admin_level = 2 AND NEW.type = 'administrative' AND NEW.country_code is not null THEN
select coalesce(country_name.name || NEW.name,NEW.name) from country_name where country_name.country_code = lower(NEW.country_code) INTO NEW.name;
-- 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)
- -- needed for test case features/db/import: Scenario: addr:street added to housenumbers
IF NEW.osm_type='N' and NEW.class='place' and NEW.type='house' THEN
-- Is this node part of an interpolation line? search for it in location_property_osmline and mark the interpolation line for reparenting
update location_property_osmline p set indexed_status = 2 from planet_osm_ways w where p.linegeo && NEW.geometry and p.osm_id = w.id and NEW.osm_id = any(w.nodes);
IF for_place_id IS NULL THEN
select parent_place_id, calculated_country_code, housenumber, rank_search, postcode, name, class, type from placex
- WHERE place_id = in_place_id and rank_address = 30
+ WHERE place_id = in_place_id and rank_search > 27
INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
- admin_level, fromarea, isaddress,
+ 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
from place_addressline join placex on (address_place_id = placex.place_id)
projects / nominatim.git / blobdiff