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

closed as unclear what you're asking by mgri, Vince, Andre Silva, Mapperz May 1 '17 at 13:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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