[postgis-users] How find the nearest vertex between lines of a dataset

[aperi2007]

Hi,

In a linestring dataset ,
I need to find the nearest vertex of some other line and the startpoint 
(or endpoint) of each line in the dataset.

sometime like this:


                             O
                            /
                           *
  (end)           start)  /
    O------*----*----O   * <--this is the nearest vertex to startpoint
                        /
                       *
                      /
                     O


To do this I produce a tables of all points and other two tables with 
the start-points and the end-points.
(and their spatial-indexes)

And run this query:

select
     a.id as id_a,
     b.id_id as id_b,
     a.geom <-> b.geom as dist
from
     uso_suolo.terrazzamenti_startpoint as a,
     uso_suolo.terrazzamenti_points as b
where
     a.id <> b.id_id
order by
     a.geom <-> b.geom
limit 1
;

I try to use the new operator "<->" to have the nearest vertex.

But this query is really time consuming.

As confirmed from the explain:

Limit  (cost=271947652.78..271947652.78 rows=1 width=264)
   ->  Sort  (cost=271947652.78..288121428.64 rows=6469510344 width=264)
         Sort Key: ((a.geom <-> b.geom))
         ->  Nested Loop  (cost=0.00..239600101.06 rows=6469510344 
width=264)
               Join Filter: (a.id <> b.id_id)
               ->  Seq Scan on terrazzamenti_startpoint a 
(cost=0.00..751.55 rows=33655 width=132)
               ->  Materialize  (cost=0.00..9745.54 rows=192236 width=132)
                     ->  Seq Scan on terrazzamenti_points b 
(cost=0.00..5029.36 rows=192236 width=132)

Surely I'm wrong something , but I don't understand what,
and perhaps I don't understand how work the new operator "<->".

There is a faster method to find this vertexs ?

Many thx,

Andrea Peri.