MySQL: get record within polygon

Question

I have a table in database with following with latitude and longitude attribute

CREATE TABLE `companies` (
  `ID` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `latitude` decimal(12,8) NOT NULL,
  `longitude` decimal(12,8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `companies` (`ID`, `title`, `latitude`, `longitude`) VALUES 
(NULL, 'John and SOns', '25.29064622', '55.368462502'), 
(NULL, 'Evas', '25.28862850', '55.40107816');

On google map I have a polyline feature, where user can draw a polyline, so we can show all companies which are within that polygon.

Here is map image

Google may giving an array of latitude longitude

How I can query to fetch all records from database which lies within draw polygon?

Provided you're happy to work with (a projection into) Euclidean geometry, then from MySQL v5.7.5 [`ST_Contains()`](https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-contains) will do exactly what you want; earlier versions of MySQL can only test against the *minimum bounding rectangle* of the specified polygon, using [`MBRContains()`](https://dev.mysql.com/doc/en/spatial-relation-functions-mysql-specific.html#function_mbrcontains). — eggyal, Jul 18 '16 at 06:06
I am using MySQL v5.6.28, As google map bounds.getCenter() function give polygon center latitude and longitude, so is there any way we can get polygon radius so can query by distance in database. — Naseer Panhwer, Jul 18 '16 at 06:22
If you're happy with that sort of approximation, what's wrong with `MBRContains()`? — eggyal, Jul 18 '16 at 08:00
For MBRContains() compare should I store lat long in same column? — Naseer Panhwer, Jul 18 '16 at 08:38
It'd be best to store both Lat and Long in a single `POINT` type column, since then you can build spatial R-Tree indexes upon it (which greatly assist in performing bounded search across 2 dimensions, as you want here). — eggyal, Jul 18 '16 at 08:47

score 0 · Answer 1 · answered Feb 27 '17 at 14:04

You should find that if you write your query as:

select ID,title from companies where st_within(point(companies.longitude,companies.latitude),  ST_GeomFromText('Polygon((75.80278873443604 26.863847231536703, 75.79540729522705 26.85136594544373, 75.81798076629639 26.851595674802315,75.80278873443604 26.863847231536703))'));

then is should work.

MySQL: get record within polygon

1 Answers1