MYSQL：在地理空间查询中使用VARCHAR字段作为POINT

Question

I've decided to detect distance between two points. One of the points is static -35.735019 51.410041, however, the other one is a point which is given by a database field.

Here is my code:

SELECT
  r0_.restaurant_point AS restaurant_point_0,
  ST_Distance(GeomFromText('POINT(35.735019 51.410041)'), GeomFromText('POINT(r0_.restaurant_point)')) *
  100 AS sclr_1
FROM restaurants r0_

We stored r0.restaurant_point as a VARCHAR. Take the string below as an example: 35.73062161548168 51.410930156707764

Although it would be worked if I change r0.restaurant_point with static value, it can't get measure with database field.

Is there anyway for binding this value or another way to resolve?

Answer 1

To concatenate the value of the column restaurant_point in the string that represents the point, you can use CONCAT().

GeomFromText(CONCAT('POINT(', t.restaurant_point, ')'))

In your case you are just trying to create a Point NOT from the coordinates in the column, BUT from the column name itself. you need to change your code as follows:

SELECT
r0_.restaurant_point AS restaurant_point_0,
ST_Distance(
  GeomFromText('POINT(35.735019 51.410041)'), 
  GeomFromText(CONCAT('POINT(', r0_.restaurant_point, ')'))
) * 100 AS sclr_1
FROM restaurants r0_