<div dir="ltr">
Dear PostGIS users,<br><br>We are using PostGIS as an engine to retrieve data in GeoJSON format.<br>As the data is worldwide and requests concern large geographic areas we did a choice of using "geography" data type.<br><br>The main functionality of our system is retrieving data in some "places" (points, lines, areas), including buffers. <br>Thus, after several tries, we decided to use ST_DWithin function.<br><br>Recently, during the tests, we found an issue with that function.<br>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>around the 0,0 point (the prime meridian and equator cross-section).<br><br>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><br>CREATE MATERIALIZED VIEW tmp_points_1x1 AS (<br>SELECT row_number() over() AS eid, ST_Translate(point, j, i)::geography AS geog<br> FROM <br> generate_series(-89, 89) AS i,<br> generate_series(-180, 179) AS j,<br> (SELECT ('POINT(0 0)')::geometry AS point) AS b )<br><br>adding these indexes will speedup test queries:<br>CREATE INDEX tmp_points_1x1_geog<br> ON tmp_points_1x1 USING gist(geog)<br> TABLESPACE pg_default;<br><br>CREATE INDEX tmp_points_1x1_eid<br> ON tmp_points_1x1 USING btree(eid)<br> TABLESPACE pg_default;<br> <br>The below test query returns in Json format data for requested geojson area (here it's 190 degrees wide):<br>SELECT row_to_json(fc)<br>FROM (<br> SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features<br> FROM (<br> SELECT 'Feature' AS type, <br> ST_AsGeoJSON(lg.geog)::json AS geometry, <br> row_to_json(lp) AS properties<br> FROM tmp_points_1x1 AS lg <br> INNER JOIN (SELECT eid <br> FROM tmp_points_1x1 <br> 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> ) AS lp <br> ON lg.eid = lp.eid <br> ) AS f <br> ) AS fc;<br><br>On the visualization of the output you can see the missing data:<div><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%"><img style="vertical-align: bottom; border: none;" src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png"> <span dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom">above180wide.png</span></a></div></div><br>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>the problem of missing data doesn't appear any more:<br><div><br><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%"><img style="vertical-align: bottom; border: none;" src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png"> <span dir="ltr" style="color:#15c;text-decoration:none;vertical-align:bottom">below180wide.png</span></a></div></div>Is it a known issue?<br>Are there chances for that to be resolved?<br>Or maybe it's not a bug (e.g. request geometries' BBOXes should not exceed 180 degrees?)<br><br>Thank you for any explanation,<br>Eliasz Haas
</div>