0

What is the best way to store 3D TIN data in postgis, to support queries like st_intersects, st_within, st_overlaps, etc. I can't use TIN since it only supports st_intersects.

1

There are now 3D functions in Postgis, including ST_3DIntersects. Postgis supports TINs natively, via the SFGCAL extension, which you can install with:

CREATE EXTENSION postgis_sfcgal;

with Postgis 2.2 or above.

This Postgis blog article gives some more detailed explanations. Here is a link to the sfgcal project -- the Postgis functions are just a wrapper around the sfcgal functions, works which was done by

So now, going back to ST_3DIntersects and TINs, from the docs, you can write a query like:

SELECT ST_3DIntersects(
      'TIN(((0 0,1 0,0 1,0 0)))', 
      'POINT(.1 .1)');

and the geometry datatype supports TINs natively, as well as Polyhedral surfaces.

You can easily extend this to 3D:

SELECT ST_3DIntersects(
     'TIN(((0 0 0,1 0 0,0 1 1,0 0 0)))', 
     'POINT(.1 .1 .1)');

Note: In order for TINs to be recognized, you either have to preface your query with:

set postgis.backend = sfcgal;

or, better, set it at the database level:

ALTER DATABASE <your_db> SET postgis.backend = sfcgal;

which will require you to log out and back in again from your terminal session (this is true with psql on linux, anyway).

I don't usually give book recommendations, and I am in no way connected to the authors, but I came across this outstanding book Postgis Cookbook, which has some very good chapters on 2.5 and 3D functionality, pointclouds, and a few other things for which it is harder to find documentation.

Finally, again no association, but I think is fair to mention oslandia who apparently did all the work to make sfgcal available to Postgis. The ability to do raster, vector, 3D, all inside the DB is making Postgis astonishingly powerful.

  • Thanks a lot John Powell for the info.I would definitely check the book. I would install SFGCAL and try to ran those queries. Before this post, I referred to one Boundless article that lists which all functionalities are available with 3D TIN data. And I found the basic queries that I want to handle are not supported for TIN, so started looking for other data type.(connect.boundlessgeo.com/docs/suite/4.5/postgis/postgis/html/…) – fastRunner Feb 14 '18 at 2:10
  • Great. Can you please accept the answer, if it has helped you? Keep up the running :D – John Powell Feb 14 '18 at 9:02

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.