Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
1  
see postgresql.org/docs/9.1/static/plpgsql-control-structures.html . FOR loop is solution , im not sure if it is correct way to do it because it sounds slow. But at leat i dont my head around how it should be done. And do you want find 1 nearest point on 3 lines or something else ? –  simplexio Mar 7 at 9:51
    
I will try . . . –  Kumar Mar 7 at 10:11

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
indeed. using ST_DWithin() in index_query is only way i see to get it faster , but that adds limitations for usage. –  simplexio Mar 7 at 11:27
    
Thanks buddy... It works. two changes I made, first renaming l.geom,p.geom as lgeom and pgeom and used it in the outer st_ClosestPoint function, then inside the with clause I added where clause with distance < 50km like. It works nice. Then what is the adv of <#> operator in searching nearby objects? –  Kumar Mar 7 at 13:48
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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