Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
See the last comment on this question. Maybe it help. [gis.stackexchange.com/questions/24456/… [1]: gis.stackexchange.com/questions/24456/… –  Alexandre Neto Aug 27 '12 at 8:42
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.