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