+-- 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
--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 place_addressline WHERE place_id = NEW.place_id;
result := deleteRoad(NEW.partition, NEW.place_id);
result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search);
- UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
+ UPDATE placex set linked_place_id = null, indexed_status = 2
+ where linked_place_id = NEW.place_id;
IF NEW.linked_place_id is not null THEN
RETURN NEW;
select * from search_name where place_id = NEW.parent_place_id INTO location;
NEW.calculated_country_code := location.country_code;
+ -- Merge the postcode into the parent's address if necessary XXXX
+ IF NEW.postcode IS NOT NULL THEN
+ isin_tokens := '{}'::int[];
+ address_street_word_id := getorcreate_word_id(make_standard_name(NEW.postcode));
+ IF address_street_word_id is not null
+ and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+ address_street_word_id := getorcreate_name_id(make_standard_name(NEW.postcode));
+ IF address_street_word_id is not null
+ and not ARRAY[address_street_word_id] <@ location.nameaddress_vector THEN
+ isin_tokens := isin_tokens || address_street_word_id;
+ END IF;
+ IF isin_tokens != '{}'::int[] THEN
+ UPDATE search_name
+ SET nameaddress_vector = search_name.nameaddress_vector || isin_tokens
+ WHERE place_id = NEW.parent_place_id;
+ END IF;
+ END IF;
+
--RAISE WARNING '%', NEW.name;
-- If there is no name it isn't searchable, don't bother to create a search record
IF NEW.name is 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;
END;
$$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION get_name_by_language(name hstore, languagepref TEXT[]) RETURNS TEXT
AS $$
DECLARE
- search TEXT[];
- found BOOLEAN;
+ result TEXT;
BEGIN
-
IF name is null THEN
RETURN null;
END IF;
- search := languagepref;
-
- FOR j IN 1..array_upper(search, 1) LOOP
- IF name ? search[j] AND trim(name->search[j]) != '' THEN
- return trim(name->search[j]);
+ FOR j IN 1..array_upper(languagepref,1) LOOP
+ IF name ? languagepref[j] THEN
+ result := trim(name->languagepref[j]);
+ IF result != '' THEN
+ return result;
+ END IF;
END IF;
END LOOP;
-- anything will do as a fallback - just take the first name type thing there is
- search := avals(name);
- RETURN search[1];
+ RETURN trim((avals(name))[1]);
END;
$$
LANGUAGE plpgsql IMMUTABLE;
$$
LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
+--housenumber only needed for tiger data
+CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, housenumber INTEGER, languagepref TEXT[]) RETURNS TEXT
AS $$
DECLARE
result TEXT[];
result := '{}';
prevresult := '';
- FOR location IN select * from get_addressdata(for_place_id) where isaddress order by rank_address desc LOOP
+ FOR location IN select * from get_addressdata(for_place_id, housenumber) where isaddress order by rank_address desc LOOP
currresult := trim(get_name_by_language(location.name, languagepref));
IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN
result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref));
distance FLOAT
);
-CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline
AS $$
DECLARE
- for_place_id BIGINT;
+ for_place_id BIGINT;--parent_place_id
result TEXT[];
search TEXT[];
found INTEGER;
countryname HSTORE;
hadcountry BOOLEAN;
BEGIN
+ --first query tiger data
+ -- %NOTIGERDATA% IF 0 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
+ INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
+ IF for_place_id IS NOT NULL THEN
+ searchhousenumber = in_housenumber::text;
+ END IF;
+ -- %NOTIGERDATA% END IF;
- select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_tiger
- WHERE place_id = in_place_id
- INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype;
-
+ -- %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
projects / nominatim.git / blobdiff