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

Hi, is there a way to extract just certain data from a specific country from that massive OSM file?

I would like to retrieve the German zip codes, the city names including the parts of town and LAT and LON. Export format maybe something like csv or xml. Is that possible in some sort of automatic way? There are free databases that provide nearly everything of my desired data but the parts of town and their specific LAT and LON are always missing.

Thank you guys for your ideas.

asked 19 Jan '22, 13:51

Bingoboy1234's gravatar image

Bingoboy1234
11112
accept rate: 0%


You can do it but it is not trivial. The easiest way is probably importing the data into a PostGIS database with osm2pgsql (you can make it import only the features you are interested in) and then work with SQL. Thing is that the "parts of town" you're after may be mapped as a single node (place=suburb or place=neighbourhood) in which case they have a straigforward lat/lon, or they might be a polygon in which case you need to compute the centroid (which is easy to do in PostGIS).

permanent link

answered 19 Jan '22, 13:58

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

Thank you, Frederik. That sounds complicated. I need to train myself with PostGIS. By now I am unfortunately only used to MySQL. Can I compute the polygon centroid automatically for the whole of all German parts of towns in one go or do I need to do that by hand for each city? Latter could become a job without an end.

(31 Jan '22, 10:22) Bingoboy1234

I am not sure what the status of osm-to-mysql importers is. I suspect it will be easier for you to get to grips with PostgreSQL than to try and import to mysql - the difference in terms of SQL should be minuscle. Of course you can compute the centroid for all parts of towns in PostGIS, something like

SELECT 
  st_centroid(way) AS geom,
  name,
  place
INTO
  ortsteile
FROM 
  planet_osm_polygon
WHERE
  place in ('neighbourhood','suburb');

After that, add those that were already present as points

INSERT INTO 
  ortsteile
(SELECT way as geom, name, place FROM 
planet_osm_point WHERE place in ('neighbourhood','suburb');

You might also want to add the admin boundaries on levels above 8 to the mix. And later to find out which city something is in,

ALTER TABLE ortsteile ADD COLUMN ort VARCHAR(64);
UPDATE ortsteile
SET ort = (SELECT name FROM planet_osm_polygon
  WHERE boundary='administrative' AND admin_level='8' AND
  st_contains(way, geom));

Because in Germany some cities ("kreisfreie Städte") have an admin_level of 6 you will want to re-run the last query for those points that didn't have an admin_level 8 parent:

UPDATE ortsteile
SET ort = (SELECT name FROM planet_osm_polygon
  WHERE boundary='administrative' AND admin_level='6' AND
  st_contains(way, geom)
WHERE ort IS NULL;
permanent link

answered 31 Jan '22, 10:37

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

edited 31 Jan '22, 10:38

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:

×83
×55
×45
×23
×9

question asked: 19 Jan '22, 13:51

question was seen: 1,179 times

last updated: 31 Jan '22, 10:38

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