Thanks Mike, Brett. The casting to geography worked for me. I do notice a significant performance degradation with the conversion. I'm hesitant to change the column to generically to geography type due to the performance impacts but we have some use cases we need join the existing table against a second table containing parsed shape zones which could cross the meridian line. Are there any techniques or plans to speed up query performance for geography-based column types?
Trang -----Original Message----- From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Mike Toews Sent: Thursday, February 19, 2015 2:47 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_intersects query that crosses date line boundaries Hi Trang, Geometry types work in Cartesian space, and don't know what happens when Y coordinates cross the antimeridian at -180/+180. Try experimenting with the geography type, which works with some, but not all PostGIS functions. ST_Intersects should work on geography types for your example, e.g.: ... and ST_intersects(startloc::geography, 'MULTIPOLYGON(((179.64844 67.73477,-155.03906 67.60118,-161.19141 61.8462,179.64844 67.73477)))'::geography) You can either adjust your query, or change the type in-place from geometry to geography, if you feel it meets all your needs. -Mike On 20 February 2015 at 10:57, Trang Nguyen <trang.ngu...@inrix.com> wrote: > Hi, > > I am using Postgres 9.3 and have a table with geometry columns: > > startloc geometry(Point), > endloc geometry(Point), > > When I run a query that crosses the date line boundary, I'm getting > incorrect results. Example: > > SELECT * from od1.trip_v1_partitioned where startts>=TIMESTAMP > '2015-02-16T20:00:00.000Z'and startts<TIMESTAMP > '2015-02-17T20:00:00.000Z'and endts<TIMESTAMP > '2015-02-17T20:00:00.000Z' and ST_intersects(startloc, > ST_MakeValid(ST_GeomFromText('MULTIPOLYGON(((179.64844 > 67.73477,-155.03906 > 67.60118,-161.19141 61.8462,179.64844 67.73477)))'))) > > Would I need to change how my columns are stored (this would require a > big migration), or is it possible to adjust my query to handle this > correctly? > > Thanks, > Trang _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
