Odtnhj

I'm having a question regarding spatial functions with MySQL 8.0.13 server.
I try to determine if a polygon contains a point, using ST_Contains function.
In my tests, both spherical objects (polygon and point) are defined with the SGID 4326 (WGS84 projection).

• 
  

  Northeastern quarter of the earth polygon:

  
  
  

  SELECT (ST_Contains(
  ST_GeomFromText( 'POLYGON((0 0,0 180,90 180,90 0,0 0))',4326),
  ST_GeomFromText( 'POINT(10 10)',4326 )
  ))

  
  

... returns 1: OK, the point is contained by the polygon.

• 
  

  Polygon in France croosing the Greenwich meridian

  
  
  

  SELECT (ST_Contains(
  ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
  ST_GeomFromText( 'POINT(45 2)',4326 )
  ))

  
  

... returns 1: OK, the point is contained by the polygon.

SELECT (ST_Contains(
 ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
 ST_GeomFromText( 'POINT(45 -2)',4326 )
))

... returns 1: OK, the point is contained by the polygon.

• 
  

  Polygon in Pacific Ocean crossing the International Date Line:

  
  
  

  SELECT (ST_Contains(
  ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
  ST_GeomFromText( 'POINT(0 179)',4326 )
  ))

  
  

... returns 1: OK, the point is contained by the polygon.

SELECT (ST_Contains(
 ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
 ST_GeomFromText( 'POINT(0 -179)',4326 )
))

... returns 1: OK, the point is contained by the polygon.

• 
  

  World clockwise: SW,SE,NE,NW,SW

  
  
  

  SELECT (ST_Contains(
  ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',4326),
  ST_GeomFromText( 'POINT(0 0)',4326 )
  ))

  
  

... returns 0. KO, the point is not detected as part of the polygon.

SELECT (ST_Contains(
 ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',0),
 ST_GeomFromText( 'POINT(0 0)',0)
))

... returns 1: OK, the point is contained by the polygon.

SELECT (ST_Contains(
 ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',0),
 ST_GeomFromText( 'POINT(0 0)',0)
))

... returns 1: OK, the point is contained by the polygon.

I think POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180)) is not relevant using spatial WGS84 coordinates; all points used into this polygon are north|south poles!
When using a flat projection (SGID 0), it works well.
I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too.

My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes) so I can use a generic SQL query that works on every cases.

Christophe