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
Written by dcdieci
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Performance
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#