[postgis-users] Deleting Geometries Above and Below 60 Degrees Latitude

[pcreso at pcreso.com]

Hi Dylan,

That approach works for me on a longitudinal basis, so it should work OK on a latitudinal one for you.


Try:

select <attr>,
       <attr>,
       ...   ,
       intersect(the_geom, SetSRID('BOX3D(-180 60,180 -60)'::box3d,4326)
                 as the_geom
       from <table>;


Note, this assumes your data is using SRID 4326, and a longitude range from +-180, not 0-360.


The output could be used to create a new table, or perhaps a view if the original data is still of value (extra where clause needed though to exclude null geometries?). You could also just modify the data in the existing table by deleting all records where the geometry not intersect(....), then updating the geometries to the intersect(...)


HTH,

  Brent Wood



--- On Sat, 6/14/08, Dylan Lorimer <edylan at google.com> wrote:

> From: Dylan Lorimer <edylan at google.com>
> Subject: [postgis-users] Deleting Geometries Above and Below 60 Degrees Latitude
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Saturday, June 14, 2008, 9:22 AM
> Hi,
> 
> Have a question I'm hoping for some assistance with. I
> have a table that
> contains polygons that are of various shapes and sizes and
> dispersed all
> over the world. Some of them exist above or below 60
> degrees north/south
> latitude. Others cross over the 60 degree north and south
> latitude line.
> 
> What I would like to do is delete from my table all
> geometries falling north
> or south of the 60 degree latitude line AND where a
> geometry crosses the
> line cut it along the line so that only the part of the
> geometry lying
> within the 60 north/south region is left.
> 
> I'm a bit lost on how to do this. One thought I had was
> to construct a box
> representing the area that I want to keep and then somehow
> removing
> everything that doesn't intersect it. Is that the right
> approach? Any other
> easier way to do this?
> 
> Cheers,
> dylan_______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users