Sunday 26 March 2017

Union/Merge 3D polygons (POLYHEDRALSURFACE Z) each with different heights to output a single polygon in PostGIS


While attempting to ST_Union, ST_Collect, ST_3DUnion an array of POLYHEDRALSURFACE Z geometries with different heights, the output is always a multipolygon with interior walls.


I need to fully merge the geometries so that the result is a single polygon (polyhedral).


In one of the trial cases bellow, I am turning the geometry into individual triangles and regroup them to attempt to get the a polygon out of the result. This is done because a simple ST_Collect results in a geometry collection, so you need to break it out then rebuild it.



SELECT ST_AsGeoJSON(ST_AsText(ST_Collect(geom))) AS geom
FROM (

SELECT ST_MakePolygon(ST_ExteriorRing(
(ST_Dump(
ST_Tesselate(

ST_Collect(
ARRAY[
ST_SETSRID(ST_GEOMFROMTEXT('POLYHEDRALSURFACE Z (((-137 53.3675 25500,-135 52.71667 25500,-133.75 51 25500,-132.81833 50.60833 25500,-133.99022 50.59698 25500,-137 53.3675 25500)),((-137 53.3675 99999,-133.99022 50.59698 99999,-132.81833 50.60833 99999,-133.75 51 99999,-135 52.71667 99999,-137 53.3675 99999)),((-137 53.3675 25500,-137 53.3675 99999,-135 52.71667 99999,-135 52.71667 25500,-137 53.3675 25500)),((-135 52.71667 25500,-135 52.71667 99999,-133.75 51 99999,-133.75 51 25500,-135 52.71667 25500)),((-133.75 51 25500,-133.75 51 99999,-132.81833 50.60833 99999,-132.81833 50.60833 25500,-133.75 51 25500)),((-132.81833 50.60833 25500,-132.81833 50.60833 99999,-133.99022 50.59698 99999,-133.99022 50.59698 25500,-132.81833 50.60833 25500)),((-133.99022 50.59698 25500,-133.99022 50.59698 99999,-137 53.3675 99999,-137 53.3675 25500,-133.99022 50.59698 25500)))'), 4326),
ST_SETSRID(ST_GEOMFROMTEXT('POLYHEDRALSURFACE Z (((-135 52.71667 23000,-135.61667 53.5 23000,-133.5 53.25 23000,-132.91667 52.7 23000,-131.05528 51.54167 23000,-132.33833 50.63028 23000,-132.81833 50.60833 23000,-133.75 51 23000,-135 52.71667 23000)),((-135 52.71667 99999,-133.75 51 99999,-132.81833 50.60833 99999,-132.33833 50.63028 99999,-131.05528 51.54167 99999,-132.91667 52.7 99999,-133.5 53.25 99999,-135.61667 53.5 99999,-135 52.71667 99999)),((-135 52.71667 23000,-135 52.71667 99999,-135.61667 53.5 99999,-135.61667 53.5 23000,-135 52.71667 23000)),((-135.61667 53.5 23000,-135.61667 53.5 99999,-133.5 53.25 99999,-133.5 53.25 23000,-135.61667 53.5 23000)),((-133.5 53.25 23000,-133.5 53.25 99999,-132.91667 52.7 99999,-132.91667 52.7 23000,-133.5 53.25 23000)),((-132.91667 52.7 23000,-132.91667 52.7 99999,-131.05528 51.54167 99999,-131.05528 51.54167 23000,-132.91667 52.7 23000)),((-131.05528 51.54167 23000,-131.05528 51.54167 99999,-132.33833 50.63028 99999,-132.33833 50.63028 23000,-131.05528 51.54167 23000)),((-132.33833 50.63028 23000,-132.33833 50.63028 99999,-132.81833 50.60833 99999,-132.81833 50.60833 23000,-132.33833 50.63028 23000)),((-132.81833 50.60833 23000,-132.81833 50.60833 99999,-133.75 51 99999,-133.75 51 23000,-132.81833 50.60833 23000)),((-133.75 51 23000,-133.75 51 99999,-135 52.71667 99999,-135 52.71667 23000,-133.75 51 23000)))'), 4326),
ST_SETSRID(ST_GEOMFROMTEXT('POLYHEDRALSURFACE Z (((-135 52.71667 0,-137 53.3675 0,-136 54 0,-135.45 54.10833 0,-135.09111 54.11194 0,-133.5 53.25 0,-135.61667 53.5 0,-135 52.71667 0)),((-135 52.71667 99999,-135.61667 53.5 99999,-133.5 53.25 99999,-135.09111 54.11194 99999,-135.45 54.10833 99999,-136 54 99999,-137 53.3675 99999,-135 52.71667 99999)),((-135 52.71667 0,-135 52.71667 99999,-137 53.3675 99999,-137 53.3675 0,-135 52.71667 0)),((-137 53.3675 0,-137 53.3675 99999,-136 54 99999,-136 54 0,-137 53.3675 0)),((-136 54 0,-136 54 99999,-135.45 54.10833 99999,-135.45 54.10833 0,-136 54 0)),((-135.45 54.10833 0,-135.45 54.10833 99999,-135.09111 54.11194 99999,-135.09111 54.11194 0,-135.45 54.10833 0)),((-135.09111 54.11194 0,-135.09111 54.11194 99999,-133.5 53.25 99999,-133.5 53.25 0,-135.09111 54.11194 0)),((-133.5 53.25 0,-133.5 53.25 99999,-135.61667 53.5 99999,-135.61667 53.5 0,-133.5 53.25 0)),((-135.61667 53.5 0,-135.61667 53.5 99999,-135 52.71667 99999,-135 52.71667 0,-135.61667 53.5 0)))'), 4326)
]

)
)
)).geom
)) AS geom
) as geom_item;

result of union polyhedral z result of union polyhedral z


I'm trying as many tricks as I can find or think but each output the same result and I feel that this might not be possible. I looked into ST_ConcaveHull but I'm not sure that would for in my scenario.


Is it possible to merge multiple 3D geometries (polyhedralsurfacez) that have varying height and output as a single polygon (no interior walls)?


Or is this outside what is possible with postgis functions?





No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...