+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+
-- Assorted helper functions for the triggers.
-CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place geometry)
- RETURNS INTEGER
+CREATE OR REPLACE FUNCTION get_center_point(place GEOMETRY)
+ RETURNS GEOMETRY
AS $$
DECLARE
- NEWgeometry geometry;
+ geom_type TEXT;
+BEGIN
+ geom_type := ST_GeometryType(place);
+ IF geom_type = ' ST_Point' THEN
+ RETURN place;
+ END IF;
+ IF geom_type = 'ST_LineString' THEN
+ RETURN ST_LineInterpolatePoint(place, 0.5);
+ END IF;
+
+ RETURN ST_PointOnSurface(place);
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+
+CREATE OR REPLACE FUNCTION geometry_sector(partition INTEGER, place GEOMETRY)
+ RETURNS INTEGER
+ AS $$
BEGIN
--- RAISE WARNING '%',place;
- NEWgeometry := ST_PointOnSurface(place);
- RETURN (partition*1000000) + (500-ST_X(NEWgeometry)::integer)*1000 + (500-ST_Y(NEWgeometry)::integer);
+ RETURN (partition*1000000) + (500-ST_X(place)::INTEGER)*1000 + (500-ST_Y(place)::INTEGER);
END;
$$
LANGUAGE plpgsql IMMUTABLE;
+
CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
RETURNS INTEGER[]
AS $$
$$
LANGUAGE plpgsql IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION get_rel_node_members(members JSONB, memberLabels TEXT[])
+ RETURNS SETOF BIGINT
+ AS $$
+DECLARE
+ member JSONB;
+BEGIN
+ FOR member IN SELECT * FROM jsonb_array_elements(members)
+ LOOP
+ IF member->>'type' = 'N' and member->>'role' = ANY(memberLabels) THEN
+ RETURN NEXT (member->>'ref')::bigint;
+ END IF;
+ END LOOP;
+
+ RETURN;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+
-- Copy 'name' to or from the default language.
--
-- \param country_code Country code of the object being named.
RETURNS TEXT
AS $$
DECLARE
- place_centre GEOMETRY;
nearcountry RECORD;
+ countries TEXT[];
BEGIN
- place_centre := ST_PointOnSurface(place);
-
--- RAISE WARNING 'get_country_code, start: %', ST_AsText(place_centre);
+-- RAISE WARNING 'get_country_code, start: %', ST_AsText(place);
-- Try for a OSM polygon
- FOR nearcountry IN
- SELECT country_code from location_area_country
- WHERE country_code is not null and st_covers(geometry, place_centre) limit 1
- LOOP
- RETURN nearcountry.country_code;
- END LOOP;
+ SELECT array_agg(country_code) FROM location_area_country
+ WHERE country_code is not null and st_covers(geometry, place)
+ INTO countries;
+
+ IF array_length(countries, 1) = 1 THEN
+ RETURN countries[1];
+ END IF;
--- RAISE WARNING 'osm fallback: %', ST_AsText(place_centre);
+ IF array_length(countries, 1) > 1 THEN
+ -- more than one country found, confirm against the fallback data what to choose
+ FOR nearcountry IN
+ SELECT country_code FROM country_osm_grid
+ WHERE ST_Covers(geometry, place) AND country_code = ANY(countries)
+ ORDER BY area ASC
+ LOOP
+ RETURN nearcountry.country_code;
+ END LOOP;
+ -- Still nothing? Choose the country code with the smallest partition number.
+ -- And failing that, just go by the alphabet.
+ FOR nearcountry IN
+ SELECT cc,
+ (SELECT partition FROM country_name WHERE country_code = cc) as partition
+ FROM unnest(countries) cc
+ ORDER BY partition, cc
+ LOOP
+ RETURN nearcountry.cc;
+ END LOOP;
+
+ -- Should never be reached.
+ RETURN countries[1];
+ END IF;
+
+-- RAISE WARNING 'osm fallback: %', ST_AsText(place);
-- Try for OSM fallback data
-- The order is to deal with places like HongKong that are 'states' within another polygon
FOR nearcountry IN
SELECT country_code from country_osm_grid
- WHERE st_covers(geometry, place_centre) order by area asc limit 1
+ WHERE st_covers(geometry, place) order by area asc limit 1
LOOP
RETURN nearcountry.country_code;
END LOOP;
--- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre);
-
- --
- FOR nearcountry IN
- SELECT country_code from country_osm_grid
- WHERE st_dwithin(geometry, place_centre, 0.5)
- ORDER BY st_distance(geometry, place_centre) asc, area asc limit 1
- LOOP
- RETURN nearcountry.country_code;
- END LOOP;
+-- RAISE WARNING 'near osm fallback: %', ST_AsText(place);
RETURN NULL;
END;
-- Find the parent of an address with addr:street/addr:place tag.
--
--- \param street Value of addr:street or NULL if tag is missing.
--- \param place Value of addr:place or NULL if tag is missing.
+-- \param token_info Naming info with the address information.
-- \param partition Partition where to search the parent.
-- \param centroid Location of the address.
--
-- \return Place ID of the parent if one was found, NULL otherwise.
-CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT,
+CREATE OR REPLACE FUNCTION find_parent_for_address(token_info JSONB,
partition SMALLINT,
centroid GEOMETRY)
RETURNS BIGINT
AS $$
DECLARE
parent_place_id BIGINT;
- word_ids INTEGER[];
BEGIN
- IF street is not null THEN
- -- Check for addr:street attributes
- -- Note that addr:street links can only be indexed, once the street itself is indexed
- word_ids := word_ids_from_name(street);
- IF word_ids is not null THEN
- parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids);
- IF parent_place_id is not null THEN
- --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent_place_id;
- RETURN parent_place_id;
- END IF;
- END IF;
+ -- Check for addr:street attributes
+ parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, token_info);
+ IF parent_place_id is not null THEN
+ {% if debug %}RAISE WARNING 'Get parent from addr:street: %', parent_place_id;{% endif %}
+ RETURN parent_place_id;
END IF;
-- Check for addr:place attributes.
- IF place is not null THEN
- word_ids := word_ids_from_name(place);
- IF word_ids is not null THEN
- parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids);
- IF parent_place_id is not null THEN
- --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent_place_id;
- RETURN parent_place_id;
- END IF;
- END IF;
- END IF;
-
- RETURN NULL;
+ parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, token_info);
+ {% if debug %}RAISE WARNING 'Get parent from addr:place: %', parent_place_id;{% endif %}
+ RETURN parent_place_id;
END;
$$
LANGUAGE plpgsql STABLE;
+
CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT)
RETURNS BOOLEAN
AS $$
END IF;
RETURN ST_Envelope(ST_Collect(
- ST_Project(geom, radius, 0.785398)::geometry,
- ST_Project(geom, radius, 3.9269908)::geometry));
+ ST_Project(geom::geography, radius, 0.785398)::geometry,
+ ST_Project(geom::geography, radius, 3.9269908)::geometry));
END;
$$
LANGUAGE plpgsql IMMUTABLE;
$$
LANGUAGE plpgsql IMMUTABLE;
+CREATE OR REPLACE FUNCTION simplify_large_polygons(geometry GEOMETRY)
+ RETURNS GEOMETRY
+ AS $$
+BEGIN
+ IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+ and ST_MemSize(geometry) > 3000000
+ THEN
+ geometry := ST_SimplifyPreserveTopology(geometry, 0.0001);
+ END IF;
+ RETURN geometry;
+END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
RETURNS BOOLEAN
SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype;
DELETE FROM import_polygon_delete where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
DELETE FROM import_polygon_error where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
- -- force delete from place/placex by making it a very small geometry
- UPDATE place set geometry = ST_SetSRID(ST_Point(0,0), 4326) where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
- DELETE FROM place where osm_type = osmtype and osm_id = osmid and class = pclass and type = ptype;
+ -- force delete by directly entering it into the to-be-deleted table
+ INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred)
+ VALUES(osmtype, osmid, pclass, ptype, false);
+ PERFORM flush_deleted_places();
RETURN TRUE;
END;
END;
$$
LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION flush_deleted_places()
+ RETURNS INTEGER
+ AS $$
+BEGIN
+ -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
+ INSERT INTO import_polygon_delete (osm_type, osm_id, class, type)
+ SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred;
+
+ -- delete from place table
+ ALTER TABLE place DISABLE TRIGGER place_before_delete;
+ DELETE FROM place USING place_to_be_deleted
+ WHERE place.osm_type = place_to_be_deleted.osm_type
+ and place.osm_id = place_to_be_deleted.osm_id
+ and place.class = place_to_be_deleted.class
+ and place.type = place_to_be_deleted.type
+ and not deferred;
+ ALTER TABLE place ENABLE TRIGGER place_before_delete;
+
+ -- Mark for delete in the placex table
+ UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
+ WHERE placex.osm_type = 'N' and place_to_be_deleted.osm_type = 'N'
+ and placex.osm_id = place_to_be_deleted.osm_id
+ and placex.class = place_to_be_deleted.class
+ and placex.type = place_to_be_deleted.type
+ and not deferred;
+ UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
+ WHERE placex.osm_type = 'W' and place_to_be_deleted.osm_type = 'W'
+ and placex.osm_id = place_to_be_deleted.osm_id
+ and placex.class = place_to_be_deleted.class
+ and placex.type = place_to_be_deleted.type
+ and not deferred;
+ UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted
+ WHERE placex.osm_type = 'R' and place_to_be_deleted.osm_type = 'R'
+ and placex.osm_id = place_to_be_deleted.osm_id
+ and placex.class = place_to_be_deleted.class
+ and placex.type = place_to_be_deleted.type
+ and not deferred;
+
+ -- Mark for delete in interpolations
+ UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted
+ WHERE place_to_be_deleted.osm_type = 'W'
+ and place_to_be_deleted.class = 'place'
+ and place_to_be_deleted.type = 'houses'
+ and location_property_osmline.osm_id = place_to_be_deleted.osm_id
+ and not deferred;
+
+ -- Clear todo list.
+ TRUNCATE TABLE place_to_be_deleted;
+
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
projects / nominatim.git / blobdiff