3

I'm searching for the nearest ten neighboring points from a given point.

When I use this query:

SELECT id, geom, ST_Distance(geography(geom), ST_GeographyFromText('SRID=4326;POINT(-0.22707 51.5444204)')) as distance
FROM cars
ORDER BY geom <-> 'SRID=4326;POINT(-0.22707 51.5444204)'::geometry
LIMIT 10;

I don't get the same result as when I use this query:

SELECT id, geom, ST_Distance(geography(geom), ST_GeographyFromText('POINT(-0.22707 51.5444204)')) as distance
FROM cars
ORDER BY distance
LIMIT 10;

Three of the 10 points returned are different. And when viewing it in QGIS it looks like the best result is the last query. I need to use index-based KNN because of performance issues.

Does anyone have any ideas?

John Powell
  • 13,649
  • 5
  • 46
  • 62
Marco
  • 31
  • 1
  • 1
    Are you using PostGIS or something else? – PolyGeo Sep 10 '15 at 10:27
  • If your last query performs better and gets the right results why don´t you simply use it? What exactly is your issue here? – MakePeaceGreatAgain Sep 10 '15 at 10:28
  • last query gives me the right results and performs well with 300 points. But in near future i will have 3 millions points and ST_Distance will not perform as using indexed nearest neighbour search ( <-> ) . I'm using postgis 2.1.2 and postgreSQL 9.3.9 on linux. – Marco Sep 10 '15 at 11:17
  • 1
    Welcome to GIS SE. Please always specify the software and version in your question. You can use the edit link to update the question. – Vince Sep 10 '15 at 11:17
  • Please specify your Software/Database to clear up your question. – Mapperz Sep 10 '15 at 14:56

1 Answers1

2

Your second query is unbounded, and therefore a full table scan, with an expensive calculation and a sort. Assuming you are using PostgreSQL and PostGIS, ST_DWithin will place an indexed spatial constraint on that query, but you'll need to gauge the risk of returning fewer than ten rows in sparse locations against the expense of processing additional rows if the search distance is too large.

Vince
  • 20,017
  • 15
  • 45
  • 64
  • i think this could be the best solution. Neverthless, i still curious why indexed nearest search query <-> does not give me all the right points... – Marco Sep 10 '15 at 11:55
  • Your question does not state what coordinate system is used in the geom column, but it seems possible that the <-> operator is working in Cartesian degrees. – Vince Sep 10 '15 at 13:03
  • The question was why are the two queries different, not how to make it faster, which certain ST_DWithin will do. – John Powell Dec 19 '16 at 14:49