PostGIS: Find nearest point from a list of 3D points

Question

Using a PostGIS databse I would like to filter from a list of points (stored as geometry in a table) the one closest to a certain point passed to the query.
I have already tried ST_3DClosestPoint, but they always talk about a point on a line.
How can I filter my list so that only the 3D point of my point cloud that is closest to the given point is determined? Is there any chance to do this with PostGIS (version 2.5)?

Edit The table structure and some example data:

CREATE TABLE points_list (id SERIAL PRIMARY KEY, name VARCHAR(64), geom GEOMETRY(POINTZ, 3857));

INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571450, 5800300, -246.028076), 31468), 3857));
INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571550, 5800300, -246.033478), 31468), 3857));
INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571650, 5800300, -246.040100), 31468), 3857));
INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571750, 5800300, -246.062714), 31468), 3857));
INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571850, 5800300, -246.104797), 31468), 3857));
INSERT INTO points_list (geom) VALUES (ST_TRANSFORM(ST_SetSRID(ST_MakePoint(4571950, 5800300, -246.162323), 31468), 3857));

Then, the query should ask for the closest point passed into the query to e.g, 4571547, 5800297, -246,0312. I would expect entry number 2 of my example values to be the result of this query.

Could you add 1) sample data 2) the table structure and 3) the exact expected result? — Jim Jones, Jan 23 '20 at 08:30
Would this be what you're looking for? `SELECT id, ST_Distance(geom,ST_SetSRID(ST_MakePoint(4571547, 5800297, -246,0312),3857)) as dist FROM points_list ORDER BY dist LIMIT 1` cannot test right now — Jim Jones, Jan 23 '20 at 09:05
It would have to be something like that. I replaced `ST_Distance` to `ST_3DDistance` because the z-dimension is also important. Unfortunately I always get point 6 of my example data with a distance of 3293614.81946199, even if I set the coordinates in the query to the values of the 2nd point of my example data. — SnoopyBrown, Jan 23 '20 at 09:34

Jim Jones · Accepted Answer · 2020-01-23T13:46:35.027

2

You were really close.

Taking data sample into account, you're doing a lot of SRS transformations to populate your table. So, the stored SRS needs to match the one used in the query as well.

SELECT id, 
  ST_3DDistance(
    geom,
    ST_Transform(
      ST_SetSRID(
        ST_MakePoint(4571547, 5800297, -246,0312),31468),3857)) AS dist
FROM points_list
ORDER BY dist LIMIT 1;

 id |       dist        
----+-------------------
  2 | 6.936250729464996
(1 Zeile)

You can avoid using ST_Transform if you already can provide ST_3DDistance with the coordinates in the SRS 3857.

edited Jan 23 '20 at 13:46

answered Jan 23 '20 at 09:52

Jim Jones

18,404
3
35
44

1

This works as expected :tada: Yeah, I know. But the provided coordinates come from another application which uses 3857 which is not the SRS of the original data. So I have to transform the coordinates somewhere (whether at the import or at the request). – SnoopyBrown Jan 23 '20 at 10:41
@SnoopyBrown in this case this query is the most straightforward option to get the 3D distance :) cheers – Jim Jones Jan 23 '20 at 10:47

PostGIS: Find nearest point from a list of 3D points

1 Answers1