SQL - Geography calculations

Question

In SQL, I have geographies stored. I want to check if geography is covering more than half globe, if geography crosses the international dateline or if geography crosses the equator.

I have come up with the below logic, now sure if it is correct.

Geography covering more than half the globe - Is the logic seems the same as point #3
Geography crossing international date line - If the coordinates of the international date line fall within the given coordinates then it seems to cross the international date line
Geography crossing equator - If any of latitude from the given coordinates is <= 0 then it seems crossing the equator

Please correct there is any better logic.

You're saying a mile-round circle centred at 0° 0° covers more than half the globe? Seems to me like you cannot use the logic for 3 to answer 1. — Damien_The_Unbeliever, Oct 06 '21 at 14:13

score 0 · Answer 1 · answered Nov 25 '21 at 22:36

Not sure what SQL database you use, but most have spatial functions. The syntax and lat/lon order might vary between databases thought.

Larger than hemisphere:

ST_Area(geo) > Earth-surface-area / 2

Crosses antimeridian (date line is slightly different though)

ST_Intersects(geo, ST_GeographyFromText('Linestring(180 -90, 180 0, 180 0)’))

Crosses equator:

ST_Intersects(geo, ST_GeographyFromText('Linestring(-180 0, -60 0, 60 0, 180 0’))

SQL - Geography calculations

1 Answers1