CODEWITHSUNDEEP
mysqlgeolocationgeospatial
quick-brown-fox
quick-brown-fox

Reputation: 71

MySQL query claims point is not within polygon

I've drawn a polygon which contains a point in Google Maps. But if I pass the coordinates to MySQL to calculate if the point is within the polygon, it returns false.

SELECT ST_Within(
    ST_GeomFromText('POINT(8.34047 54.91320)', 4326),
    ST_GeomFromText('POLYGON((62.144619879597 10.486242310988,54.622536815923 2.3124141859883,55.403637023919 23.977453248488,62.144619879597 10.486242310988))', 4326)
) AS is_point_within_polygon;

=> returns 0

But the point is obviously within the polygon:

enter image description here

I double-checked that using Python:

import numpy as np
from shapely.geometry import Point
from shapely.geometry.polygon import Polygon

if __name__ == '__main__':
    v0 = [62.144619879597, 10.486242310988]
    v1 = [54.622536815923, 2.3124141859883]
    v2 = [55.403637023919, 23.977453248488]
    lats_vect = np.array([v0[0], v1[0], v2[0]])
    lons_vect = np.array([v0[1], v1[1], v2[1]])

    lats_vect = np.append(lats_vect, lats_vect[0])
    lons_vect = np.append(lons_vect, lons_vect[0])

    lons_lats_vect = np.column_stack((lons_vect, lats_vect))
    polygon = Polygon(lons_lats_vect)
    point = Point(8.34047, 54.9132)
    print(point.within(polygon))

=> prints True

What's wrong with the MySQL query?

Upvotes: 0

Views: 149

Answers (1)

Michael Entin
Michael Entin

Reputation: 7724

I think there are two issues here:

  1. First with the query. You list polygon in lat-lon order, but the point seems to be in lon-lat order. You probably want
SELECT ST_Within(
    ST_GeomFromText('POINT(54.91320 8.34047)', 4326),  -- NOTE CHANGE HERE
    ST_GeomFromText('POLYGON((62.144619879597 10.486242310988,54.622536815923 2.3124141859883,55.403637023919 23.977453248488,62.144619879597 10.486242310988))', 4326)
) AS is_point_within_polygon;
  1. Even this query returns FALSE, and this is expected in MySQL. 4326 is Geodesic coordinate system, meaning it operates on the spherical Earth, not on this flat map. With geodesic CRS, edges follow the geodesic shortest lines on Earth, not straight lines on flat map, and for really long lines like here and points close to the edge it matter:

enter image description here

Points slightly further North would be within the polygon, e.g. check out

SELECT ST_Within(
    ST_GeomFromText('POINT(56 8.34047)', 4326),
    ST_GeomFromText('POLYGON((62.144619879597 10.486242310988,54.622536815923 2.3124141859883,55.403637023919 23.977453248488,62.144619879597 10.486242310988))', 4326)
) AS is_point_within_polygon

Upvotes: 1

Related Questions