I have written a PostGIS query on a database with OSM data of the state of Baden-Württemberg.

SELECT * FROM public.planet_osm_polygon WHERE boundary = 'administrative' AND admin_level = '8';

So it turns out that not all cities/towns have an admin_level tag of 8. Is there any way we can further constrain the query to filter out only the towns/cities?

asked 08 Mar, 05:19

Sujay's gravatar image

Sujay
517
accept rate: 0%

edited 08 Mar, 05:20


Posting your question in multiple places at once is impolite. It can lead to several people answering your question, without knowing that it has been answered already elsewhere. It means that you just think of yourself (maximizing your chance of getting an answer) and you don't care if you waste the time of others. Think about that before you do it next time!

The reason that not all cities in Germany have an admin_level of 8 is that there are some "kreisfreie Städte" which use admin_level 6 (and, outside of Baden-Württemberg, even two "Stadtstaaten" which use admin_level 4).

Finding the cities that do not have an admin_level 8 tag but instead an admin_level 6 tag could be done like this:

SELECT a.*
FROM planet_osm_polygon a
LEFT OUTER JOIN planet_osm_polygon b
ON ST_COVERS(a.way,b.way) AND b.boundary='administrative' AND b.admin_level='8'
WHERE a.boundary='administrative' AND a.admin_level='6' AND b.osm_id IS NULL

The "left outer join" instructs PostgreSQL to find pairs of adminlevel 6/8 entities where the adminlevel 6 entity covers the adminlevel 8 entity, and to set the adminlevel 8 entity to NULL if none is found matching this criterion. Then, we only select those adminlevel 6 entities where the adminlevel 8 part is NULL, i.e. those that have no "children" on admin_level 8.

permanent link

answered 09 Mar, 23:48

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
67.5k806161048
accept rate: 24%

edited 09 Mar, 23:49

Thank you so much for your answer Frederik! I'm sorry, I will make sure not to post it in multiple places from next time. Thanks again.

(10 Mar, 07:46) Sujay
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:

×503
×140
×117

question asked: 08 Mar, 05:19

question was seen: 125 times

last updated: 10 Mar, 07:47

powered by OSQA