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 <trang.ngu...@inrix.com>
To: "postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org>
Sent: Friday, February 20, 2015 10:57 AM
Subject: [postgis-users] ST_intersects query that crosses date line boundaries
<!--#yiv5574049598 _filtered #yiv5574049598 {font-family:Calibri;panose-1:2 15
5 2 2 2 4 3 2 4;}#yiv5574049598 #yiv5574049598 p.yiv5574049598MsoNormal,
#yiv5574049598 li.yiv5574049598MsoNormal, #yiv5574049598
div.yiv5574049598MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
"sans-serif";}#yiv5574049598 a:link, #yiv5574049598
span.yiv5574049598MsoHyperlink
{color:blue;text-decoration:underline;}#yiv5574049598 a:visited, #yiv5574049598
span.yiv5574049598MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv5574049598
span.yiv5574049598EmailStyle17 {font-family:"Calibri",
"sans-serif";color:windowtext;}#yiv5574049598 .yiv5574049598MsoChpDefault
{font-family:"Calibri", "sans-serif";} _filtered #yiv5574049598 {margin:1.0in
1.0in 1.0in 1.0in;}#yiv5574049598 div.yiv5574049598WordSection1 {}-->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
