<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px"><div id="yui_3_16_0_1_1423459746958_2878673">Hi,</div><div id="yui_3_16_0_1_1423459746958_2878608"><br></div><div id="yui_3_16_0_1_1423459746958_2878623" dir="ltr">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. <br></div><div id="yui_3_16_0_1_1423459746958_2878706" dir="ltr"><br></div><div id="yui_3_16_0_1_1423459746958_2878625" dir="ltr">You could:</div><div id="yui_3_16_0_1_1423459746958_2878763" dir="ltr">1. cast the geometry to a geography for the query,</div><div id="yui_3_16_0_1_1423459746958_2878762" dir="ltr">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)<br></div><div id="yui_3_16_0_1_1423459746958_2878627" dir="ltr">3. try hard coding 0-360 longitudes in your query: <br></div><div id="yui_3_16_0_1_1423459746958_2878810" dir="ltr">ST_GeomFromText('MULTIPOLYGON(((179.64844
67.73477,204.96094 67.60118,198.80859 61.8462,179.64844 67.73477)))')</div><div id="yui_3_16_0_1_1423459746958_2878760" dir="ltr"><br></div><div id="yui_3_16_0_1_1423459746958_2878894" dir="ltr">Note that points in your table will also need shifting to a 0-360 space in the query for 2 & 3.<br></div><div id="yui_3_16_0_1_1423459746958_2878896" dir="ltr"><br></div><div id="yui_3_16_0_1_1423459746958_2878806" dir="ltr">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.<br></div><br><div dir="ltr" id="yui_3_16_0_1_1423459746958_2878607"><span>HTH,</span></div><div id="yui_3_16_0_1_1423459746958_2878898" dir="ltr"><br><span></span></div><div id="yui_3_16_0_1_1423459746958_2878900" dir="ltr"><span>Brent Wood</span></div><br> <div id="yui_3_16_0_1_1423459746958_2878634" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 13px;"> <div id="yui_3_16_0_1_1423459746958_2878633" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1423459746958_2878632" dir="ltr"> <hr id="yui_3_16_0_1_1423459746958_2878722" size="1"> <font id="yui_3_16_0_1_1423459746958_2878631" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Trang Nguyen <Trang.Nguyen@inrix.com><br> <b><span style="font-weight: bold;">To:</span></b> "postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Friday, February 20, 2015 10:57 AM<br> <b><span style="font-weight: bold;">Subject:</span></b> [postgis-users] ST_intersects query that crosses date line boundaries<br> </font> </div> <div id="yui_3_16_0_1_1423459746958_2878639" class="y_msg_container"><br><div id="yiv5574049598">
<style><!--
#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
{}
--></style>
<div id="yui_3_16_0_1_1423459746958_2878638">
<div id="yui_3_16_0_1_1423459746958_2878637" class="yiv5574049598WordSection1">
<div id="yui_3_16_0_1_1423459746958_2878636" class="yiv5574049598MsoNormal">Hi,</div>
<div id="yui_3_16_0_1_1423459746958_2878641" class="yiv5574049598MsoNormal"><br>
I am using Postgres 9.3 and have a table with geometry columns:</div>
<div id="yui_3_16_0_1_1423459746958_2878643" class="yiv5574049598MsoNormal"> startloc geometry(Point),</div>
<div id="yui_3_16_0_1_1423459746958_2878645" class="yiv5574049598MsoNormal"> endloc geometry(Point),</div>
<div id="yui_3_16_0_1_1423459746958_2878647" class="yiv5574049598MsoNormal"> </div>
<div id="yui_3_16_0_1_1423459746958_2878651" class="yiv5574049598MsoNormal">When I run a query that crosses the date line boundary, I’m getting incorrect results. Example:</div>
<div id="yui_3_16_0_1_1423459746958_2878649" class="yiv5574049598MsoNormal">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)))')))</div>
<div id="yui_3_16_0_1_1423459746958_2878653" class="yiv5574049598MsoNormal"> </div>
<div class="yiv5574049598MsoNormal">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?</div>
<div class="yiv5574049598MsoNormal"> </div>
<div class="yiv5574049598MsoNormal">Thanks,<br>
Trang</div>
<div class="yiv5574049598MsoNormal"> </div>
</div>
</div>
</div><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@lists.osgeo.org" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br><br></div> </div> </div> </div></body></html>