Find N Nearest LineString From Point Using MySQL Spatial Extensions

Question

I use MySQL Spatial Extensions to store data about roads and hotels. I store hotel data as a point, while I store road data as LineString. The tables look like this:

CREATE TABLE IF NOT EXISTS `Hotels` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` text, `coordinate` point NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `coordinate` (`coordinate`), ) CREATE TABLE IF NOT EXISTS `Roads` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` text, `route` linestring NOT NULL, PRIMARY KEY (`id`), SPATIAL KEY `coordinate` (`route`), ) 

Visualization of the instance will be like this.

My problem is given the number N and the point P, what is the SQL query to find the N nearest roads from point P? The distance is determined by the smallest perpendicular distance between the length of the road to the point as shown above. (although in reality the closest distance should be between the highway gate and the hotel, but in this case we can enter the highway from anywhere: P)

If for this problem there is no single solution to the SQL query, intermediate SQL query and post processing are acceptable to me. But what will be the effective SQL query and how to perform post-processing of data?

Answer 1

You can create two functions in the database:

• Distance: this will give you the distance between two points.
• DistanceFromLine: Here, the distance will be calculated from each point in the line and will give you the shortest distance.

Compare the distance between points and lines and select the shortest.

Here is the Distance function

---

 delimiter // CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double) RETURNS double DETERMINISTIC BEGIN SET @RlatA = radians(latA); SET @RlonA = radians(lonA); SET @RlatB = radians(latB); SET @RlonB = radians(LonB); SET @deltaLat = @RlatA - @RlatB; SET @deltaLon = @RlonA - @RlonB; SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) + COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2); RETURN 2 * ASIN(SQRT(@d)) * 637101; END// 

---

Here's the DistanceFromLine function:

---

 DROP function IF EXISTS `DistanceFromLine`; delimiter // CREATE FUNCTION `DistanceFromLine`( route LINESTRING, point1 POINT ) RETURNS INT DETERMINISTIC BEGIN DECLARE a INT Default 0 ; DECLARE minDistance INT Default 0; DECLARE currentDistance INT Default 0; DECLARE currentpoint point ; DECLARE size INT Default 0 ; SET size = NumPoints(route); simple_loop: LOOP SET a = a+1; SET currentpoint = PointN(route,a); SET currentDistance = Distance(X(point1), Y(point1), X(currentpoint),Y(currentpoint)); IF a = 1 THEN SET minDistance = currentDistance; END IF; IF currentDistance < minDistance THEN SET minDistance = currentDistance; END IF; IF a=size THEN LEAVE simple_loop; END IF; END LOOP simple_loop; RETURN (minDistance); END// 

---

Answer 2

This was a very useful answer for me, but I am using MySQL 5.7.18, which has more complex or just different geo-query functions. The distance function is no longer needed - use ST_Distance_Sphere. So here is an update of the same code to make DistanceFromLine compatible with modern (5.7.6+) MySQL ...

 DROP function IF EXISTS `DistanceFromLine`; delimiter // CREATE FUNCTION `DistanceFromLine`( route LINESTRING, point1 POINT ) RETURNS INT DETERMINISTIC BEGIN DECLARE a INT Default 0 ; DECLARE minDistance INT Default 0; DECLARE currentDistance INT Default 0; DECLARE currentpoint point ; DECLARE size INT Default 0 ; SET size = ST_NumPoints(route); simple_loop: LOOP SET a = a+1; SET currentpoint = ST_PointN(route,a); SET currentDistance = ST_Distance_Sphere(point1,currentpoint); IF a = 1 THEN SET minDistance = currentDistance; END IF; IF currentDistance < minDistance THEN SET minDistance = currentDistance; END IF; IF a=size THEN LEAVE simple_loop; END IF; END LOOP simple_loop; RETURN (minDistance); END// 

Answer 3

I am also working on this problem, but, unfortunately, finding the nearest road for hotels is an unfavorable solution. I found that the entrance on which the road goes is the final answer. In other words, the address. This means that the address table and matching points with the nearest address road.