[postgis-users] trouble getting nearest neighbor with ST_DWithin and ST_Distance

[Karsten Vennemann]

>>>  What is the SRID of the geometry column in your roads table? >>>
The SRID is 4326. That' why I used the transform to 9102003.

>>>  The reason I ask is that in your query you're forcing PostGIS to
>>> reproject every single row on the >>>  roads table which is why it seq
>>> scans.
Oh ok. I was assuming that step would only do the transform on original
geometries found within the bounding box...

>>>  Assuming that all the geometries in your roads table are SRID 9102003, 
>>>  then as a starting point, the following query should be quick:
>>>  SELECT * FROM roads WHERE ST_DWithin(roads.the_geom, 
>>>  GeomFromText('POINT(-2026631.3499604 1355302.2855377)', 9102003), 50)
I added a second geometry column the_geom2 with SRID 9102003, and created an
gist index on it (took a total of 6 hours or so for both) and voila the
whole query is pretty fast and retrieves one row in 310ms:
SELECT * FROM roads WHERE ST_DWithin(roads.the_geom2,
GeomFromText('POINT(-2260366.9053948 253261.48812865)', 9102003), 150)
ORDER BY ST_Distance(roads.the_geom2, GeomFromText('POINT(-2260366.9053948
253261.48812865)', 9102003))
limit 1;
Thanks
Karsten


-- 
View this message in context: http://www.nabble.com/trouble-getting-nearest-neighbor-with-ST_DWithin-and-ST_Distance-tp25467509p25486772.html
Sent from the PostGIS - User mailing list archive at Nabble.com.