Thread: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Alexander Farber
Date:
Hello dear PostgreSQL users
I have prepared a https://dbfiddle.uk/vOFXNgns for my question and also list my SQL code below.
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
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Tom Lane
Date:
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
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Christoph Berg
Date:
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
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Thiemo Kellner
Date:
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?
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Tony Shelver
Date:
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.
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
From
Alexander Farber
Date:
Thank you all. I would like to use a geometry type, but will it work well with the Npgsql C# packages?