NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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

asked 27 Sep '21, 14:59

akulin's gravatar image

akulin
21336
accept rate: 0%

Do you mean that where several different villages have the same name, you only want to extract one of them? But then which lat/lon do you want to extract?

(27 Sep '21, 15:38) alan_gr

I need the first available coordinates, just ignore the others. Like for Adamov will be used only first point from my "Example"

(27 Sep '21, 15:44) akulin

I guess it shoud be something with DISTINCT(name)

(27 Sep '21, 16:55) akulin

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's gravatar image

TZorn
12.3k764225
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
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's gravatar image

akulin
21336
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×144
×134
×34
×15

question asked: 27 Sep '21, 14:59

question was seen: 1,028 times

last updated: 28 Sep '21, 10:32

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum