public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alexander Farber <[email protected]>
To: pgsql-general <[email protected]>
Subject: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
Date: Tue, 25 Mar 2025 11:39:03 +0100
Message-ID: <CAADeyWiTu2P1Y_6dPn2E+1AwSvNkwNHmT6UcFStubz0kFh9gtg@mail.gmail.com> (raw)

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
  In-Reply-To: <CAADeyWiTu2P1Y_6dPn2E+1AwSvNkwNHmT6UcFStubz0kFh9gtg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox