Apologies if this is a silly question, I'm very new to PostgreSQL/PostGIS.

I have downloaded an .osm file for the entire state of Baden-Württemberg from here - https://download.geofabrik.de/europe/germany/baden-wuerttemberg.html

I have to answer the following questions, by writing queries for the same -

What is the area (expressed in m^2) of Baden-Württemberg? What is by area the smallest city of Baden-Württemberg? I have imported the osm file into a PostgreSQL using the osm2pgsql tool.

There seem to be multiple tables that are being created, core and generated by osm2pgsql. In addition to the core tables - planet_osm_nodes, planet_osm_ways and planet_osm_rels there are other tables as well, and I'm not quite sure how to use them to complete my tasks.

It would be really helpful if anybody could provide some insight into how I can go about writing the PostGIS query for the same.

asked 06 Mar, 10:21

Sujay's gravatar image

Sujay
51238
accept rate: 0%


Is it possible that you want us to do your homework for you? If you have been given these tasks in the context of a course or school assignment, then surely whoever gave you the tasks wants you to learn something from solving them. So here's some hints but not a full solution:

  • administrative areas will be in the planet_osm_polygon table, and state and city boundaries can likely be identified looking at the "boundary" and "admin_level" tags
  • note that not all cities in Germany have an admin_level tag of 8!
  • the area of something can be computed with the ST_AREA PostGIS function, but
  • consider which projection your data has been imported in, and whether you might have to reproject it before invoking ST_AREA (also consider perhaps employing the GEOGRAPHY data type for best results)
permanent link

answered 06 Mar, 10:46

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
71.3k846451113
accept rate: 24%

Hi Frederik, thank you for the hints. I've been learning PostGIS for the past couple of days and am still new to it. I'm running against a deadline for the tasks and so I thought I'd post the question here. I'll write a query using ST_Area() and post it here when I'm done! Thanks again.

(06 Mar, 10:56) Sujay

SELECT ST_Area(ST_Transform((SELECT way FROM planet_osm_polygon WHERE osm_id=-62611 AND way_area=1.68203e+007)), 4326)::geography)FROM planet_osm_polygon;

Can you tell me what seems to be the problem with this query?

(06 Mar, 11:15) Sujay
1

Do you get an error message? A bad result? No result?

(06 Mar, 11:48) Frederik Ramm ♦

The query doesn't end. It doesn't seem right either. The geography object being passed to ST_Area() requires a polygon/multi-polygon to be passed to it. Can you tell me how I can isolate the polygon/multi-polygon for BW from planet_osm_nodes/planet_osm_point?

(07 Mar, 04:16) Sujay

I tried this query -

SELECT osm_id, way, ST_AsText(way), ST_IsValid(way), ST_IsEmpty(way) FROM planet_osm_polygon WHERE osm_id = -62611 AND way_area = '8.20869e+010';

Now I need to feed this Polygon to the ST_Area() function. Can you help me with this?

(07 Mar, 04:49) Sujay
1

Your second query works, but you can drop the "AND way_area..." since there should only be one polygon with osm_id = -62611 anyway. You can then use function calls in your SELECT, i.e. instead of "select way" you can write "select st_area(st_transform(way..." just like you did in your first query. The sub-select in your first query is unnecessary, you can do it with just one SELECT. If for any reason you have more than one polygons with osm_id = -62611 then you can add a SUM() around your ST_AREA to sum them all up.

(07 Mar, 09:29) Frederik Ramm ♦
showing 5 of 6 show 1 more comments
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:

×551
×213
×146
×122

question asked: 06 Mar, 10:21

question was seen: 589 times

last updated: 07 Mar, 09:29

powered by OSQA