CODEWITHSUNDEEP
ruby-on-railspostgis
Jerome
Jerome

Reputation: 6189

ActiveRecord query with find_by_sql on postgis data

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:

How can this query be formulated?

Upvotes: 0

Views: 81

Answers (1)

engineersmnky
engineersmnky

Reputation: 29328

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}

Upvotes: 1

Related Questions