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 <[email protected]> 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 [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
