X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/631e8d09ab8bcfedb4e211e6453c684312a0140a..5f97080c738f59d58d9ec334b31be300bf6d04a8:/sql/partitions.src.sql diff --git a/sql/partitions.src.sql b/sql/partitions.src.sql index 9532216f..e31eadff 100644 --- a/sql/partitions.src.sql +++ b/sql/partitions.src.sql @@ -17,8 +17,8 @@ CREATE INDEX idx_location_area_country_geometry ON location_area_country USING G CREATE TABLE search_name_country () INHERITS (search_name_blank); CREATE INDEX idx_search_name_country_place_id ON search_name_country USING BTREE (place_id); CREATE INDEX idx_search_name_country_centroid ON search_name_country USING GIST (centroid); -CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector gin__int_ops); -CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops); +CREATE INDEX idx_search_name_country_name_vector ON search_name_country USING GIN (name_vector gin__int_ops) WITH (fastupdate = off); +CREATE INDEX idx_search_name_country_nameaddress_vector ON search_name_country USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off); -- start CREATE TABLE location_area_large_-partition- () INHERITS (location_area_large); @@ -28,8 +28,8 @@ CREATE INDEX idx_location_area_large_-partition-_geometry ON location_area_large CREATE TABLE search_name_-partition- () INHERITS (search_name_blank); CREATE INDEX idx_search_name_-partition-_place_id ON search_name_-partition- USING BTREE (place_id); CREATE INDEX idx_search_name_-partition-_centroid ON search_name_-partition- USING GIST (centroid); -CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops); -CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops); +CREATE INDEX idx_search_name_-partition-_name_vector ON search_name_-partition- USING GIN (name_vector gin__int_ops) WITH (fastupdate = off); +CREATE INDEX idx_search_name_-partition-_nameaddress_vector ON search_name_-partition- USING GIN (nameaddress_vector gin__int_ops) WITH (fastupdate = off); CREATE TABLE location_property_-partition- () INHERITS (location_property); CREATE INDEX idx_location_property_-partition-_place_id ON location_property_-partition- USING BTREE (place_id); @@ -37,6 +37,15 @@ CREATE INDEX idx_location_property_-partition-_parent_place_id ON location_prope CREATE INDEX idx_location_property_-partition-_housenumber_parent_place_id ON location_property_-partition- USING BTREE (parent_place_id, housenumber); --CREATE INDEX idx_location_property_-partition-_centroid ON location_property_-partition- USING GIST (centroid); +CREATE TABLE location_road_-partition- ( + partition integer, + place_id INTEGER, + country_code VARCHAR(2) + ); +SELECT AddGeometryColumn('location_road_-partition-', 'geometry', 4326, 'GEOMETRY', 2); +CREATE INDEX idx_location_road_-partition-_geometry ON location_road_-partition- USING GIST (geometry); +CREATE INDEX idx_location_road_-partition-_place_id ON location_road_-partition- USING BTREE (place_id); + -- end create or replace function getNearFeatures(in_partition INTEGER, point GEOMETRY, maxrank INTEGER, isin_tokens INT[]) RETURNS setof nearfeature AS $$ @@ -47,12 +56,19 @@ BEGIN -- start IF in_partition = -partition- THEN FOR r IN - SELECT place_id, keywords, rank_address, rank_search, ST_Distance(point, centroid) as distance FROM ( + SELECT place_id, keywords, rank_address, rank_search, min(ST_Distance(point, centroid)) as distance FROM ( SELECT * FROM location_area_large_-partition- WHERE ST_Contains(geometry, point) and rank_search < maxrank UNION ALL SELECT * FROM location_area_country WHERE ST_Contains(geometry, point) and rank_search < maxrank ) as location_area - ORDER BY rank_address desc, isin_tokens && keywords desc, isguess asc, ST_Distance(point, centroid) * CASE WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 ELSE 1 END ASC + GROUP BY place_id, keywords, rank_address, rank_search, isguess, centroid + ORDER BY rank_address desc, isin_tokens && keywords desc, isguess asc, + ST_Distance(point, centroid) * + CASE + WHEN rank_address = 16 AND rank_search = 15 THEN 0.2 -- capital city + WHEN rank_address = 16 AND rank_search = 16 THEN 0.25 -- city + WHEN rank_address = 16 AND rank_search = 17 THEN 0.5 -- town + ELSE 1 END ASC -- everything else LOOP RETURN NEXT r; END LOOP; @@ -91,14 +107,12 @@ DECLARE BEGIN IF in_rank_search <= 4 THEN - DELETE FROM location_area_country where place_id = in_place_id; INSERT INTO location_area_country values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry); RETURN TRUE; END IF; -- start IF in_partition = -partition- THEN - DELETE FROM location_area_large_-partition- where place_id = in_place_id; INSERT INTO location_area_large_-partition- values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, in_centroid, in_geometry); RETURN TRUE; END IF; @@ -142,7 +156,8 @@ END $$ LANGUAGE plpgsql; -create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) RETURNS setof nearfeature AS $$ +create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) + RETURNS setof nearfeature AS $$ DECLARE r nearfeature%rowtype; BEGIN @@ -172,18 +187,18 @@ LANGUAGE plpgsql; create or replace function insertSearchName( in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_name_vector INTEGER[], in_nameaddress_vector INTEGER[], - in_rank_search INTEGER, in_rank_address INTEGER, + in_rank_search INTEGER, in_rank_address INTEGER, in_importance FLOAT, in_centroid GEOMETRY) RETURNS BOOLEAN AS $$ DECLARE BEGIN DELETE FROM search_name WHERE place_id = in_place_id; - INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code, + INSERT INTO search_name values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, in_name_vector, in_nameaddress_vector, in_centroid); IF in_rank_search <= 4 THEN DELETE FROM search_name_country WHERE place_id = in_place_id; - INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, 0, in_country_code, + INSERT INTO search_name_country values (in_place_id, in_rank_search, in_rank_address, in_importance, in_country_code, in_name_vector, in_nameaddress_vector, in_centroid); RETURN TRUE; END IF; @@ -223,3 +238,115 @@ BEGIN END $$ LANGUAGE plpgsql; + +create or replace function insertLocationRoad( + in_partition INTEGER, in_place_id integer, in_country_code VARCHAR(2), in_geometry GEOMETRY) RETURNS BOOLEAN AS $$ +DECLARE +BEGIN + +-- start + IF in_partition = -partition- THEN + DELETE FROM location_road_-partition- where place_id = in_place_id; + INSERT INTO location_road_-partition- values (in_partition, in_place_id, in_country_code, in_geometry); + RETURN TRUE; + END IF; +-- end + + RAISE EXCEPTION 'Unknown partition %', in_partition; + RETURN FALSE; +END +$$ +LANGUAGE plpgsql; + +create or replace function deleteRoad(in_partition INTEGER, in_place_id integer) RETURNS BOOLEAN AS $$ +DECLARE +BEGIN + +-- start + IF in_partition = -partition- THEN + DELETE FROM location_road_-partition- where place_id = in_place_id; + RETURN TRUE; + END IF; +-- end + + RAISE EXCEPTION 'Unknown partition %', in_partition; + + RETURN FALSE; +END +$$ +LANGUAGE plpgsql; + +create or replace function getNearestRoadFeature(in_partition INTEGER, point GEOMETRY) RETURNS setof nearfeature AS $$ +DECLARE + r nearfeature%rowtype; + search_diameter FLOAT; +BEGIN + +-- start + IF in_partition = -partition- THEN + search_diameter := 0.00005; + WHILE search_diameter < 0.1 LOOP + FOR r IN + SELECT place_id, null, null, null, + ST_Distance(geometry, point) as distance + FROM location_road_-partition- + WHERE ST_DWithin(geometry, point, search_diameter) + ORDER BY distance ASC limit 1 + LOOP + RETURN NEXT r; + RETURN; + END LOOP; + search_diameter := search_diameter * 2; + END LOOP; + RETURN; + END IF; +-- end + + RAISE EXCEPTION 'Unknown partition %', in_partition; +END +$$ +LANGUAGE plpgsql; + +create or replace function getNearestParellelRoadFeature(in_partition INTEGER, line GEOMETRY) RETURNS setof nearfeature AS $$ +DECLARE + r nearfeature%rowtype; + search_diameter FLOAT; + p1 GEOMETRY; + p2 GEOMETRY; + p3 GEOMETRY; +BEGIN + + IF st_geometrytype(line) not in ('ST_LineString') THEN + RETURN; + END IF; + + p1 := ST_Line_Interpolate_Point(line,0); + p2 := ST_Line_Interpolate_Point(line,0.5); + p3 := ST_Line_Interpolate_Point(line,1); + +-- start + IF in_partition = -partition- THEN + search_diameter := 0.0005; + WHILE search_diameter < 0.01 LOOP + FOR r IN + SELECT place_id, null, null, null, + ST_Distance(geometry, line) as distance + FROM location_road_-partition- + WHERE ST_DWithin(line, geometry, search_diameter) + ORDER BY (ST_distance(geometry, p1)+ + ST_distance(geometry, p2)+ + ST_distance(geometry, p3)) ASC limit 1 + LOOP + RETURN NEXT r; + RETURN; + END LOOP; + search_diameter := search_diameter * 2; + END LOOP; + RETURN; + END IF; +-- end + + RAISE EXCEPTION 'Unknown partition %', in_partition; +END +$$ +LANGUAGE plpgsql;