I've been working with PostGIS for a while now and I'm able to run queries over huge datasets fairly easy and fast. However, I do have an issue now that I don't know how to solve. I did find some work-arounds, although they mean lower performance, higher execution times and more complex queries.
The objective is very simple: to get all the points within a volume. But, the volume is not a simple extrusion of a polygon, it's rather the difference of a greater volume, minus some smaller volumes here and there.
My first attempt to tackle the problem was inspired by this question, although it didn't work:
- Create the master polygon, using a third axis (Z), to use as the base for the volume:
ST_Polygon(ST_GeomFromText('LINESTRING(Longs, Lats, Zs...)'), 4326);
- Extrude the polygon using the height of the volume, getting a POLYHEDRALSURFACE as a resulting geometry:
ST_Extrude(Previous_polygon, x=0, y=0, z=height);
- Having a table with the information regarding each master volume and its small volumes to be removed from, I created a new geometry removing all these small volumes from the master volume:
ST_3DDifference(Master volume, combination of smaller volumes to be removed);
- Query the table with the positions and joining the resulting master volume on
3dpoints.geometry &&& Master_Volume.geometry
As I said, it didn't work because the points were found also outside the master volume. Not sure why, though. I guess I'll try to come back to that option should the following solution be too much of a hassle.
The second attempt I tried was:
- Query the table with the positions straightaway, merging the volume as a 2D polygon, and including the filter on the Z axis, something like:
ST_Contains(Master_Polygon.geometry, 2dpoints.geometry) AND 2dpoints.Z BETWEEN Master_Polygon.MinZ AND Master_Polygon.MaxZ
- Of course, this included all the point within the smaller volumes I didn't want to consider, so then I added the condition to remove those points:
ST_Contains(Master_Polygon.geometry, 2dpoints.geometry) AND 2dpoints.Z BETWEEN Master_Polygon.MinZ AND Master_Polygon.MaxZ AND NOT (ST_Contains(Smaller_Polygon.geometry, 2dpoints.geometry) AND 2dpoints.Z BETWEEN Smaller_Polygon.MinZ AND Smaller_Polygon.MaxZ)
And it does work, although only when the smaller volume (polygon) is only one. Should the smaller volumes be more than one, then the condition is not enough as it would create different set of points based on each condition. I.e. all the points within the master volume but not in the smaller volume A would be returned, and all the points within the master volume but not in the smaller volume B, which could then be in A, etc.
Is there a way to get all the points in the master volume and add the condition to remove all those points that are in any of the smaller volumes at once?
I apologise for the long post, but it's a rather complex explanation of the issue. I did find a solution, and it's by splitting the master volume into smaller volumes to be then merged after the query, but it's not quite right, especially when I will have to update the geometries and make the master volume to change accordingly automatically.