3

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?

  • It might be, but it is very hard to tell from your example query. First of all, ST_3DUnion only works on two geometries at a time and, second, you are using 4326, but you have z values such as 25500. I think the z value should be in the same measurement system as your SRID. – John Powell Feb 13 '18 at 12:53
  • Can you provide 2 geometries that demonstrate the behaviour you are referring to -- ie, interrior walls, after a union. If it is possible to get that working, then it is always possible to generalize to more than 2. – John Powell Feb 13 '18 at 13:15
  • As far as I can tell, 4326 support height dimension spatialreference.org/ref/epsg/wgs-84 – cmac Feb 13 '18 at 14:43
1

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;

enter image description here

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.

  • I get errors if I just run the ST_3DUnion part of this, without all the geosjon, exteriorring, dump stuff: NOTICE: During union_3d(A,B) : ERROR: TriangulatedSurface is invalid : inconsistent orientation of PolyhedralSurface detected at edge 2 (7-4) of polygon 61 . My guess is that you have to set the right hand rule correctly, first. – John Powell Feb 13 '18 at 15:23
  • Hmm, thats seems strange. If I simply run only the ST_3DUnion for the two geometries it returns a TIN Z geometry – cmac Feb 13 '18 at 15:35
  • I may have a different version of Postgis, sfgcal, linux, etc :-). I am just starting to use the 3D functionality in anger, and while it seems very powerful, I am getting some interesting errors every now and then. Cool image, btw, what did you use to generate it: ST_AsX3D? – John Powell Feb 13 '18 at 15:39
  • Using Postgis 2.3.0 with sfgcal. I think having the latest will help. The geojson generated is loaded into CesiumJS which is what you're seeing in the images. Its open source geospatial 3D mapping in javascript. – cmac Feb 13 '18 at 16:11
  • I have 2.3.3. Yes, I am familiar with CesiumJS, from my OpenLayers 3 days. I was going to test ST_AsX3D, but I prefer using JSON, so I might try your approach. Nevertheless, I still get the same error messages as above. – John Powell Feb 13 '18 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.