How can I transform 2D OSM Polygon to 3D when I have a height value attribute?

user_1820715 0
Viewed 571

I have an OSM database with only buildings polygons and its height attribute. I've deleted anything without height.

Now I need to convert the "way" column to 3D (or create another column I don't know) to convert all plain 2D polys to 3D geometries, setting the height attribute to the "Z" coordinate value.

I can't find any PostGIS function to do this (or I couldn't interpret the ones that exist).

Is there any way to do this or do I need GDAL?

geojson postgis gdal openstreetmap postgresql
1 Answers

You could ST_DumpPoints your geometry, add the Z dimension to with ST_MakePoint and create the Polygon again, e.g.

WITH j (geom,height) AS (
 SELECT 
  (ST_DumpPoints('POLYGON((30 10,40 40,20 40,10 20,30 10))'::geometry)).geom,42
)
SELECT 
ST_AsText(
 ST_MakePolygon(
  ST_MakeLine(
   ST_MakePoint(ST_X(geom),ST_Y(geom),height))))
FROM j;

                         st_astext                          
------------------------------------------------------------
 POLYGON Z ((30 10 42,40 40 42,20 40 42,10 20 42,30 10 42))
(1 Zeile)

In the end, creating a new column or updating an existing one is up to you.

I admit this is not a very elegant solution. I'd be definitely glad if our fellow PostGIS experts can come up with a better one.

If you already have all 3 dimensions in your polygon and just want to change the geometry to the proper type, take a look at ST_Force3D:

SELECT 
ST_AsText(
 ST_Force3D('POLYGON((30 10 42,40 40 42,20 40 42,10 20 42,30 10 42))'::geometry));

                         st_astext                          
------------------------------------------------------------
 POLYGON Z ((30 10 42,40 40 42,20 40 42,10 20 42,30 10 42))
(1 Zeile)

EDIT: This is the answer in OSM context kindly provided by @Magno C:

First you need to convert the "height" attribute column to "real" because OSM data is only text. But first you need to delete all non-number data. You may delete all null lines too. Be advised you WILL loose data!

alter table planet_osm_polygon add column alt real;
delete from planet_osm_polygon where height !~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'
update planet_osm_polygon set alt = CAST ( REPLACE(height,',','.') AS real);

Now, you need to force the LineStrings to be closed. I follow this answer and create the ST_ForceClosed function.

Now the above mentioned answer can work. I modify it to conform the OSM table structure.

ALTER TABLE planet_osm_polygon ADD COLUMN buildings3d geometry;

and

WITH j (geom,height) AS (
  SELECT (ST_DumpPoints(way::geometry)).geom, alt from planet_osm_polygon
)
update planet_osm_polygon set buildings3d = ( SELECT 
 ST_MakePolygon( ( ST_ForceClosed( ST_MakeLine( ST_MakePoint(ST_X(geom),ST_Y(geom),height) ) ) ) ) 
FROM j );

Further reading: ST_MakeLine and ST_MakePolygon

user_22753880
Related