public inbox for [email protected]
help / color / mirror / Atom feedHow to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
4+ messages / 3 participants
[nested] [flat]
* How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
@ 2025-03-25 10:39 Alexander Farber <[email protected]>
2025-03-25 17:07 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander Farber @ 2025-03-25 10:39 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
2025-03-25 10:39 How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Alexander Farber <[email protected]>
@ 2025-03-25 17:07 ` Tom Lane <[email protected]>
2025-03-26 05:33 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Tony Shelver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-03-25 17:07 UTC (permalink / raw)
To: Alexander Farber <[email protected]>; +Cc: pgsql-general
Alexander Farber <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
2025-03-25 10:39 How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Alexander Farber <[email protected]>
2025-03-25 17:07 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Tom Lane <[email protected]>
@ 2025-03-26 05:33 ` Tony Shelver <[email protected]>
2025-03-26 10:31 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Alexander Farber <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tony Shelver @ 2025-03-26 05:33 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-general
On Tue, 25 Mar 2025 at 19:07, Tom Lane <[email protected]> wrote:
> Alexander Farber <[email protected]> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function?
2025-03-25 10:39 How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Alexander Farber <[email protected]>
2025-03-25 17:07 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Tom Lane <[email protected]>
2025-03-26 05:33 ` Re: How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Tony Shelver <[email protected]>
@ 2025-03-26 10:31 ` Alexander Farber <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Alexander Farber @ 2025-03-26 10:31 UTC (permalink / raw)
To: ; +Cc: pgsql-general
Thank you all. I would like to use a geometry type, but will it work well
with the Npgsql C# packages?
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-03-26 10:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-25 10:39 How to pass a list of locations (longitude, latitude) to a PostgreSQL/PostGIS stored function? Alexander Farber <[email protected]>
2025-03-25 17:07 ` Tom Lane <[email protected]>
2025-03-26 05:33 ` Tony Shelver <[email protected]>
2025-03-26 10:31 ` Alexander Farber <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox