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

[Paul Ramsey]

The <-> and <#> operators don't provide index assisted join
conditions, you have to have a constant on one side of them. So you
could script up an iteration that runs through all your ends points
and runs a single query to find the nearest vertex, but you can't do
it in a single SQL statement.

P.

On Tue, Nov 1, 2011 at 6:06 AM, aperi2007 <aperi2007 at gmail.com> wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>