2

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?

abraham
  • 46,583
  • 10
  • 100
  • 152
brianbancroft
  • 463
  • 3
  • 17

1 Answers1

3

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.

abraham
  • 46,583
  • 10
  • 100
  • 152
dbkbali
  • 146
  • 2