ActiveRecord query with find_by_sql on postgis data

Question

To date, I have only been able to run a find_by_sql query involving all records of two tables to find Intersects.

Regionpolygon.where('nation_id = ?', 74).find_by_sql "SELECT regionpolygons.id, area_name, destinations.id 
  FROM regionpolygons, destinations 
  WHERE ST_Intersects(regionpolygons.polygon_area, destinations.latlon)"

Two goals which need to be achieved are:

having a smaller subset from which to query regionpolygons @rps = Regionpolygon.where('nation_id = ?',74).all This appears to work..
providing a single point from the destinations table @dests = Destination.all @dests.each do |dest| [...] so that an iteration can allow for updating record attributes instance variables do not appear to be well digested when added to this type of query

How can this query be formulated?

engineersmnky · Accepted Answer · 2019-04-11 17:43:05Z

1

Your question is a bit unclear but it you are just looking for a manageable and programmatic way to generate that query then, you could perform this search with arel as follows

rp_table = Regionpolygon.arel_table
destination_table = Destination.arel_table

query = rp_table.project(rp_table[:id], 
           rp_table[:area_name], 
           destination_table[:id].as('destination_id')
   ).join(destination_table).on(
       Arel::Nodes::NamedFunction.new('ST_Intersects', 
         [rp_table[:polygon_area], destination_table[:latlon]]
       )
   ).where(rp_table[:nation_id].eq(74))

This will produce the following SQL

SELECT 
    [regionpolygons].[id], 
    [regionpolygons].[area_name], 
    [destinations].[id] AS destination_id 
FROM 
    [regionpolygons] 
    INNER JOIN [destinations] ON 
        ST_Intersects([regionpolygons].[polygon_area], [destinations].[latlon])     
WHERE 
    [regionpolygons].[nation_id] = 74

You can convert query to SQL by literally calling to_sql. So:

ActiveRecord::Base.connection.exec_query(query.to_sql).to_hash

Will return an Array of the rows it found performing the above where the rows are converted to hashes. This hash will look like:

 {"id" => 1, "area_name" => "area_name", "destination_id" => 1}

answered Apr 11, 2019 at 17:43

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerome Over a year ago

since the goal is to iterate over a collection, I modified the where clause to ).where(rp_table[:nation].eq(74).and(destination_table[:id].eq(4079))). Very clear and thus useful answer; I had totally glossed over AREL, yet wonder why find_by_sql was so 'unscoped' ...

Collectives™ on Stack Overflow

ActiveRecord query with find_by_sql on postgis data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related