Find n Nearest Neighbors for given Point using PostGIS?

Now since PostGIS 2.0, there's a KNN index for geometry types available. This gives you nearest 5 records with regard to how far they are away from "your location...".

SELECT *
FROM your_table 
ORDER BY your_table.geom <-> "your location..."
LIMIT 5;

See <-> operator in PostgreSQL manual.


As I think you were answered at the list the unit is in degrees so you area almost searching the whole world with 300 degrees in st_dwithin.

If your dataset is that big so you can't work in a projected meterbased projection instead (much faster and less cpu-intensive calculations) you should consider using the geograpphy type instead. Then you can use st_dwithin with meter.

The make things faster you should I would just create a new table with the geometry converted to geography.

But to just test it you can cast on the fly:

SELECT start.asciiname, ende.asciiname, 
ST_Distance(start.geom::geography, ende.geom::geography) as distance 
FROM geoname As start, geoname As ende 
WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid AND
ST_DWithin(start.geom::geography, ende.geom::geography, 300) 
order by distance 
limit 5;

HTH Nicklas