+-- Splits the line at the given point and returns the two parts
+-- in a multilinestring.
+CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
+RETURNS GEOMETRY
+ AS $$
+BEGIN
+ RETURN ST_Split(ST_Snap(line, point, 0.0005), point);
+END;
+$$
+LANGUAGE plpgsql;
+
+
CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry) RETURNS INTEGER
AS $$
DECLARE
FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
- -- If there is a place of a type other than place/house, use that because
- -- it is guaranteed to be the original node. For place/house types use the
- -- one with the smallest id because the original node was created first.
- -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
- select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
- and indexed_status < 100 and housenumber is not NULL
- order by (type = 'address'),place_id limit 1 INTO nextnode;
+ 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.place_id IS NOT NULL THEN
+ 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
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
startnumber := housenum;
sectiongeo := ST_Reverse(sectiongeo);
END IF;
-
- -- a correction for odd/even is NOT necessary anymore (e.g. if interpolationtype=even, but start/endnumber != even)
- -- see PlaceLookup.php
- -- keep for compatibility with previous versions
- delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id
- and place_id != prevnode.place_id and class = 'place'
- and type = 'house';
insert into location_property_osmline
- values (sectiongeo, nextval('seq_place'), partition, wayid, NULL, startnumber, endnumber, interpolationtype, prevnode.admin_level,
- 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,
--DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
- RETURN NEW; -- @DIFFUPDATES@ The following is not needed until doing diff updates, and slows the main index process down
+ RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
IF NEW.rank_address > 0 THEN
IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
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, so that ALL places in placex, that are needed for reparenting, are 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);
- -- mark descendants for reparenting
- UPDATE placex SET indexed_status = 2 WHERE parent_place_id = OLD.place_id;
- 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;
END IF;
IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN
-
- -- 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);
- END IF;
RETURN NEW;
END IF;
--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;
---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
+ -- 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
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
NEW.parent_place_id = 0;
parent_place_id_rank = 0;
+
-- convert isin to array of tokenids
isin_tokens := '{}'::int[];
IF NEW.isin IS NOT NULL THEN
END IF;
END IF;
+ -- %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
FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP
nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
END LOOP;
END IF;
+ -- %NOTIGERDATA% END IF;
-- RAISE WARNING 'ISIN: %', isin_tokens;
UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
-- interpolations are special
- IF OLD.class = 'place' and OLD.type = 'houses' THEN
+ IF OLD.osm_type='W' and OLD.class = 'place' and OLD.type = 'houses' THEN
UPDATE location_property_osmline set indexed_status = 100 where osm_id = OLD.osm_id; -- osm_id = wayid (=old.osm_id)
END IF;
--DEBUG: RAISE WARNING '-----------------------------------------------------------------------------------';
--DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
- RAISE WARNING 'X3366 - place_isnert';
-- 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,
-- 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
- IF existing.osm_type IS NOT NULL THEN
- -- pathological case caused by the triggerless copy into place during initial import
- -- force delete even for large areas, it will be reinserted later
- UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
- DELETE from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
- END IF;
- -- 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);
- RAISE WARNING 'X3399 - updated nodes of interpolation line';
+
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;
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
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
+ -- 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);
+ END IF;
END IF;
RETURN NULL;
END IF;
-END;
+END;
$$ LANGUAGE plpgsql;
countryname HSTORE;
hadcountry BOOLEAN;
BEGIN
-
-- first query osmline (interpolation lines)
select parent_place_id, calculated_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
IF for_place_id IS NOT NULL THEN
searchhousenumber = in_housenumber::text;
END IF;
-
+
--then query tiger data
+ -- %NOTIGERDATA% IF 0 THEN
IF for_place_id IS NULL THEN
select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger
WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber
searchhousenumber = in_housenumber::text;
END IF;
END IF;
-
+ -- %NOTIGERDATA% END IF;
+
+ -- %NOAUXDATA% IF 0 THEN
IF for_place_id IS NULL THEN
select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux
WHERE place_id = in_place_id
INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
END IF;
-
+ -- %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
WHERE place_id = in_place_id and rank_address = 30
projects / nominatim.git / blobdiff