Speed up pgRouting point_to_id using new <-> operator in PostGIS 2.0.0

Question

The point_to_id function, which assigns a node id to a point based on existing nodes and tolerance, is using a query as follows:

SELECT
id, the_geom
FROM vertices_tmp WHERE
    ST_DWithin(_p,the_geom, tolerance) 
ORDER BY ST_Distance(_p,the_geom) LIMIT 1;

This example is very similar to the one used to explain the new KNN index based queries. I modified the query like this:

SELECT
id, the_geom
FROM vertices_tmp WHERE
    ST_DWithin(_p,the_geom, tolerance) 
ORDER BY _p <-> the_geom LIMIT 1;

However, as the documentation for '<->' says, the index kicks in only if one of the two operands is constant, as in ST_MakePoint(10,10).

Is there any trick to get the index to be used also in a function?

See the last comment on this question. Maybe it help.
http://gis.stackexchange.com/questions/24456/nearest-neighbor-problem-in-postgis-2-0-using-gist-index-function — Alexandre Neto, Aug 27 '12 at 08:42

score 2 · Answer 1 · answered Apr 27 '12 at 09:10

For now, I resorted to using the new topology layers, using

UPDATE mytable 
SET source = topology.TopoGeo_AddPoint('nodes_topo', the_geom, 0.0001);
UPDATE mytable 
SET target = topology.TopoGeo_AddPoint('nodes_topo', the_geom, 0.0001);

Which adds also a new layer in which I can check the topology points' positions. I wonder if those functions are optimized using the new KNN indexes, though.

Speed up pgRouting point_to_id using new <-> operator in PostGIS 2.0.0

1 Answers1