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?