How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

Started by Alexander Farberabout 1 year ago6 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexander Farber (#1)
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

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

#3Christoph Berg
myon@debian.org
In reply to: Tom Lane (#2)
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

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

#4Thiemo Kellner
thiemo@gelassene-pferde.biz
In reply to: Christoph Berg (#3)
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

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?

#5Tony Shelver
tshelver@gmail.com
In reply to: Tom Lane (#2)
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored 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 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.

#6Alexander Farber
alexander.farber@gmail.com
In reply to: Tony Shelver (#5)
Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?

Thank you all. I would like to use a geometry type, but will it work well
with the Npgsql C# packages?

Show quoted text