+-- 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
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, street, coalesce(prevnode.postcode, defpostalcode),
+ 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
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)
+ -- 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);
- -- mark descendants for reparenting
- UPDATE placex SET indexed_status = 2 WHERE parent_place_id = OLD.place_id;
+ -- 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;
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;
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 there isn't an existing item in location_property_osmline, just add it
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
i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place, NEW.postcode, NEW.country_code, NEW.geometry);
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, '')
-- 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);
+ i = insert_osmline(NEW.osm_id, NEW.housenumber, NEW.street, NEW.addr_place,
+ NEW.postcode, NEW.country_code, NEW.geometry);
+
+ -- 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);
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