Ruby Rails Postgis - Find all points in a polygon.

Question

I need help building sql queries for use on rails with an activerecord-postgis adapter. I read a little, but now I'm a little stuck, any help would be greatly appreciated.

I have two models: Events and areas:

Events have a geometry column that 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 that 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 display both events and areas on a Google map, and create areas by clicking on the map and saving to the database.

I want to be able to fulfill the following 2 queries:

• @ area.events - show all events in the area
• @ event.areas - show all areas in which one event is located

I know that maybe I would ask a little here, but any help would be greatly appreciated

Many thanks

Answer 1

This answer will be a little incomplete for you. I am weak with rubies on rails, but I have to help you in the DB section.

You have two tables: Area, which contains the polygon, and an event, which contains the event as one point (this is a bit more complicated if the event is also an area, and you are trying to select an overlapping area ... if the events are single points, this works) .

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

This will create a list of all areas attached to each event ... if you have 500 events and 20 areas, the query will return 10'000 rows. Now you want to filter this out only for events that are in the area to which they were attached. 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., E. for all events within the area. 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 your entire area (by id) and the number of events in it. Turning off a.id with e.id will give a list of event IDs and the numbering area in which they are located.

Unfortunately, I have no idea how to express these queries in Ruby, but the database concepts you will need are here ...

For speed reasons, you should study 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 installation ... you need to find this Contrib file. They will install a number of GIS functions in your database, including ST_Contains. (the functions are in the database, so make sure you install the functions in the database you are using)

The second thing that files to create PostGIS install is the template_postGIS database, which is required for geometry data types (geometry as a data type will not exist until it is installed).

Answer 2

Here is a quick way to do it. They 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 should probably memoize (cache the result) so that you do not query the database every time you call the method. It should be simple; I leave this as an exercise for the reader.

You may be able to refine and wrap this in a true Rails proxy connection (so that you can get all the useful Rails properties). I did not look at that, though. In any case, this will not be the standard Rails association, because you do not store identifiers.

Twelfth Correct: You must create spatial indexes for both tables. The Activerecord-postgis adapter should make this easy during the migration process.

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

If you are having trouble installing postgis, I recently wrote a bunch of blog posts on this material. Check out http://www.daniel-azuma.com/blog/archives/category/tech/georails . I am also the author of rgeo and activerecord-postgis-adapter, so I am happy to help if you are stuck on things.