0

In SQL Server, I want to find if geography intersects/crosses the international dateline and equator.

I thought of the below logic but am not sure if it is the right way of doing this.

  • Geography crossing international date line: if the coordinates of the international date line fall within the given coordinates then it seems to cross the dateline

  • Geography crossing the equator: if any of latitude from the given coordinate is <= 0 then it seems to cross the equator

Any help is highly appreciated.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
Reyan Chougle
  • 4,917
  • 2
  • 30
  • 57

1 Answers1

0

What I'd do is come up with linestrings that represent the Equator and International Dateline (respectively). From there, you can use STIntersects() against your geography data to test for intersection.

Addendum - here are naïve definitions of the equator and International Date Line (IDL). The equator works around what appears to be a technical limitation in SQL Server's geography implementation regarding antipodes while the IDL uses the simple but incorrect definition of "longitude -180°".

DECLARE 
    @e GEOGRAPHY = geography::STMLineFromText('MULTILINESTRING ((-179.9999 0, 0 0), (0 0, 179.9999 0))', 4326),
    @idl GEOGRAPHY = geography::STMLineFromText('MULTILINESTRING ((-180 0, -180 90), (-180 0, -180 -90))', 4326);
Ben Thul
  • 31,080
  • 4
  • 45
  • 68