<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Hi Darafei,
</div>
<div>
<br>
</div>
<div>
yes, the MakeBox example was not a good one. Even though the basic question -- which rule determines inside and outside of a large geography polygon -- remains the same.
<br>
</div>
<div>
<br>
</div>
<div>
But the problem is a different one:
<br>
</div>
<div>
SELECT ST_Distance(ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452,
<div>
130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
</div>
<div>
172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
</div>-36.5625 40.9798980696201))')::geography , ST_Point(0, 0)) / 1000
</div>
<div>
return 0 in PostGis 2.5.2. (as expected)
<br>
</div>
<div>
<br>
</div>
<div>
However: ST_Intersect or ST_DWithin on the same geometries return false. Distance 0 and Intersects=false does not make sense.
<br>
</div>
<div>
The culprit is the bounding-box check in the latter two function ( OPERATOR(public.&&) ).
<br>
</div>
<div>
<br>
</div>
<div>
Test Case:
<br>
</div>
<div>
Create a sample dataset with one point per 1x1 degree:
<br>
</div>
<div>
CREATE MATERIALIZED VIEW public.tmp_points_1x1 AS (
<br>SELECT row_number() over() AS eid, ST_Translate(point, j, i) AS geom
<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 )
</div>
<div>
<br>
</div>
<div>
Now select from this dataset:
<br>
</div>
<div>
SELECT eid,ST_SetSRID(geom, 4326) FROM tmp_points_1x1
<br>WHERE ST_Distance(geom::geography, ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452,
<br>130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
<br>172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
<br>-36.5625 40.9798980696201))')::geography ) <= 0.0
</div>
<div>
<br>
</div>
<div>
returns the expected result:
<br>
</div>
<div>
<img src="cid:b77ea88d4a8040e2acba71977eef0c3c@Open-Xchange" style="max-width: 100%;" class="aspect-ratio" alt="" data-pending="true" data-id="upload-image-7555" from_clipboard="true">
<br>
</div>
<div>
<br>
</div>
<div>
ST_Intersects leaves a hole around the point 0, 0!
<br>
</div>
<div>
SELECT eid,ST_SetSRID(geom::geography, 4326) FROM tmp_points_1x1
<br>WHERE ST_Intersects(geom::geography, ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452,
<br>130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
<br>172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
<br>-36.5625 40.9798980696201))')::geography )
</div>
<div>
<img src="cid:f4d33ea06e63493bbaed4f5c326815e9@Open-Xchange" style="max-width: 100%;" class="aspect-ratio" alt="" data-pending="true" data-id="upload-image-7661" from_clipboard="true">
<br>
</div>
<div>
<br>
</div>
<div>
&& leaves 2 holes and select a lot of additional points outside of the expected bounding box.
<br>
</div>
<div>
SELECT eid,ST_SetSRID(geom, 4326) FROM tmp_points_1x1
<br>WHERE geom::geography OPERATOR(public.&&) ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452,
<br>130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
<br>172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
<br>-36.5625 40.9798980696201))')::geography
</div>
<div>
<img src="cid:aff01ee403f64389986eb83bd95998a5@Open-Xchange" style="max-width: 100%;" class="aspect-ratio" alt="" data-pending="true" data-id="upload-image-8259" from_clipboard="true">
<br>
</div>
<div>
<br>
</div>
<div>
Greetings
<br>
</div>
<div>
Christian
<br>
</div>
<div>
<br>
</div>
<blockquote type="cite">
<div>
On 09 August 2019 at 14:56 "Darafei \"Komяpa\" Praliaskouski" <
<a href="mailto:me@komzpa.net">me@komzpa.net</a>> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
Hi Christian,
</div>
<div>
<br>
</div>
<div>
To understand the query please try plotting your polygon on a globe and
</div>
<div>
then imagine axis aligned box that will contain it (one side parallel to
</div>
<div>
equator, other parallel to 0 and third to 90th meridian). You will see that
</div>
<div>
your "large" geometry is really 40 degrees wide spot around antimeridian.
</div>
<div>
To visualize it better using common planar GIS tools, feed it into
</div>
<div>
geography ST_Segmentize - it will produce the actual 'curved' geometry used
</div>
<div>
in calculation. To see closer to behavior you expect, make sure there are
</div>
<div>
no points connected by longer, not shorter, part of Great Circle, as in
</div>
<div>
your example.
</div>
<div>
<br>
</div>
<div>
Hope this helps.
</div>
<div>
<br>
</div>
<div>
On Thu, Aug 8, 2019 at 2:46 AM Christian Pschierer <
</div>
<div>
<a href="mailto:christian.pschierer@gmx.net">christian.pschierer@gmx.net</a>> wrote:
</div>
<div>
<br>
</div>
<blockquote type="cite">
<div>
Hi,
</div>
<div>
<br>
</div>
<div>
we found some unexpected results when doing spatial queries on very
</div>
<div>
large geography polygons. For example
</div>
<div>
<br>
</div>
<div>
SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(160,
</div>
<div>
60),ST_Point(-160,-60)), 4326)::geography,ST_SetSRID(ST_Point(0, 0),
</div>
<div>
4326)::geography)/1000
</div>
<div>
<br>
</div>
<div>
returns 13130km instead of 0 as the point 0,0 should be inside this polygon.
</div>
<div>
<br>
</div>
<div>
Queries on smaller search areas, or geometry queries return 0.
</div>
<div>
<br>
</div>
<div>
SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(60,
</div>
<div>
60),ST_Point(-60,-60)), 4326)::geography,ST_SetSRID(ST_Point(0,
</div>
<div>
0),4326)::geography)/1000
</div>
<div>
<br>
</div>
<div>
SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(160,
</div>
<div>
60),ST_Point(-160,-60)), 4326),ST_SetSRID(ST_Point(0, 0), 4326))/1000
</div>
<div>
<br>
</div>
<div>
It seems like PostGis switches inside/outside on geographies if they
</div>
<div>
exceed a certain size. Is this correct? Is there a way to control this
</div>
<div>
behaviour?
</div>
<div>
<br>
</div>
<div>
Greetings
</div>
<div>
Christian
</div>
<div>
<br>
</div>
<div>
_______________________________________________
</div>
<div>
postgis-users mailing list
</div>
<div>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
</div>
<div>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noopener" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
<br>
</div>
</blockquote>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
--
</div>
<div>
Darafei Praliaskouski
</div>
<div>
Support me:
<a href="http://patreon.com/komzpa" rel="noopener" target="_blank">http://patreon.com/komzpa</a>
<br>
</div>
<div>
_______________________________________________
</div>
<div>
postgis-users mailing list
</div>
<div>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<br>
</div>
<div>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noopener" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
<br>
</div>
</blockquote>
</body>
</html>