MySQL: Spatial Query to find whether a latitude/longitude point is located within a given boundary

Question

I'm working on google map search functionality. The vision of this is to find whether a (geolocation) point is located within a polygon or not as shown in the illustration below?

I'm using mysql 5.6.20 with Spatial extension, I know it has useful geometry functions built-in, so I will be allowed to query geocoded locations from a database directly.

My aim was to familiarize myself with geospatial functions, so I wrote up an experimental sql.

given a point geolocation: POINT(100.52438735961914 13.748889613522605)

and a polygon or boundary to be tested with was: POLYGON(100.49503326416016 13.766897133254545,100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617,100.48885345458984 13.739051587150175)

here is my sql example:

SELECT ST_Within(
ST_GEOMFROMTEXT('POINT(100.52438735961914 13.748889613522605)'),
ST_GEOMFROMTEXT('POLYGON(100.49503326416016 13.766897133254545,
100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617,
100.48885345458984 13.739051587150175)'))
As geoFenceStatus

but after issuing the command, what I got in return seemed to be as follows:

geoFenceStatus
===============
null

I'm so unsure why it returned me 'null' value. since it was indicated in function documentation that this should return '1' in case a point resides within a given polygon

any advice would be appreciated, how to get my sql right.

Answer 1

The error message isn't very clear but your issue is that you have an invalid Polygon. There are two problems with it:

1. You have to repeat the first point at the end -- this is to differentiate it from a LINESTRING, essentially, ie, it is closed.

2. POLYGONS start with and end with double parenthesis, ie, POLYGON((x1 y1, x2 y2.......xn yn, x1 y1)). This is to allow for inner rings to be delineated with single parenthesis sets inside the polygon.

See the wikipedia WKT article for more information.

You should find that if you write you query as:

SELECT ST_Within(ST_GEOMFROMTEXT('POINT(100.52438735961914 13.748889613522605)'),
             ST_GEOMFROMTEXT('POLYGON((100.49503326416016 13.766897133254545, 100.55940628051758 13.746555203977,100.56266784667969 13.72170897580617, 100.48885345458984 13.739051587150175,
             100.49503326416016 13.766897133254545))'))
As geoFenceStatus

then is should work.