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

[Trang Nguyen]

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-bounces at lists.osgeo.org [mailto:postgis-users-bounces at 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.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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users