<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">You may find that this problem is recently addressed (how does this bug exist for 10 years and get reported twice in two weeks?) in <a href="https://trac.osgeo.org/postgis/ticket/4480" class="">https://trac.osgeo.org/postgis/ticket/4480</a> <div class=""><br class=""></div><div class="">While in general the rule “things that are larger than a hemisphere are going to be problematic” does hold, the reported case was in fact addressable and fixable, if not in general then at least for most common cases.</div><div class=""><br class=""></div><div class="">In general:</div><div class=""><br class=""></div><div class="">- try to break your large things into equivalent sets of smaller things</div><div class="">- remember that no edge will span more than 180 degrees, as we do interpret an edge as the shortest distance between a coordinate pair</div><div class=""><br class=""></div><div class="">ATB,</div><div class=""><br class=""></div><div class="">P</div><div class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Aug 13, 2019, at 9:55 AM, Szymon Haas <<a href="mailto:haasel77@gmail.com" class="">haasel77@gmail.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">
Dear PostGIS users,<br class=""><br class="">We are using PostGIS as an engine to retrieve data in GeoJSON format.<br class="">As the data is worldwide and requests concern large geographic areas we did a choice of using "geography" data type.<br class=""><br class="">The main functionality of our system is retrieving data in some "places" (points, lines, areas), including buffers. <br class="">Thus, after several tries, we decided to use ST_DWithin function.<br class=""><br class="">Recently, during the tests, we found an issue with that function.<br class="">Briefly
- for very large areas (from the observations BBOX wider than 180
degrees EW) results of the ST_DWithin are missing data in a circular
area <br class="">around the 0,0 point (the prime meridian and equator cross-section).<br class=""><br class="">To
reproduce the issue one can create a table/view with a grid of points
(lines or polygons would have the same effect) around the globe:<br class=""><br class="">CREATE MATERIALIZED VIEW tmp_points_1x1 AS (<br class="">SELECT row_number() over() AS eid, ST_Translate(point, j, i)::geography AS geog<br class=""> FROM <br class=""> generate_series(-89, 89) AS i,<br class=""> generate_series(-180, 179) AS j,<br class=""> (SELECT ('POINT(0 0)')::geometry AS point) AS b )<br class=""><br class="">adding these indexes will speedup test queries:<br class="">CREATE INDEX tmp_points_1x1_geog<br class=""> ON tmp_points_1x1 USING gist(geog)<br class=""> TABLESPACE pg_default;<br class=""><br class="">CREATE INDEX tmp_points_1x1_eid<br class=""> ON tmp_points_1x1 USING btree(eid)<br class=""> TABLESPACE pg_default;<br class=""> <br class="">The below test query returns in Json format data for requested geojson area (here it's 190 degrees wide):<br class="">SELECT row_to_json(fc)<br class="">FROM (<br class=""> SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features<br class=""> FROM (<br class=""> SELECT 'Feature' AS type, <br class=""> ST_AsGeoJSON(lg.geog)::json AS geometry, <br class=""> row_to_json(lp) AS properties<br class=""> FROM tmp_points_1x1 AS lg <br class=""> INNER JOIN (SELECT eid <br class=""> FROM tmp_points_1x1 <br class=""> WHERE st_dwithin(geog, ST_GEOMFROMGEOJSON('{"type":"Polygon","coordinates":[[[-20,-50],[75,-50],[170,-50],[170,50],[75,50],[-20,50],[-20,-50]]]}' ),0)<br class=""> ) AS lp <br class=""> ON lg.eid = lp.eid <br class=""> ) AS f <br class=""> ) AS fc;<br class=""><br class="">On the visualization of the output you can see the missing data:<div class=""><div class="gmail_chip gmail_drive_chip" style="width:396px;height:18px;max-height:18px;background-color:#f5f5f5;padding:5px;color:#222;font-family:arial;font-style:normal;font-weight:bold;font-size:13px;border:1px solid #ddd;line-height:1"><a href="https://drive.google.com/file/d/1wgkpLi4xyCviN3AzE8hAnRZUFVUbH3ST/view?usp=drive_web" target="_blank" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:medium none;width:100%" class=""><img style="vertical-align: bottom; border: none;" src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" class=""> <span dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom" class="">above180wide.png</span></a></div></div><br class="">If the request area is narrowed to <180 degrees wide, e.g. 170 degrees: {"type":"Polygon","coordinates":[[[-20,-50],[20,-50],[20,50],[-20,50],[-20,-50]]]}<br class="">the problem of missing data doesn't appear any more:<br class=""><div class=""><br class=""><div class="gmail_chip gmail_drive_chip" style="width:396px;height:18px;max-height:18px;background-color:#f5f5f5;padding:5px;color:#222;font-family:arial;font-style:normal;font-weight:bold;font-size:13px;border:1px solid #ddd;line-height:1"><a href="https://drive.google.com/file/d/1FuXOmESE6N4_LWAjqAxg0WbOAjwJLeEe/view?usp=drive_web" target="_blank" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:medium none;width:100%" class=""><img style="vertical-align: bottom; border: none;" src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" class=""> <span dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom" class="">below180wide.png</span></a></div></div>Is it a known issue?<br class="">Are there chances for that to be resolved?<br class="">Or maybe it's not a bug (e.g. request geometries' BBOXes should not exceed 180 degrees?)<br class=""><br class="">Thank you for any explanation,<br class="">Eliasz Haas
</div>
_______________________________________________<br class="">postgis-users mailing list<br class=""><a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</div></blockquote></div><br class=""></div></body></html>