[postgis-users] Inside / Outside of geography polygons

[Christian Pschierer]

Hi all,

I have some more input on this question.
ST_MakeBox2D seems to have it's own set of problems. In the original example, the distance from 0,0 to the boundary at 0,60 should be around 6700km, not 13100km.
Shrinking the latitudinal extend even increases the effect: SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(160, 1),ST_Point(-160,-1)), 4326)::geography,ST_SetSRID(ST_Point(0, 0),4326)::geography)/1000  ==> 17800km


My core question however was around polygons defined normally using a series of vertices. We found that upgrading from PostGIS 2.4.4 to 2.5.2 fixed some problems, but not all. Here are two more cases.
The first one returned 2576km in PostGIS 2.4.4, and now 0km in 2.5.2. The 2nd (larger polygon) returns 3462km on both versions.

SELECT ST_Distance(ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452,
			130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
			172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
			-36.5625 40.9798980696201))')::geography  , ST_Point(0, 0)) / 1000

SELECT ST_Distance(ST_GeomFromText('POLYGON((-36.5625 70.9798980696201, -22.5 -77.71099165543322,6.328125 -74.0875850282452,
			130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 69.162557908105,
			172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
			-36.5625 70.9798980696201))')::geography  , ST_Point(0, 0)) / 1000



> On 08 August 2019 at 01:46 Christian Pschierer <christian.pschierer at gmx.net> wrote:
>
>
> Hi,
>
> we found some unexpected results when doing spatial queries on very
> large geography polygons. For example
>
>     SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(160,
> 60),ST_Point(-160,-60)), 4326)::geography,ST_SetSRID(ST_Point(0, 0),
> 4326)::geography)/1000
>
> returns 13130km instead of 0 as the point 0,0 should be inside this polygon.
>
> Queries on smaller search areas, or geometry queries return 0.
>
>     SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(60,
> 60),ST_Point(-60,-60)), 4326)::geography,ST_SetSRID(ST_Point(0,
> 0),4326)::geography)/1000
>
>     SELECT ST_Distance(ST_SetSRID( ST_MakeBox2D(ST_Point(160,
> 60),ST_Point(-160,-60)), 4326),ST_SetSRID(ST_Point(0, 0), 4326))/1000
>
> It seems like PostGis switches inside/outside on geographies if they
> exceed a certain size. Is this correct? Is there a way to control this
> behaviour?
>
> Greetings
> Christian
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users