Horton
Menu

Thread

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

    Alexander Farber <alexander.farber@gmail.com> — 2025-03-25T10:39:03Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-25T17:07:29Z

    Alexander Farber <alexander.farber@gmail.com> writes:
> 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 $$

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

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

    Christoph Berg <myon@debian.org> — 2025-03-25T17:58:40Z

    Re: Tom Lane
> You might be best advised to create a composite
> type like "location (long bigint, lat bigint)" and use an array of
> that.

Or use the geometry types from PostGIS, since you are probably going
to do geo lookups on these points anyway.

Christoph

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

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-03-25T20:21:34Z

    El 25-03-25 a las 18:58, Christoph Berg escribió:
> Re: Tom Lane
>> You might be best advised to create a composite
>> type like "location (long bigint, lat bigint)" and use an array of
>> that.
> Or use the geometry types from PostGIS, since you are probably going
> to do geo lookups on these points anyway.
>
> Christoph
Why is pure SQL not an option instead of a procedure/function?

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

    Tony Shelver <tshelver@gmail.com> — 2025-03-26T05:33:26Z

    On Tue, 25 Mar 2025 at 19:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alexander Farber <alexander.farber@gmail.com> writes:
> > 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 $$
>
> 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
>
>
>
Another approach I use is to string everything into a JSON object, and
de-serialize it in the function.

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

    Alexander Farber <alexander.farber@gmail.com> — 2025-03-26T10:31:39Z

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

>