+-- 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 geom LOOP
+ IF NOT ST_IsEmpty(geo.geom) AND ST_GeometryType(geo.geom) in ('ST_Polygon','ST_MultiPolygon') THEN
+ added := added + 1;
+ RETURN NEXT geo.geom;
+ 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 geom LOOP
+ RETURN NEXT geo.geom;
+ END LOOP;
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql;
projects / nominatim.git / blobdiff