how to Find n nearest lines for the given points from a collection of lines?

Question

I have two tables, points and lines. For each point in point table, I want to find out nearest 3 points in the nearest 3 cables.

I almost developed a query, but it is best suitable for single point, but i need a query for all the points in the point table.

My query is

select * from (
with index_query as (
select l.data,p.data, ST_Distance(c.geom,p.geom) as distance,
ST_AsText(st_line_interpolate_point(l.geom, 
st_line_locate_point(l.geom, p.geom))) as nearest_point_in_line, 
from lines l,points p order by l.geom <#> p.geom limit 100)
select *,RANK() OVER (PARTITION BY pid order by distance ) AS r
from index_query ) as s where r < 4

LIMIT 100 is the limiting factor, which is limiting total output rows to 100 irrespective of number of points in Point table.

I need nearest 3 point_in_line for all points in my table. Total 3*no of rows in point table. How to achieve it?

Answer 1

I think your solution is quite elegant.

I guess what you want to do that you don't succeed in doing is getting help from indexes distance ordering. From the query it looks like you are aware that the <#> operator only works on the bounding box not the real geoemtry. But you will get in another trouble. As stated in the documentation: http://postgis.net/docs/manual-2.1/geometry_distance_box.html

"Index only kicks in if one of the geometries is a constant e.g. ORDER BY (ST_GeomFromText('POINT(1 2)') <#> geom) instead of g1.geom <#>."

That means you will not get the help from the index.

The best you can do is reducing the calculations done on all combinations. So what I have done in the example below is moving out the calculation of closest point on line to after you have fount the three closest lines. In your query you do that calculation on all combinations.

As you see I also use ST_ClosestPoint instead of the combination of st_line_interpolate_point and st_line_locate_point. ST_ClosestPoint was added in PostGIS 1.5.

select *, ST_AsText(ST_ClosestPoint(l.geom, p.geom)) nearest_point_in_line from (
with index_query as (
select l.id, p.pid, l.data,p.data,l.geom, p.geom, ST_Distance(l.geom,p.geom) as distance
from lines l,points p)
select *,RANK() OVER (PARTITION BY pid order by distance ) AS r
from index_query ) as s where r < 4

HTH

Answer 2

SELECT row_number() as gid, 
ST_AsText(st_line_interpolate_point(g.geom, st_line_locate_point(g.geom, g.pgeom))) as nearest_point_in_line, 
  FROM 
  (SELECT p.geom, k.geom as pgeom,  p.gid as pgid, k.gid, ST_Distance(p.geom,k.geom)as dis
    , RANK() OVER (PARTITION BY k.gid ORDER BY ST_Distance(p.geom,k.geom) ASC)as r 
  from lines p , points k )as u WHERE u.r<4) as g 

Does that do the trick . inner sql returns 3 lines per point geom. Outer interpolates point. But main difference to your and @Nicklas Aven solution is that inner SQL does "everything" and outer just created new data. It would be fun to know if those two solutions have any performance difference for future reference.