qxcy-q
Last Updated: February 25, 2016
·
459
· dcdieci

Rails - postgisnearest neighbor performance

I recently had to implement a nearest neighbor search in rails with postgres postgis.

I am running on:
- osx maverciks
- rails 4.03
- postgres 9.3.3

I created the following scope on a large data table (400k entries). lonlat column has an index.

scope :closest, ->(point) { 
  where("ST_Distance(lonlat, ST_WKTToSQL('#{point.as_text}')) < 5000 ")
  .order("ST_Distance(lonlat, ST_GeomFromText('#{point.as_text}', #{SRID}))").limit(5) }

Load (748.7ms)

compared to

scope :closest, ->(point) 
  where("lonlat && ST_Expand(ST_WKTToSQL('#{point.as_text}'), 5000)")
  .order("ST_Distance(lonlat, ST_GeomFromText('#{point.as_text}', #{SRID}))").limit(5) }

Load (1327.8ms)

so in order to speed up your query it seems that st_distance is much faster compared to the other where condition