MIME-Version: 1.0
From: Alexander Farber <alexander.farber@gmail.com>
Date: Tue, 25 Mar 2025 11:39:03 +0100
Message-ID: <CAADeyWiTu2P1Y_6dPn2E+1AwSvNkwNHmT6UcFStubz0kFh9gtg@mail.gmail.com>
Subject: How to pass a list of locations (longitude, latitude) to a
 PostgreSQL/PostGIS stored function?
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fcd2040631285481"
Archived-At: <https://www.postgresql.org/message-id/CAADeyWiTu2P1Y_6dPn2E%2B1AwSvNkwNHmT6UcFStubz0kFh9gtg%40mail.gmail.com>
Precedence: bulk

--000000000000fcd2040631285481
Content-Type: text/plain; charset="UTF-8"

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

--000000000000fcd2040631285481
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>He=
llo dear PostgreSQL users<br><br>I have prepared a <a href=3D"https://dbfid=
dle.uk/vOFXNgns">https://dbfiddle.uk/vOFXNgns</a>=C2=A0 for my question and=
 also list my SQL code below.</div><div><br></div><div>I have created a cou=
ntires_boundaries table, which I intend to fill with .poly files provided a=
t Geofabrik:</div><div><br></div><div>=C2=A0 =C2=A0 CREATE EXTENSION IF NOT=
 EXISTS postgis;</div><div><br></div><div>=C2=A0 =C2=A0 CREATE TABLE IF NOT=
 EXISTS countries_boundaries (</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 countr=
y TEXT PRIMARY KEY CHECK (country ~ &#39;^[a-z]{2}$&#39;),</div><div>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL</div><=
div>=C2=A0 =C2=A0 );</div><div><br></div><div>=C2=A0 =C2=A0 CREATE INDEX IF=
 NOT EXISTS countries_boundaries_index_1</div><div>=C2=A0 =C2=A0 ON countri=
es_boundaries</div><div>=C2=A0 =C2=A0 USING GIST (boundary);</div><div><br>=
</div><div>Then I am trying to add a function, which would receive a series=
 of locations (longitude and latitude pairs in microdegrees) and return a l=
ist of lowercase 2-letter country codes, like &quot;de&quot;, &quot;pl&quot=
;, &quot;lv&quot;:</div><div><br></div><div>=C2=A0 =C2=A0 CREATE OR REPLACE=
 FUNCTION find_countries(locations BIGINT[][])</div><div>=C2=A0 =C2=A0 RETU=
RNS TABLE (country TEXT) AS $$</div><div>=C2=A0 =C2=A0 SELECT DISTINCT encl=
osing_countries.country</div><div>=C2=A0 =C2=A0 FROM unnest(locations) AS l=
ocation_array(lng, lat)</div><div>=C2=A0 =C2=A0 JOIN LATERAL (</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT country</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 FROM countries_boundaries</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- C=
onvert microdegrees to degrees and check if the location lies within the co=
untry boundary.</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE ST_Contains(</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 boun=
dary,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 ST_SetSRID(</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ST_MakePoint(lng / 1000000.0, lat / 1000=
000.0),=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 4326</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 )</div><div>=C2=A0 =C2=A0 ) AS enclosing_countries ON TRU=
E;</div><div>=C2=A0 =C2=A0 $$ LANGUAGE sql STABLE;</div><div><br></div><div=
>Unfortunately, this gives me the error:</div><div><br></div><div>=C2=A0 =
=C2=A0 table &quot;location_array&quot; has 1 columns available but 2 colum=
ns specified</div><div><br></div><div>I have also tried:</div><div><br></di=
v><div>=C2=A0 =C2=A0 CREATE OR REPLACE FUNCTION find_countries(locations BI=
GINT[][])</div><div>=C2=A0 =C2=A0 RETURNS TABLE (country TEXT) AS $$</div><=
div>=C2=A0 =C2=A0 SELECT DISTINCT enclosing_countries.country</div><div>=C2=
=A0 =C2=A0 FROM unnest(locations) AS location</div><div>=C2=A0 =C2=A0 JOIN =
LATERAL (</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT country</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM countries_boundaries</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -- Convert microdegrees to degrees and check if the locat=
ion lies within the country boundary.</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 WHERE ST_Contains(</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 boundary,=C2=A0</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ST_SetSRID(</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ST_MakePoint(locati=
on[1] / 1000000.0, location[2] / 1000000.0),=C2=A0</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 4326</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )</div><d=
iv>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )</div><div>=C2=A0 =C2=
=A0 ) AS enclosing_countries ON TRUE;</div><div>=C2=A0 =C2=A0 $$ LANGUAGE s=
ql STABLE;</div><div><br></div><div>But that gives me the error:</div><div>=
<br></div><div>=C2=A0 =C2=A0 cannot subscript type bigint because it does n=
ot support subscripting</div><div><br>I had even more attempts at fixing my=
 issue, but have not succeeded yet<br><br>In the long run I am trying to ca=
ll the function from an ASP.Net Core 8 app as:<br><br><div>=C2=A0 =C2=A0 pu=
blic async Task&lt;ISet&lt;string&gt;&gt; FindCountries(IEnumerable&lt;(lon=
g lng, long lat)&gt; locations)</div><div>=C2=A0 =C2=A0 {</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 HashSet&lt;string&gt; countries =3D [];</div><div><br>=
</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 await retryPolicy.ExecuteAsync(async=
 () =3D&gt;</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 {</div><div>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 await using NpgsqlConnection connection =3D ne=
w(connectionString);</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 aw=
ait connection.OpenAsync();</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 using NpgsqlCommand command =3D new(&quot;SELECT country FROM find_c=
ountries(@locations)&quot;, connection);</div><div><br></div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 // convert locations into the expected f=
ormat (array of BIGINT pairs)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 List&lt;(long lng, long lat)&gt; locationList =3D [.. locations];</d=
iv><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 long[][] locationArray =
=3D [.. locationList.Select(loc =3D&gt; new long[] { loc.lng, loc.lat })];<=
/div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 command.Parameters.AddW=
ithValue(&quot;locations&quot;, locationArray);</div><div><br></div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 await using NpgsqlDataReader read=
er =3D await command.ExecuteReaderAsync();</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 while (await reader.ReadAsync())</div><div>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 {</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 string countryCode =3D reader.GetString(0);</div><=
div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (!string.IsN=
ullOrWhiteSpace(countryCode))</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 {</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 countries.Add(countryCode);</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 }</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 }</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 });</di=
v><div><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 return countries;</div><div>=C2=A0 =
=C2=A0 }</div></div><div><br></div><div>Best regards</div><div>Alex</div></=
div></div></div></div>

--000000000000fcd2040631285481--