Get village names with lat lon coords without duplicate village names

Question

I used this query to extract all village names with lat lon coords:

SELECT name, st_astext(st_transform(way, 4326)) FROM planet_osm_point WHERE place='village' ORDER BY name;

Example:

Abertamy | POINT(12.8182600982157 50.3687442948073)
Adamov   | POINT(15.4089128978551 49.8578161948231)
Adamov   | POINT(14.5395935979761 49.0005116948522)
Adamov   | POINT(15.9781534977758 50.5416186948022)
Adolfov  | POINT(13.9049393980644 50.7353921947967)

As you can see name Adamov listed 3 times. How to get all village names with lat lon coords without duplicate village names? Even if lat lon of the same village name are significantly different

Accepted Answer

3

I'm not familiar with PostgreSQL and PostGIS specifics but in general SQL you could do that with an aggregate function. Something like this:

SELECT name, FIRST(st_astext(st_transform(way, 4326))) AS unique_coord
FROM planet_osm_point 
WHERE place='village'
GROUP BY name 
ORDER BY name;

FIRST() is not available in all dialects. MIN() might do the trick if you only look at one column.

permanent link

answered 28 Sep '21, 09:04

TZorn
12.3k●7●64●225
accept rate: 15%

2

Agreed this is a standard SQL problem, solvable using Window functions. The Postgres reference is here https://www.postgresql.org/docs/9.1/functions-window.html

(28 Sep '21, 09:43) SK53 ♦

Works ok with MIN()

(28 Sep '21, 10:32) akulin

Answer 2

0

SELECT DISTINCT ON (name) name, st_astext(st_transform(way, 4326)) FROM planet_osm_point WHERE place='village' ORDER BY name;

permanent link

answered 27 Sep '21, 17:01

akulin
21●3●3●6
accept rate: 0%

I doubt that works since the coordinates are not DISTINCT and as such the query would still return all lines. But maybe DISTINCT is handled differently in PostgreSQL/PostGIS than in other SQL dialects.

(28 Sep '21, 09:06) TZorn

@TZorn I believe it is DISTINCT ON (name) that makes this different from a standard SQL DISTINCT query.

(28 Sep '21, 09:34) alan_gr

Get village names with lat lon coords without duplicate village names

Follow this question

Related questions