[postgis-users] How to return latest results from intersecting polygons?

[David Waddy]

Please bare with my explanation below:

--------------------------------
|       |         |             |
|  1   |    2    |             |      <--- 4
|       |         |             |
--------------------------------

Look at the ascii diagram we assume #4 is the large area of the earth
described by lat/lon points. #1 and #2 are subsets contained within it.
Note that there is no #3.

If I have a table of source data in the following form (just an example):

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))
...

What I want to do is pick out the latest event for any paticular area of
lat/lon points.
For example if we assume each number in the ascii daigram is a set of
points and the event time is a year we will have:
2007 4
2002 1
2003 2

Thus to return the latest points for this data it would simply be #4's
lat_lon points.

If it were this instead:
2007 1
2002 4
2003 2

We would return #1,#2, and the part of #4 that is still visible (doesn't
intersect #1 and #2). We would thus be generating a new set of points for
#4. I believe this is the trickiest part.

Any help would be greatly appreciated,
Dave
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120115/666a069d/attachment.html>