Re: How to return latest results without a polygon intersect?

From: Andy Colson <andy(at)squeakycode(dot)net>
To: David Waddy <david(at)littleriver(dot)ca>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to return latest results without a polygon intersect?
Date: 2012-01-12 03:20:04
Message-ID: 4F0E5164.7010603@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

Are you using PostGIS? Assuming yes, try something like:

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message debian nick 2012-01-12 05:16:04 Postgresql allow connections without password
Previous Message Edwin Quijada 2012-01-12 03:06:00 Compiling C function with VC++ for Windows version