ST_intersects query that crosses date line boundaries

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

ST_intersects query that crosses date line boundaries

Trang Nguyen

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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_intersects query that crosses date line boundaries

Mike Taves
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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_intersects query that crosses date line boundaries

Brent Wood
In reply to this post by Trang Nguyen
Hi,

There are two ways to draw a line between (-179, 0) & (179 0) - the long & short way around the earth, & Postgis can't tell which is correct.

You  could:
1. cast the geometry to a geography for the query,
2. try ST_ShiftLongitude([geometry]) which will change it to a 0-360 longitude space instead of +-180 (this was written to fix some 180 issues)
3. try hard coding 0-360 longitudes in your query:
ST_GeomFromText('MULTIPOLYGON(((179.64844 67.73477,204.96094 67.60118,198.80859 61.8462,179.64844 67.73477)))')

Note that points in your table will also need shifting to a 0-360 space in the query for 2 & 3.

You should include a ST_SetSRID([geometry],4326) as well so Postgis knows the CRS of the created polygon, & your point columns should also have this set.

HTH,

Brent Wood


From: Trang Nguyen <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Friday, February 20, 2015 10:57 AM
Subject: [postgis-users] ST_intersects query that crosses date line boundaries

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


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_intersects query that crosses date line boundaries

Trang Nguyen
In reply to this post by Mike Taves
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: [hidden email] [mailto:[hidden email]] 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 <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users