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, 14:59

akulin's gravatar image

akulin
2126
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, 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, 15:44) akulin

I guess it shoud be something with DISTINCT(name)

(27 Sep, 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, 09:04

TZorn's gravatar image

TZorn
10.7k552198
accept rate: 14%

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, 09:43) SK53 ♦

Works ok with MIN()

(28 Sep, 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, 17:01

akulin's gravatar image

akulin
2126
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, 09:06) TZorn

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

(28 Sep, 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:

×139
×132
×31
×15

question asked: 27 Sep, 14:59

question was seen: 221 times

last updated: 28 Sep, 10:32

powered by OSQA