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;
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?
Best Answer
Simple attempt. 3D Union polyhedrons using ST_3DUnion. ST_3DUnion outputs as TIN Z triangles, so you need to create a new geometry out of it to eventually output as geojson.
This is being output as a multipolygon.
SQL:
SELECT ST_AsGeoJSON(ST_Collect(geom)) AS geom FROM ( SELECT ST_MakePolygon(ST_ExteriorRing( (ST_Dump( ST_3DUnion( 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)))'), 4979), 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)))'), 4979) ) )).geom )) AS geom ) as geom_item;Geojson:
{"type":"MultiPolygon","coordinates":[[[[-135.141835518355,52.896837701677,23000],[-135.61667,53.5,23000],[-135.12983103161,53.4424994249943,23000],[-135.141835518355,52.896837701677,23000]]],[[[-135,52.71667,23000],[-135.141835518355,52.896837701677,23000],[-135.12983103161,53.4424994249943,23000],[-135,52.71667,23000]]],[[[-133.5,53.25,23000],[-135,52.71667,23000],[-135.12983103161,53.4424994249943,23000],[-133.5,53.25,23000]]],[[[-135.61667,53.5,99999],[-135,52.71667,99999],[-133.5,53.25,99999],[-135.61667,53.5,99999]]],[[[-135.348401582673,53.1592298971175,56496.6835839953],[-135,52.71667,99999],[-135.61667,53.5,99999],[-135.348401582673,53.1592298971175,56496.6835839953]]],[[[-135.61667,53.5,23000],[-135.348401582673,53.1592298971175,56496.6835839953],[-135.61667,53.5,99999],[-135.61667,53.5,23000]]],[[[-135.348401582673,53.1592298971175,56496.6835839953],[-135,52.71667,23000],[-135,52.71667,99999],[-135.348401582673,53.1592298971175,56496.6835839953]]],[[[-135.348401582673,53.1592298971175,56496.6835839953],[-135.141835518355,52.896837701677,23000],[-135,52.71667,23000],[-135.348401582673,53.1592298971175,56496.6835839953]]],[[[-135.61667,53.5,23000],[-135.141835518355,52.896837701677,23000],[-135.348401582673,53.1592298971175,56496.6835839953],[-135.61667,53.5,23000]]],[[[-134.42080968898,53.3587568786088,56496.6835839953],[-135.61667,53.5,99999],[-133.5,53.25,99999],[-134.42080968898,53.3587568786088,56496.6835839953]]],[[[-133.5,53.25,23000],[-134.42080968898,53.3587568786088,56496.6835839953],[-133.5,53.25,99999],[-133.5,53.25,23000]]],[[[-134.42080968898,53.3587568786088,56496.6835839953],[-135.61667,53.5,23000],[-135.61667,53.5,99999],[-134.42080968898,53.3587568786088,56496.6835839953]]],[[[-134.42080968898,53.3587568786088,56496.6835839953],[-135.12983103161,53.4424994249943,23000],[-135.61667,53.5,23000],[-134.42080968898,53.3587568786088,56496.6835839953]]],[[[-133.5,53.25,23000],[-135.12983103161,53.4424994249943,23000],[-134.42080968898,53.3587568786088,56496.6835839953],[-133.5,53.25,23000]]],[[[-132.91667,52.7,23000],[-135,52.71667,23000],[-133.5,53.25,23000],[-132.91667,52.7,23000]]],[[[-133.75,51,23000],[-135,52.71667,23000],[-132.91667,52.7,23000],[-133.75,51,23000]]],[[[-132.91667,52.7,99999],[-135,52.71667,99999],[-133.75,51,99999],[-132.91667,52.7,99999]]],[[[-133.5,53.25,99999],[-135,52.71667,99999],[-132.91667,52.7,99999],[-133.5,53.25,99999]]],[[[-135,52.71667,23000],[-133.75,51,99999],[-135,52.71667,99999],[-135,52.71667,23000]]],[[[-135,52.71667,23000],[-133.75,51,23000],[-133.75,51,99999],[-135,52.71667,23000]]],[[[-132.81833,50.60833,23000],[-133.75,51,23000],[-132.91667,52.7,23000],[-132.81833,50.60833,23000]]],[[[-132.81833,50.60833,99999],[-132.91667,52.7,99999],[-133.75,51,99999],[-132.81833,50.60833,99999]]],[[[-133.75,51,23000],[-132.81833,50.60833,99999],[-133.75,51,99999],[-133.75,51,23000]]],[[[-133.75,51,23000],[-132.81833,50.60833,23000],[-132.81833,50.60833,99999],[-133.75,51,23000]]],[[[-132.91667,52.7,23000],[-133.5,53.25,99999],[-132.91667,52.7,99999],[-132.91667,52.7,23000]]],[[[-132.91667,52.7,23000],[-133.5,53.25,23000],[-133.5,53.25,99999],[-132.91667,52.7,23000]]],[[[-131.05528,51.54167,23000],[-132.33833,50.63028,23000],[-132.91667,52.7,23000],[-131.05528,51.54167,23000]]],[[[-132.33833,50.63028,23000],[-132.81833,50.60833,23000],[-132.91667,52.7,23000],[-132.33833,50.63028,23000]]],[[[-132.33833,50.63028,99999],[-132.91667,52.7,99999],[-132.81833,50.60833,99999],[-132.33833,50.63028,99999]]],[[[-132.33833,50.63028,99999],[-131.05528,51.54167,99999],[-132.91667,52.7,99999],[-132.33833,50.63028,99999]]],[[[-131.05528,51.54167,23000],[-132.91667,52.7,99999],[-131.05528,51.54167,99999],[-131.05528,51.54167,23000]]],[[[-131.05528,51.54167,23000],[-132.91667,52.7,23000],[-132.91667,52.7,99999],[-131.05528,51.54167,23000]]],[[[-132.81833,50.60833,23000],[-132.33833,50.63028,99999],[-132.81833,50.60833,99999],[-132.81833,50.60833,23000]]],[[[-132.81833,50.60833,23000],[-132.33833,50.63028,23000],[-132.33833,50.63028,99999],[-132.81833,50.60833,23000]]],[[[-132.33833,50.63028,23000],[-131.05528,51.54167,99999],[-132.33833,50.63028,99999],[-132.33833,50.63028,23000]]],[[[-132.33833,50.63028,23000],[-131.05528,51.54167,23000],[-131.05528,51.54167,99999],[-132.33833,50.63028,23000]]],[[[-135.61667,53.5,0],[-137,53.3675,0],[-136,54,0],[-135.61667,53.5,0]]],[[[-135,52.71667,0],[-137,53.3675,0],[-135.61667,53.5,0],[-135,52.71667,0]]],[[[-135,52.71667,99999],[-135.61667,53.5,99999],[-137,53.3675,99999],[-135,52.71667,99999]]],[[[-137,53.3675,99999],[-135.61667,53.5,99999],[-136,54,99999],[-137,53.3675,99999]]],[[[-137,53.3675,0],[-135,52.71667,99999],[-137,53.3675,99999],[-137,53.3675,0]]],[[[-137,53.3675,0],[-135,52.71667,0],[-135,52.71667,23000],[-137,53.3675,0]]],[[[-135,52.71667,99999],[-137,53.3675,0],[-135,52.71667,23000],[-135,52.71667,99999]]],[[[-136,54,0],[-137,53.3675,99999],[-136,54,99999],[-136,54,0]]],[[[-136,54,0],[-137,53.3675,0],[-137,53.3675,99999],[-136,54,0]]],[[[-135.45,54.10833,0],[-135.61667,53.5,0],[-136,54,0],[-135.45,54.10833,0]]],[[[-136,54,99999],[-135.61667,53.5,99999],[-135.45,54.10833,99999],[-136,54,99999]]],[[[-135.45,54.10833,0],[-136,54,99999],[-135.45,54.10833,99999],[-135.45,54.10833,0]]],[[[-135.45,54.10833,0],[-136,54,0],[-136,54,99999],[-135.45,54.10833,0]]],[[[-135.45,54.10833,0],[-135.09111,54.11194,0],[-135.61667,53.5,0],[-135.45,54.10833,0]]],[[[-133.5,53.25,0],[-135.61667,53.5,0],[-135.09111,54.11194,0],[-133.5,53.25,0]]],[[[-135.09111,54.11194,99999],[-135.61667,53.5,99999],[-133.5,53.25,99999],[-135.09111,54.11194,99999]]],[[[-135.45,54.10833,99999],[-135.61667,53.5,99999],[-135.09111,54.11194,99999],[-135.45,54.10833,99999]]],[[[-135.61667,53.5,0],[-135.12983103161,53.4424994249943,23000],[-135.61667,53.5,23000],[-135.61667,53.5,0]]],[[[-135.12983103161,53.4424994249943,23000],[-133.5,53.25,0],[-133.5,53.25,23000],[-135.12983103161,53.4424994249943,23000]]],[[[-135.12983103161,53.4424994249943,23000],[-135.61667,53.5,0],[-133.5,53.25,0],[-135.12983103161,53.4424994249943,23000]]],[[[-135,52.71667,0],[-135.141835518355,52.896837701677,23000],[-135,52.71667,23000],[-135,52.71667,0]]],[[[-135.141835518355,52.896837701677,23000],[-135.61667,53.5,0],[-135.61667,53.5,23000],[-135.141835518355,52.896837701677,23000]]],[[[-135.141835518355,52.896837701677,23000],[-135,52.71667,0],[-135.61667,53.5,0],[-135.141835518355,52.896837701677,23000]]],[[[-135.09111,54.11194,0],[-135.45,54.10833,99999],[-135.09111,54.11194,99999],[-135.09111,54.11194,0]]],[[[-135.09111,54.11194,0],[-135.45,54.10833,0],[-135.45,54.10833,99999],[-135.09111,54.11194,0]]],[[[-133.5,53.25,23000],[-135.09111,54.11194,99999],[-133.5,53.25,99999],[-133.5,53.25,23000]]],[[[-133.5,53.25,0],[-135.09111,54.11194,99999],[-133.5,53.25,23000],[-133.5,53.25,0]]],[[[-133.5,53.25,0],[-135.09111,54.11194,0],[-135.09111,54.11194,99999],[-133.5,53.25,0]]]]}In the image, you can see all the individual polygons randomly colored.
The problem seems that trying to make a polygon out of the individual triangles only results in them all being triangles.