I defined the problem a little better and took your advice and asked in postgis users:
--
David Waddy
Waddy & Colpitts Ltd.
99 Milky Way
Colpitts Settlement, NB
E4J 0B6 Canada
tel: 506-372-4564
http://www.littleriver.ca
Dave
On Wed, Jan 11, 2012 at 11:20 PM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote:
Are you using PostGIS? Assuming yes, try something like:On 01/11/2012 07:14 PM, David Waddy wrote:
If I have a table of the following form:
id (integer) event_time (timestamp) lat_lon (polygon)
3497 1977-01-01 00:00:00
((-64.997,45.975),(,(-64.9981,45.975),(-64.8981,45.875),(-64.9978,45.9751))
3431 2007-06-06 01:00:00
((-64.971,45.982),(-64.921,45.982),(-64.972,45.982),(-64.973,45.98209),(-64.97,45.98237))
3498 1977-01-01 00:00:00
((-64.97838,45.9778),(-64.9783,45.97767),(-64.978,45.977),(-64.9781,45.97728),(-64.9781,45.97714),(-64.977,45.976))
...
How would I return a list of the latest events for a particular
lat/lon region? More precisely, how would a return a result set with
the greatest event times with polygons that don't intersect any other
polygon in the result set?
Any help would be greatly appreciated,
Dave
select * from theTable a cross join theTable b on not (a.lat_lon && b.lat_lon) order by event_time
Also try the postgis news group, there are people there with more experience with the postGIS functions.
Your two questions dont seem to be asking the same thing, though. One asks for a particular region. The second for a region that doesn't intersect with any other's. I went for the second, cross joining the table to itself, so every record with be compared to every other record, which is gonna be a huge number of comparisons. So it'll be slow.
-Andy
David Waddy
Waddy & Colpitts Ltd.
99 Milky Way
Colpitts Settlement, NB
E4J 0B6 Canada
tel: 506-372-4564
http://www.littleriver.ca
