如何向PostgreSQL/PostGIS存储函数传入经纬度坐标列表？

我创建了countries_boundaries表，用来存储国家边界数据：

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS countries_boundaries (
    country TEXT PRIMARY KEY CHECK (country ~ '^[a-z]{2}$'),
    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL
);

CREATE INDEX IF NOT EXISTS countries_boundaries_index_1
ON countries_boundaries
USING GIST (boundary);

我需要创建一个函数，接收一组以微度为单位的经纬度坐标对，返回对应的小写双字母国家代码（如"de"、"pl"、"lv"）。

第一次尝试的函数代码 #

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
    -- 将微度转换为度，并检查坐标是否位于国家边界内。
    WHERE ST_Contains(
              boundary, 
              ST_SetSRID(
                  ST_MakePoint(lng / 1000000.0, lat / 1000000.0), 
                  4326
              )
          )
) AS enclosing_countries ON TRUE;
$$ LANGUAGE sql STABLE;

执行后报错：

表"location_array"仅提供1列，但指定了2列

第二次尝试的函数代码 #

CREATE OR REPLACE FUNCTION find_countries(locations BIGINT[][])
RETURNS TABLE (country TEXT) AS $$
SELECT DISTINCT enclosing_countries.country
FROM unnest(locations) AS location
JOIN LATERAL (
    SELECT country
    FROM countries_boundaries
    -- 将微度转换为度，并检查坐标是否位于国家边界内。
    WHERE ST_Contains(
              boundary, 
              ST_SetSRID(
                  ST_MakePoint(location[1] / 1000000.0, location[2] / 1000000.0), 
                  4326
              )
          )
) AS enclosing_countries ON TRUE;
$$ LANGUAGE sql STABLE;

执行后仍报错：

无法对bigint类型使用下标，因为它不支持下标操作

我多次尝试修复都没成功。

预期的ASP.Net Core 8调用代码 #

最终我打算从ASP.Net Core 8应用中调用这个函数，代码如下：

public async Task<ISet<string>> FindCountries(IEnumerable<(long lng, long lat)> locations)
{
    HashSet<string> countries = [];

    await retryPolicy.ExecuteAsync(async () =>
    {
        await using NpgsqlConnection connection = new(connectionString);
        await connection.OpenAsync();
        using NpgsqlCommand command = new("SELECT country FROM find_countries(@locations)", connection);

        // 将坐标转换为预期格式（BIGINT对数组）
        List<(long lng, long lat)> locationList = [.. locations];
        long[][] locationArray = [.. locationList.Select(loc => new long[] { loc.lng, loc.lat })];
        command.Parameters.AddWithValue("locations", locationArray);

        await using NpgsqlDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            string countryCode = reader.GetString(0);
            if (!string.IsNullOrWhiteSpace(countryCode))
            {
                countries.Add(countryCode);
            }
        }
    });

    return countries;
}

内容的提问来源于stack exchange，提问作者Alexander Farber