Subject: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

From: Alexander Farber <[email-masked]>
Date: Tue, 25 Mar 2025 11:39:03 +0100

Hello dear PostgreSQL users

I have prepared a https://dbfiddle.uk/vOFXNgns  for my question and also
list my SQL code below.

I have created a countires_boundaries table, which I intend to fill with
.poly files provided at Geofabrik:

    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);

Then I am trying to add a function, which would receive a series of
locations (longitude and latitude pairs in microdegrees) and return a list
of lowercase 2-letter country codes, like "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
        -- 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;

Unfortunately, this gives me the error:

    table "location_array" has 1 columns available but 2 columns specified

I have also tried:

    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
        -- Convert microdegrees to degrees and check if the location lies
within the country boundary.
        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;

But that gives me the error:

    cannot subscript type bigint because it does not support subscripting

I had even more attempts at fixing my issue, but have not succeeded yet

In the long run I am trying to call the function from an ASP.Net Core 8 app
as:

    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);

            // convert locations into the expected format (array of BIGINT
pairs)
            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;
    }

Best regards
Alex

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Mar 2025 13:07:29 -0400

>     RETURNS TABLE (country TEXT) AS $$

Postgres isn't too friendly to representing a list of locations as
a 2-D array, because we generally don't treat arrays as being
arrays-of-arrays, so unnest produces a set of bigints not a set
of smaller arrays.  You might be best advised to create a composite
type like "location (long bigint, lat bigint)" and use an array of
that.  If you're really hot to use a 2-D array, the only construct
I can think of that's on board with unnesting that the way you need
is plpgsql's FOREACH SLICE syntax:

https://www.postgresql.org/docs/devel/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY

You could probably make a custom version of unnest that uses that
and then keep your query about the same.

			regards, tom lane

---

From: Tony Shelver <[email-masked]>
Date: Wed, 26 Mar 2025 07:33:26 +0200

> and then keep your query about the same.
Another approach I use is to string everything into a JSON object, and
de-serialize it in the function.

---

From: Alexander Farber <[email-masked]>
Date: Wed, 26 Mar 2025 11:31:39 +0100

Thank you all. I would like to use a geometry type, but will it work well
with the Npgsql C# packages?