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.
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.
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.