How to rewrite this RGeo query using AR/Arel?

I have the following scope I've gotten to work with raw SQL:

class DeliveryZone < ActiveRecord::Base
  def self.contains(addressable)
    point = addressable.lonlat
    where(<<-SQL.squish)
    ST_Intersects("delivery_zones"."shape", ST_GeomFromText('#{point}'))
    SQL
  end
end

Where delivery_zones.shape is a geography(Polygon,4326) and point is a geography(Point,4326), for PostgreSQL types.

In a rails console, they are a #<RGeo::Geos::CAPIPolygonImpl> and #<RGeo::Geos::CAPIPointImpl> respectively.

I want to write something more akin to

where(arel_table[:shape].st_intersects(point))

... but that gives me this error:

RuntimeError: unsupported: RGeo::Geos::CAPIPointImpl

Would love some help getting raw SQL out of my model! Also, I'm a RGeo/PostGIS newbie, so please don't assume I know what I'm doing. :D

Answers


Please also don't assume I know what exactly you're talking about, but I use the squeel gem to write rails-style queries with rgeo and activerecord-postgis-adapter. Daniel Azuma wrote some great blogs about this topic which got me started a couple of years ago. Here is the first one of the series: http://daniel-azuma.com/articles/georails/part-1

From what I understand, this might help a bit:

class DeliveryZone < ActiveRecord::Base
  def self.contains(addressable)
    where{st_intersects(:shape, addressable)}
  end
end

Note this only works if you've installed the squeel gem. And if you want to be particular about it, maybe you should use this instead:

class DeliveryZone < ActiveRecord::Base
  def self.contains(addressable)
    where{st_contains(:shape, addressable)}
  end
end

Glad somebody else is using rails for GIS stuff. Best of luck


You can do this without arel or squeel:

where("delivery_zones.shape && ?", addressable.lonlat.to_geometry)

In general, this should work:

where("point_column && ?", rgeo_object.to_geometry)

Here's an example with a model of City with a point column called coordinates, which is a st_point. I want to query all the cities in a bounding box defined by two corner points (SE & NW):

box = RGeo::Cartesian::BoundingBox.create_from_points(
        City.first.coordinates, City.last.coordinates)

City.where("coordinates && ?", box.to_geometry)

What's going on?

> box.to_geometry.to_s
=> "POLYGON ((-90.0674 29.9627, -79.09529 29.9627, -79.09529 36.18375, -90.0674 36.18375, -90.0674 29.9627))"

> City.where("coordinates && ?", box.to_geometry).to_sql
=> "SELECT \"cities\".* FROM \"cities\" WHERE (coordinates && '0020000003000010e60000000100000005c05684504816f007403df67381d7dbf5c053c6193b3a68b2403df67381d7dbf5c053c6193b3a68b2404217851eb851ecc05684504816f007404217851eb851ecc05684504816f007403df67381d7dbf5')"

Need Your Help

Tips on storing Query values and passing them between activities?

java android json

I trying to store a list of songs that I query from Android Media Store but I am not sure how to save multiple columns (i.e. song name, track path, duration, etc..)

Editing ObservableCollection's SelectedItem Without Bound Controls Seeing Edits Until They Are Saved

c# wcf data-binding collections

I have a view containing a ListView and an "Edit" Button. The ListView's ItemSource is bound to an ObservableCollection&lt;Account&gt; property on the underlying view model. Its SelectedItem proper...