By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- Enable PostGIS extension if not enabled
CREATE EXTENSION IF NOT EXISTS postgis;
-- Create table for countries if not exists
CREATE TABLE IF NOT EXISTS countries_boundaries (
country TEXT PRIMARY KEY CHECK (country ~ '^[a-z]{2}$'),
boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL
);
-- Add spatial index if not exists
CREATE INDEX IF NOT EXISTS countries_boundaries_index_1
ON countries_boundaries
USING GIST (boundary);
CREATE OR REPLACE FUNCTION find_countries(locations BIGINT[][])
RETURNS TABLE (country TEXT) AS $$
SELECT DISTINCT enclosing_countries.country
FROM unnest(locations) AS location_array(lng, lat)
JOIN LATERAL (
SELECT country
FROM countries_boundaries
-- Convert microdegrees to degrees and check if the location lies within the country boundary.
WHERE ST_Contains(
boundary,
ST_SetSRID(
ST_MakePoint(lng / 1000000.0, lat / 1000000.0),
4326
)
)
) AS enclosing_countries ON TRUE;
$$ LANGUAGE sql STABLE;
CREATE EXTENSION
CREATE TABLE
CREATE INDEX
ERROR: table "location_array" has 1 columns available but 2 columns specified CONTEXT: SQL function "find_countries"