1

The below query gives result in wrong order.

SELECT *, ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(-75.66,45.33), 4326 ) , geometry ) / 1609.34 AS distance 
  FROM myTable 
 ORDER BY geometry <#> ST_SetSRID(ST_MakePoint(-75.66,45.33), 4326 ) LIMIT 1 
  • Index on the geometry & geography exists.

  • I have also tried st_distance in order by, results are perfect but It does not use geometry or geography index.

  • I have tried st_DWithin but it is also taking time. for a shorter distance, Dwithin is working fine, but in this case, search distance is at least with 5000 miles.

PostGreSQL version: 9.4 No of rows: almost a million.

how knn fits in above scenario?

I have upgraded version from 9.4 to 9.5. It's working Updated working query is:

SELECT *, ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(-75.66,45.33), 4326 ) , geometry ) / 1609.34 AS distance 
FROM myTable 
ORDER BY geometry <-> ST_SetSRID(ST_MakePoint(-75.66,45.33), 4326 )::geography LIMIT 1
  • 2
    You have a full table scan query, yet you expect an index to be used? Please edit the question to provide all the details usually required in SQL questions: Versions of software, row count and extent of the spatial table(s), indexes created, and the EXPLAIN plan and actual times (in milliseconds) for your queries. – Vince May 1 '17 at 10:58
  • @Vince: Thanks for enlighting, ill update all the plan soon. – Murli Jha May 1 '17 at 11:50
  • 1
    If you are running PostGIS < 2.2 or PostgreSQL < 9.5, KNN # is not sufficient to guarantee exact distance. Also you should be using <-> not <#> for true distance in 9.5+, PostGIS 2.2 – LR1234567 May 1 '17 at 13:19
  • @LR1234567: Yes I have upgraded PostgreSQL version from 9.4 to 9.5. and It did work. Thanks. – Murli Jha May 2 '17 at 8:56