|
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. |
|
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:
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 '19, 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 '19, 11:15)
Sujay
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 '19, 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 '19, 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 '19, 09:29)
Frederik Ramm ♦
showing 5 of 6
show 1 more comments
|
crossposts: https://stackoverflow.com/questions/55019529/given-the-osm-data-for-a-state-find-its-area and https://gis.stackexchange.com/questions/314541/given-the-osm-data-for-a-state-find-its-area-by-writing-a-postgis-query