How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
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
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: 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
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?
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 alist
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.