CODEWITHSUNDEEP
ruby-on-railsactiverecordpostgisrgeo
brianbancroft
brianbancroft

Reputation: 472

Order by Nearest using PostGIS, RGeo, Spatial Adapter

I'm asking this question because the answers I've found in Order by nearest - PostGIS, GeoRuby, spatial_adapter wasn't able to provide a solution. I'm trying to create a controller method that can return the n closest records to a certain record's lonlat. The record it is querying against is from the same table. This concept isn't such a big stretch if I was doing this completely in SQL. That much is clear in the linked example and below is a specific case where I obtained a result: 

condos_development=#
  SELECT id, name FROM condos
  ORDER BY ST_Distance(condos.lonlat, ST_PointFromText('POINT(-71.06 42.45)'))
condos_development-#
  LIMIT 5;

My problem is in making this work with ActiveRecord. I'm using a method that was inspired by the response by @dc10 but I'm unable to create a working query either through the RGeo methods, or direct SQL. Here's what I have so far:

def find_closest_condos(num, unit)
  result = Condo.order('ST_Distance(condos.lonlat, ST_PointFromText("#{unit.lonlat.as_text)}")')
                .limit(5)
end

The response from this attempt is as follows:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "LIMIT" 10:29:50 rails.1 LINE 1: ...lonlat, ST_PointFromText("#{unit.lonlat.as_text)}") LIMIT $1

Would someone be able to set me on the right track on how to put this work query together so that I can make it work in Rails?

Upvotes: 2

Views: 553

Answers (1)

dbkbali
dbkbali

Reputation: 146

The problem is with how active record is resolving your query to SQL, also the position of the Limit clause. If you change the query to this:

Condo.order("ST_Distance(lonlat, ST_GeomFromText('#{unit.lonlat.as_text}', 4326))")
     .limit(num)

You should find this works.

Upvotes: 3

Related Questions