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

[Brent Wood]

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.Nguyen at inrix.com>
 To: "postgis-users at lists.osgeo.org" <postgis-users at 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 at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150220/399d27bd/attachment.html>