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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
