1

ST_Overlaps, ST_Contains, ST_Touches, ST_Within works for TIN with point data. But, when I try with 2 TIN it gives error. Could you please help me understand the reason for failure and how to fix it. I need comparisons only between TIN objects.

SELECT ST_Overlaps
('TIN Z (((0 0 0,0 0 0.5,0 0.5 0.5,0 0 0)),((0 0.5 0,0 0 0,0 0.5 0.5,0 0.5 0)))'::geometry,
 'POINT(0.5 0 0.5)'::geometry);

SELECT ST_Overlaps
('TIN Z (((0 0 0,0 0 0.5,0 0.5 0.5,0 0 0)),((0 0.5 0,0 0 0,0 0.5 0.5,0 0.5 0)))'::geometry, 
 'TIN Z (((0 0 0,0 0 0.5,0 0.5 0.5,0 0 0)),((0 0.5 0,1 1 1,1 0.5 0.5,0 0.5 0)))'::geometry);

ERROR: Unknown geometry type: 15 - Tin
CONTEXT: SQL function "st_overlaps" statement 1

-Note: ST_3dIntersects works with 2 TIN objects

1

It is something of a hack, but you can use a the replace function on the TIN Zs, convert those to POLYGON Zs, and then use ST_Dump to extract the individual triangles as polygons. For example:

WITH 
    tin (id, geom) AS (
       SELECT (ST_Dump(
                  ST_GeomFromText(
                     replace(
                        'TIN Z (((0 0 0,0 0 0.5,0 0.5 0.5,0 0 0)),
                        ((0 0.5 0,0 0 0,0 0.5 0.5,0 0.5 0)))'::text, 
                        'TIN Z', 'MULTIPOLYGON')))
               ).*), 
    tin1 (id, geom) AS (
        SELECT (ST_Dump(
                   ST_GeomFromText(
                     replace(
                         'TIN Z (((0 0 0,0 0 0.5,0 0.5 0.5,0 0 0)),
                          ((0 0.5 0,1 1 1,1 0.5 0.5,0 0.5 0)))'::text, 
                          'TIN Z', 'MULTIPOLYGON')))
                ).*)
 SELECT 
      a.id[0], 
      b.id[0], 
      ST_3dIntersects(a.geom, b.geom) 
    FROM tin a, tin1 b;

As there is no 3D equivalent of ST_Contains, you would have to ensure that your TINs are closed in 3D (ie, are solids) and use ST_3DIntersection of of the two TINs and compare against ST_Volume of each individual one.

  • Thanks John Powell. I will try this and see if the results are as expected. Also, could you please recommend any book/ source which has query examples like the one you mentioned. All the books/ sources that I have seen so far just explains the function with very simple query, like the one I wrote in this post. I have worked on complex SQL and PLSQL but I am very new to Spatial databases and couldn't write/ think of complex inner queries or joins. Any pointers are appreciated. – fastRunner Feb 20 '18 at 19:16
  • There is a section on 3D in this book, and the blog of one of the authors, has more examples of this nature. – John Powell Feb 21 '18 at 10:20
  • Here is the git source I referred to above, with some of this TIN manipulation type code. – John Powell Feb 21 '18 at 10:30

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.