mysql - 查找距离 map 上的点最近的分区

Question

我有表 Metro_station:

CREATE TABLE metro_station (
   metro_station_id int(11) NOT NULL DEFAULT '0',
   metro_station_name varchar(25) NOT NULL DEFAULT '',
   lng double(12,8) NOT NULL DEFAULT '0.00000000',
   lat double(12,8) NOT NULL DEFAULT '0.00000000',
   PRIMARY KEY (metro_station_id),
   KEY metro_line_id (metro_line_id)
     ) 
 INSERT INTO metro_station (metro_station_id, metro_station_name, lng, lat) VALUES
 (101, 'Name1', 27.53907013, 53.88590000),
 (102, 'Name2', 27.54864857, 53.89176877);

AND 表分区:

CREATE TABLE town_subdistrict (
  town_subdistrict_id int(11) NOT NULL DEFAULT '0',
  town_id int(11) NOT NULL DEFAULT '0',
  gis polygon DEFAULT NULL,
  PRIMARY KEY (town_subdistrict_id,town_id),
)

这是多边形的示例:

GeomFromText('POLYGON((27.68961910000009 53.85629640000011,27.6895764 53.85631860000004,27.68929950000009 53.85646880000002,27.68877430000001 53.8567605000001,27.68861040000004 53.8568457,27.68544530000002 53.85830420000002,27.68500580000005 53.85851960000003,27.68492000000004 53.8585892,27.68481260000006 53.85874140000007,27.68458750000006 53.85946250000008,27.68430890000006 53.86093070000005,27.68414780000008 53.86159519999996,27.6842887000001 53.86172090000009,27.68448770352082 53.86193099132333,27.68225238075777 53.85976079891942,27.67841497878083 53.85585953344282,27.67379448487603 53.85128354900957,27.6686035596248 53.84600035655184,27.66775455453183 53.84496766955237,27.66753322480145 53.84469845019371,27.66679610000001 53.84380180000007,27.66783560000009 53.84443690000007,27.66944540000004 53.84336060000003,27.67098580000009 53.84200109999997,27.67248260000003 53.84017040000015,27.67406100000008 53.83824710000003,27.6763772000001 53.83757050000008,27.67734710000004 53.83615390000004,27.67862339999999 53.83496679999995,27.67909450000011 53.83404369999998,27.6789687000001 53.83313210000006,27.67847662680461 53.83272019097144,27.69263016829434 53.83201716330638,27.69400539037001 53.82958231146308,27.69385479649461 53.82727139785728,27.6926500454914 53.82513813368487,27.68828282310418 53.82282697491387,27.68331322521536 53.82078238203517,27.68032580000007 53.81892670000002,27.67986200000003 53.81858860000009,27.67910950000001 53.81792580000012,27.67828620000011 53.81719320000005,27.67753390000013 53.81651950000003,27.67722040000001 53.81625219999998,27.67655940000009 53.81561589999992,27.67580240000011 53.81495020000008,27.6750387000001 53.81430539999997,27.67418070000007 53.81353240000009,27.67399120000006 53.81340409999999,27.67213690000017 53.81160090000013,27.67161260000005 53.81100900000003,27.67142320000011 53.8107294,27.67024680000015 53.8088484,27.67135070205039 53.80848121192794,27.67488737639733 53.80777385908746,27.68201776822574 53.80582015590466,27.68532627003423 53.80531487302213,27.68812138363106 53.80487695626488,27.68971859140055 53.80494432837148,27.69057423842 53.80514644404158,27.69365456768981 53.80592121175043,27.69519473232477 53.80666228050963,27.69901662234492 53.80925591801111,27.70494910834612 53.81346602682155,27.70837169642382 53.81552040639381,27.71316331973261 53.81777674004643,27.71264993152094 53.81821452205632,27.70695040000009 53.82211469999999,27.70701040000001 53.82255270000004,27.70702220000012 53.82268390000014,27.70701880000003 53.82323590000005,27.70689450000014 53.82376860000005,27.70667430000002 53.82419530000004,27.70614210000008 53.82510090000006,27.7019328 53.82809180000003,27.70013020000012 53.82938730000003,27.69772210000011 53.83105480000007,27.69648220327563 53.83182582490014,27.69612568368424 53.83245702810299,27.69719524245847 53.83334069660663,27.70054147070045 53.83540405953607,27.70430138456548 53.83787092626668,27.7054823282021 53.84078545245671,27.70753006210112 53.84583818257505,27.70801748194182 53.84704069019841,27.70821247348324 53.8473004850166,27.70828194328709 53.84739304147723,27.69748263140682 53.85265565096212,27.6974264419219 53.85258549112009,27.6972124000001 53.85268900000007,27.69685850000002 53.8528578,27.69519809999999 53.85363349999992,27.69528360000001 53.85369800000001,27.69511160000011 53.85378049999996,27.69502910000013 53.85372320000008,27.68961910000009 53.85629640000011))',0)

此数据用于 map 显示。我需要找到距离地铁站最近的小区。

SELECT 
    metro_station_id, metro_station_name, 
    (SELECT town_subdistrict_id 
     FROM town_subdistrict 
     WHERE ST_DISTANCE(gis, CONCAT('Point(', lng, ' ', lat, ')')) < 1 
     LIMIT 1) as subdistrict_id 
FROM metro_station 
WHERE 1

此查询始终返回 null。我该如何修复它？

Answer 1

您需要使用ST_GeomFromText将您的POINT转换为geom:

SQL DEMO

SELECT 
    metro_station_id, metro_station_name, 
    (SELECT town_subdistrict_id 
     FROM town_subdistrict 
     WHERE ST_DISTANCE(gis, ST_GeomFromText(CONCAT('Point(', lng, ' ', lat, ')'))) < 1 
     LIMIT 1) as subdistrict_id 
FROM metro_station