A.R.H A.R.H - 25 days ago 13
MySQL Question

MySQL : how to find nearest polygon from a special point in mysql versions above 5.6.1?

I've a table

urbanpolygons
the column which holds polygon is
paths
I have a point which surrounded by polygons and I wanna search the polygons and find which one is closer to my point.

The green box(polygon) is which one should be chosen!

enter image description here

I do this to manage is point inside a polygon or not:

select * FROM urbanpolygons where ST_Contains(urbanpolygons.paths, GeomFromText('POINT(29.632262, 52.497868)'));


THIS QUESTION IS NOT DUPLICATE OF Get polygons close to a lat,long in MySQL or MySQL Find Polygon Nearest to Point

The older question that I mentioned above was asked 6 years ago and plenty of geospatial features have been implemented in mysql from that time.

It's better to answer considering new features of mysql < 5.6.1 offers,such as
ST_Contains
and ...

sqlfiddle doesn't offer MYSQL version above 5.6 so I decided to write a litte example here:

CREATE TABLE `urbanpolygons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`paths` polygon NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
)
INSERT INTO urbanpolygons (`id`, `name`, `paths`) VALUES ('1', 'area_1', ST_GeomFromText('POLYGON ((29.63383 52.492869,29.63411 52.493942,29.634446 52.494586,29.634837 52.495658,29.635397 52.497289,29.635676 52.49804,29.635919 52.498856,29.636217 52.499778,29.63674 52.501345,29.636889 52.501967,29.637131 52.502847,29.636851 52.503405,29.636497 52.503748,29.635285 52.50495,29.634483 52.505808,29.632804 52.503641,29.631592 52.502053,29.629652 52.499628,29.628496 52.498126,29.629204 52.497783,29.629652 52.497482,29.630361 52.496774,29.631126 52.495594,29.632002 52.494242,29.632655 52.493577,29.633308 52.492912,29.63383 52.492869))'));
INSERT INTO urbanpolygons (`id`, `name`, `paths`) VALUES ('2', 'area_2', ST_GeomFromText('POLYGON ((29.633084 52.486968,29.632748 52.488384,29.629055 52.489929,29.625176 52.49023,29.625138 52.489328,29.624579 52.487869,29.624467 52.487311,29.627078 52.485809,29.628831 52.484179,29.630249 52.482634,29.631144 52.482419,29.633084 52.486968))'));
INSERT INTO urbanpolygons (`id`, `name`, `paths`) VALUES ('3', 'area_3', ST_GeomFromText('POLYGON ((29.622564 52.488553,29.624504 52.494046,29.626892 52.498853,29.630622 52.505204,29.632711 52.507436,29.628981 52.509153,29.624653 52.501085,29.620773 52.505376,29.616744 52.49645,29.617192 52.490956,29.622564 52.488553))'));


I've this point:

GeomFromText('POINT(29.630528, 52.492461)')


this point is out of the provided polygons but I want to get the nearest polygon to this point which here is
area_1
!

Answer Source

finally I could handle It this way:

fortunately ST_DISTANCE() function provides us distance from any geometry object to others!(I believe the distance between a point and polygon calculated from point to the nearest edge of polygons,tested with google maps distance and the provided distance from ST_DISTANCE)

select id
    from (select st_distance(paths,POINT(29.630528, 52.492461))as polyDistance,id from urbanpolygons)as nearestPoly
    where polyDistance = (select MIN(st_distance(paths,POINT(29.630528, 52.492461))) from urbanpolygons);

which I know is a bit complex and inefficient,and i'm eager to learn any better solution!