+
+CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT,
+ in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER
+ AS $$
+DECLARE
+
+ newpoints INTEGER;
+ place_centroid GEOMETRY;
+ out_partition INTEGER;
+ out_parent_place_id BIGINT;
+ location RECORD;
+ address_street_word_id INTEGER;
+ out_postcode TEXT;
+
+BEGIN
+
+ place_centroid := ST_Centroid(pointgeo);
+ out_partition := get_partition(place_centroid, in_countrycode);
+ out_parent_place_id := null;
+
+ address_street_word_id := get_name_id(make_standard_name(in_street));
+ IF address_street_word_id IS NOT NULL THEN
+ FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+ out_parent_place_id := location.place_id;
+ END LOOP;
+ END IF;
+
+ IF out_parent_place_id IS NULL THEN
+ FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+ out_parent_place_id := location.place_id;
+ END LOOP;
+ END IF;
+
+ out_postcode := in_postcode;
+ IF out_postcode IS NULL THEN
+ SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
+ END IF;
+ IF out_postcode IS NULL THEN
+ out_postcode := getNearestPostcode(out_partition, place_centroid);
+ END IF;
+
+ newpoints := 0;
+ insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
+ values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+ newpoints := newpoints + 1;
+
+ RETURN newpoints;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
+ AS $$
+DECLARE
+ result TEXT[];
+ i INTEGER;
+BEGIN
+
+ FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+ IF members[i+1] = member THEN
+ result := result || members[i];
+ END IF;
+ END LOOP;
+
+ return result;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
+ AS $$
+DECLARE
+ i INTEGER;
+BEGIN
+
+ FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+ IF members[i+1] = ANY(memberLabels) THEN
+ RETURN NEXT members[i];
+ END IF;
+ END LOOP;
+
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- See: http://stackoverflow.com/questions/6410088/how-can-i-mimic-the-php-urldecode-function-in-postgresql
+CREATE OR REPLACE FUNCTION decode_url_part(p varchar) RETURNS varchar
+ AS $$
+SELECT convert_from(CAST(E'\\x' || array_to_string(ARRAY(
+ SELECT CASE WHEN length(r.m[1]) = 1 THEN encode(convert_to(r.m[1], 'SQL_ASCII'), 'hex') ELSE substring(r.m[1] from 2 for 2) END
+ FROM regexp_matches($1, '%[0-9a-f][0-9a-f]|.', 'gi') AS r(m)
+), '') AS bytea), 'UTF8');
+$$
+LANGUAGE SQL IMMUTABLE STRICT;
+
+CREATE OR REPLACE FUNCTION catch_decode_url_part(p varchar) RETURNS varchar
+ AS $$
+DECLARE
+BEGIN
+ RETURN decode_url_part(p);
+EXCEPTION
+ WHEN others THEN return null;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+DROP TYPE wikipedia_article_match CASCADE;
+create type wikipedia_article_match as (
+ language TEXT,
+ title TEXT,
+ importance FLOAT
+);
+
+CREATE OR REPLACE FUNCTION get_wikipedia_match(extratags HSTORE, country_code varchar(2)) RETURNS wikipedia_article_match
+ AS $$
+DECLARE
+ langs TEXT[];
+ i INT;
+ wiki_article TEXT;
+ wiki_article_title TEXT;
+ wiki_article_language TEXT;
+ result wikipedia_article_match;
+BEGIN
+ langs := ARRAY['english','country','ar','bg','ca','cs','da','de','en','es','eo','eu','fa','fr','ko','hi','hr','id','it','he','lt','hu','ms','nl','ja','no','pl','pt','kk','ro','ru','sk','sl','sr','fi','sv','tr','uk','vi','vo','war','zh'];
+ i := 1;
+ WHILE langs[i] IS NOT NULL LOOP
+ wiki_article := extratags->(case when langs[i] in ('english','country') THEN 'wikipedia' ELSE 'wikipedia:'||langs[i] END);
+ IF wiki_article is not null THEN
+ wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
+ wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
+ wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
+ wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
+ wiki_article := replace(wiki_article,' ','_');
+ wiki_article_title := trim(split_part(wiki_article, ':', 2));
+ IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+ wiki_article_title := trim(wiki_article);
+ wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END;
+ ELSE
+ wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
+ END IF;
+
+ select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
+ from wikipedia_article
+ where language = wiki_article_language and
+ (title = wiki_article_title OR title = catch_decode_url_part(wiki_article_title) OR title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ UNION ALL
+ select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
+ from wikipedia_redirect join wikipedia_article on (wikipedia_redirect.language = wikipedia_article.language and wikipedia_redirect.to_title = wikipedia_article.title)
+ where wikipedia_redirect.language = wiki_article_language and
+ (from_title = wiki_article_title OR from_title = catch_decode_url_part(wiki_article_title) OR from_title = replace(catch_decode_url_part(wiki_article_title),E'\\',''))
+ order by importance desc limit 1 INTO result;
+
+ IF result.language is not null THEN
+ return result;
+ END IF;
+ END IF;
+ i := i + 1;
+ END LOOP;
+ RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION quad_split_geometry(geometry GEOMETRY, maxarea FLOAT, maxdepth INTEGER)
+ RETURNS SETOF GEOMETRY
+ AS $$
+DECLARE
+ xmin FLOAT;
+ ymin FLOAT;
+ xmax FLOAT;
+ ymax FLOAT;
+ xmid FLOAT;
+ ymid FLOAT;
+ secgeo GEOMETRY;
+ secbox GEOMETRY;
+ seg INTEGER;
+ geo RECORD;
+ area FLOAT;
+ remainingdepth INTEGER;
+ added INTEGER;
+
+BEGIN
+
+-- RAISE WARNING 'quad_split_geometry: maxarea=%, depth=%',maxarea,maxdepth;
+
+ IF (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') OR NOT ST_IsValid(geometry)) THEN
+ RETURN NEXT geometry;
+ RETURN;
+ END IF;
+
+ remainingdepth := maxdepth - 1;
+ area := ST_AREA(geometry);
+ IF remainingdepth < 1 OR area < maxarea THEN
+ RETURN NEXT geometry;
+ RETURN;
+ END IF;
+
+ xmin := st_xmin(geometry);
+ xmax := st_xmax(geometry);
+ ymin := st_ymin(geometry);
+ ymax := st_ymax(geometry);
+ secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(ymin,xmin),ST_Point(ymax,xmax)),4326);
+
+ -- if the geometry completely covers the box don't bother to slice any more
+ IF ST_AREA(secbox) = area THEN
+ RETURN NEXT geometry;
+ RETURN;
+ END IF;
+
+ xmid := (xmin+xmax)/2;
+ ymid := (ymin+ymax)/2;
+
+ added := 0;
+ FOR seg IN 1..4 LOOP
+
+ IF seg = 1 THEN
+ secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin),ST_Point(xmid,ymid)),4326);
+ END IF;
+ IF seg = 2 THEN
+ secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymid),ST_Point(xmid,ymax)),4326);
+ END IF;
+ IF seg = 3 THEN
+ secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymin),ST_Point(xmax,ymid)),4326);
+ END IF;
+ IF seg = 4 THEN
+ secbox := ST_SetSRID(ST_MakeBox2D(ST_Point(xmid,ymid),ST_Point(xmax,ymax)),4326);
+ END IF;
+
+ IF st_intersects(geometry, secbox) THEN
+ secgeo := st_intersection(geometry, secbox);
+ IF NOT ST_IsEmpty(secgeo) AND ST_GeometryType(secgeo) in ('ST_Polygon','ST_MultiPolygon') THEN
+ FOR geo IN select quad_split_geometry(secgeo, maxarea, remainingdepth) as geometry LOOP
+ IF NOT ST_IsEmpty(geo.geometry) AND ST_GeometryType(geo.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
+ added := added + 1;
+ RETURN NEXT geo.geometry;
+ END IF;
+ END LOOP;
+ END IF;
+ END IF;
+ END LOOP;
+
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION split_geometry(geometry GEOMETRY)
+ RETURNS SETOF GEOMETRY
+ AS $$
+DECLARE
+ geo RECORD;
+BEGIN
+ -- 10000000000 is ~~ 1x1 degree
+ FOR geo IN select quad_split_geometry(geometry, 0.25, 20) as geometry LOOP
+ RETURN NEXT geo.geometry;
+ END LOOP;
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql;
projects / nominatim.git / blobdiff