[postgis-users] ST_intersects query that crosses date line boundaries

[Mike Toews]

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.Nguyen at 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