StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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).</p> <pre><code>Select * from area a inner join event e on 1=1 </code></pre> <p>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):</p> <pre><code>where st_contains(a.polygon,e.point) = 't' </code></pre> <p>If you run this, it should give you a.<em>,e.</em> for all events within area's. Now it's just a matter of counting what you want to count.</p> <pre><code>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 </code></pre> <p>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.</p> <p>Unfortunately I have no idea how to express these queries within Ruby, but the DB concepts that you'll need are here...</p> <p>For speed considerations, you should look into the GIStree indexing that Postgres has...indexed polygons perform exponentially better.</p> <p>Edit:</p> <p>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)</p> <p>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).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload