[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] ST_intersects query that crosses date line	boundaries
From:       Trang Nguyen <Trang.Nguyen () inrix ! com>
Date:       2015-02-20 22:35:31
Message-ID: 7BA3BA5B1126A649BF2B6E9A6C43B528CEF27A () COREXH10 ! inrix ! corpnet ! local
[Download RAW message or body]

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@lists.osgeo.org \
                [mailto:postgis-users-bounces@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@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


[prev in list] [next in list] [prev in thread] [next in thread]