2

Totally new to GIS and could use a hand. I have a database table tracks containing ADS-B data stored in a Postgres database. Each record has the following columns:

id, unix_time, latitude, longitude, altitude

I know I can select all records within some lat and lon using the ST_Within function. However, how do I deal with altitude? Is it enough to first filter using ST_Within, and then only select the points that lie within some defined altitude range? Or, is there another way I should be approaching this (i.e., taking the ellipsoid nature of the Earth into consideration)?

1 Answer 1

0

These are the 3d functions available to you:

ST_3DClosestPoint — Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line.

ST_3DDistance — For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.

ST_3DDWithin — For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units.

ST_3DDFullyWithin — Returns true if all of the 3D geometries are within the specified distance of one another.

ST_3DIntersects — Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area).

ST_3DLongestLine — Returns the 3-dimensional longest line between two geometries

ST_3DMaxDistance — For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units.

ST_3DShortestLine — Returns the 3-dimensional shortest line between two geometries

It sounds like you are trying to get points within a 3d line or 3d polygon? If so, you should be able to use st_3dintersects to test which points intersect the 3d surface/line.

You can also play with N-D indexes, which use the coordinate dimension of the geometry instead of the standard 2D index. You create that using gist_geometry_ops_nd like this:

CREATE INDEX nyc_streets_gix_nd ON nyc_streets
USING GIST (geom gist_geometry_ops_nd);

Once you have the N-D indexes, you can do bounding box queries with the &&& operator.

docs:

https://postgis.net/docs/manual-3.3/ST_3DIntersects.html

https://postgis.net/workshops/postgis-intro/3d.html

https://postgis.net/docs/manual-3.3/reference.html

2
  • Yes, I want to get all points within a 3D polygon. st_3dintersects might be the way to go, thanks. If I wanted to instead get all points within a certain radius of another point, am I correct in assuming that ST_3DWithin would be the function needed?
    – pookie
    Commented Sep 7, 2022 at 17:44
  • Yes, but it's st_3ddwithin - 2 D's for 3D Within Distance
    – jbalk
    Commented Sep 7, 2022 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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