3

What is the fastest algorithm to get the nearest line segment to a point (that is not on any line segment) in a GIS database?

Noura
  • 3,429
  • 3
  • 20
  • 41
daydayup
  • 192
  • 7

1 Answers1

4

Using the <#> operator:

SELECT *
FROM point
CROSS JOIN line
WHERE [condition for the points];
ORDER BY point.geom <#> line.geom
LIMIT 1;

Make spatial index before running this query, to speed up.

Create index pnt_idx on point using GIST(geom);
Create index lin_idx on line using GIST(geom);`
Evan Carroll
  • 7,071
  • 2
  • 32
  • 58
Zoltan
  • 7,325
  • 17
  • 27
  • You should use <-> to return the real distance instead of the bounding box distance. And I think that point.geom will not be available inside the subquery. Better to do something like this: select point.id from point, line order by point.geom <-> line.geom limit 1 – pLumo Sep 07 '17 at 11:40