How to pass a list of locations (longitude, latitude) to a PostgreSQLPostGIS stored function? - Stack Overflow

I have prepared a DB fiddle for my questions and also list my SQL code below.

I have created a countries_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;
}

I have prepared a DB fiddle for my questions and also list my SQL code below.

I have created a countries_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;
}

• postgresql
• stored-procedures
• postgis
• npgsql
• stored-functions

Share Improve this question Follow edited Mar 25 at 10:40 Alexander Farber asked Mar 25 at 10:34 Alexander FarberAlexander Farber 23.1k7878 gold badges258258 silver badges442442 bronze badges 3

• 1 Instead of unnest(locations) AS location_array(lng, lat) try this: (select l.lng, l.lat from unnest(locations) as l) as location_array – Stefanov.sm Commented Mar 25 at 12:06
• Thank you, but dbfiddle.uk/c74uMtd_ gives me column l.lng does not exist – Alexander Farber Commented Mar 25 at 12:14
• 1 Sorry, I omitted that locations is bigint[][]. Maybe create type loc as (lng bigint, lat bigint) and use function argument as locations loc[] – Stefanov.sm Commented Mar 25 at 12:41

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You're trying to access slices - there's colon : syntax for that:

SELECT DISTINCT enclosing_countries.country
FROM unnest( locations[:][1:1]
            ,locations[:][2:2] ) AS location_array(lng, lat)

That works in tandem with variadic version of unnest() - note that I gave it two arguments. This effectively unpacks the values from the first column of your 2d array in pairs with those from its second column.

Here's your example, fixed using the above. Below is a smaller one for demonstration:

with args(locations)as(values(array[ [11,12]
                                    ,[21,22]
                                    ,[31,32]]) )
select lng, lat
from args cross join lateral 
   unnest( locations[:][1:1]
          ,locations[:][2:2])as location_array(lng, lat);

lng	lat
11	12
21	22
31	32

Main issue is that UNNEST(array) expand all array elements, not first level.
So rowset have 1 column.

select * from 
unnest(array[array[1,2],array[3,4],array[10,11]])

output is rows (1),(2),(3),(4),(10),(11). fiddle

I suggest expand array by UNNEST and group by back to (lng,lat) pairs.

(
    select (ordinality-1)/2 idx
      ,min(case when ((ordinality-1)%2)=0 then val end)lng
      ,min(case when ((ordinality-1)%2)=1 then val end)lat
    from unnest(locations)with ordinality  t(val) 
    group by (ordinality-1)/2
  ) as location_array(idx,lng,lat) 

See example

-- Enable PostGIS extension if not enabled
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create table for countries if not exists
CREATE TABLE IF NOT EXISTS countries_boundaries (
    country TEXT PRIMARY KEY CHECK (country ~ '^[a-z]{2}$'),
    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL
);
insert into countries_boundaries values
 ('ca',st_geomFromText('Polygon((1 1, 1 3, 3 3,3 1,1 1))'))
,('cb',st_geomFromText('Polygon((10 11,10 15,15 15,10 11))'))
;
CREATE INDEX IF NOT EXISTS countries_boundaries_index_1
ON countries_boundaries
USING GIST (boundary);

CREATE OR REPLACE FUNCTION find_countries(locations BIGINT[][])
RETURNS TABLE (country  TEXT) AS $$
SELECT DISTINCT enclosing_countries.country
FROM (
    select (ordinality-1)/2 idx
      ,min(case when ((ordinality-1)%2)=0 then val end)lng
      ,min(case when ((ordinality-1)%2)=1 then val end)lat
    from unnest(locations)with ordinality  t(val) 
    group by (ordinality-1)/2
  ) location_array(idx,lng,lat) 
JOIN LATERAL (
    SELECT country,boundary
    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;

select * from  find_countries(array[array[2000000,2000000],array[12000000,13000000]])

country
ca
cb

fiddle