Ruby Rails Postgis - Finding all the points in a polygon

Question

I would like some help on constructing sql queries for use in rails with activerecord-postgis-adapter. I have been doing quite a bit of reading but am now a bit stuck, any help would be much appreciated.

I have the two models Events and Areas:

Events have a 'geometry' column which is of type Point

class Event < ActiveRecord::Base
  self.rgeo_factory_generator = RGeo::Geos.factory_generator    
end

t.spatial  "geometry", :limit => {:srid=>4326, :type=>"polygon", :geographic=>true}

Areas have a 'geometry' column which is of type Polygon

class Area < ActiveRecord::Base 
  self.rgeo_factory_generator = RGeo::Geos.factory_generator
end

t.spatial  "geometry", :limit => {:srid=>4326, :type=>"point", :geographic=>true}

I can create and plot both events and areas on a google map, and create areas by clicking on a map and saving to the database.

I want to be able to do the follow 2 queries:

1. @area.events - show all the events in an area
2. @event.areas - show all the areas a single event is in

I know i might be asking a bit much here, but any help would be much appreciated

Many thanks

Answer 1

Here's a quick way to do this. These will simply return arrays of ActiveRecord objects.

class Area
  def events
    Event.joins("INNER JOIN areas ON areas.id=#{id} AND st_contains(areas.geometry, events.geometry)").all
  end
end

class Event
  def areas
    Area.joins("INNER JOIN events ON events.id=#{id} AND st_contains(areas.geometry, events.geometry)").all
  end
end

You probably should memoize (cache the result) so that you don't query the database every time you call the method. That should be straightforward; I leave it as an exercise for the reader.

It may be possible to get sophisticated and wrap this up in a true Rails association proxy (so you can get all the Rails association goodies). I haven't looked into this though. It wouldn't be a standard Rails association in any case, because you're not storing IDs.

Twelfth is right: you should create spatial indexes for both tables. Activerecord-postgis-adapter should make those easy to do in your migration.

change_table :events do |t|
  t.index :geometry, :spatial => true
end

change_table :areas do |t|
  t.index :geometry, :spatial => true
end

If you're having trouble with installing postgis, I recently wrote up a bunch of blog entries on this stuff. Check out http://www.daniel-azuma.com/blog/archives/category/tech/georails. I'm also the author of rgeo and activerecord-postgis-adapter themselves, so I'm happy to help if you're stuck on stuff.

Answer 2

This answer will be a bit of a work in progress for you. I'm weak with ruby on rails, but I should be able to help you through the DB section.

You have two tables, Area which holds a polygon and Event which holds the event as a single point (it's a bit more complicated if the event is also an area and you're trying to pick out overlapping area's...if events are single points this works).

Select *
from area a inner join event e on 1=1

This is going to create a list of every area joined to every event...if you have 500 events and 20 area's, this will query will return 10'000 lines. Now you want to filter this so only events that are within the area they've been joined to. We can use st_contains for this as st_contains(polygon,point):

where st_contains(a.polygon,e.point) = 't'

If you run this, it should give you a.,e. for all events within area's. Now it's just a matter of counting what you want to count.

select a.id, count(1)
from area a inner join event e on 1=1
where st_contains(a.polygon,e.point) = 't'
group by 1

This will give you a list of all your area's (by id) and the count of the events in it. Switching out a.id with e.id will give a list of event id's and the number area's they are in.

Unfortunately I have no idea how to express these queries within Ruby, but the DB concepts that you'll need are here...

For speed considerations, you should look into the GIStree indexing that Postgres has...indexed polygons perform exponentially better.

Edit:

PostGIS is a contrib file that comes with Postgres but does not exist in a standard install...you'll need to find this contrib file. These will install a series of GIS functions within your database including ST_Contains. (functions reside in a database, so make sure you install the functions in the DB you are using)

The second thing the PostGIS contrib files installs is the template_postGIS database which is required for the geometry datatypes (geom as a data type won't exist until this is installed).