MySQL - 在空间点附近选择
我根据下面的查询来选择另一个 point 的空间点附近的点lookup-given-latitude-longitude">SO 解决方案,但在过去的几个小时里,我无法让它返回任何结果,我现在有点急躁...... p>
我的表 lastcrawl
有一个简单的模式:
(通过ALTER TABLE lastcrawl ADD SPATIAL INDEX(point);添加空间键;
)
我已经通过以下方式成功插入了几行:
$query = sprintf("INSERT INTO lastcrawl (point) VALUES(GeomFromText('POINT(%F %F)'))",$lat,$lng);
但是,我无法查询最近的点来获得非空结果集.如何让查询选择离给定空间点最近的点?
<小时>插入 $lat=40, $lng=-100
后,尝试查询它也不会返回任何内容(因此,甚至不匹配精确坐标):
SELECT * FROM lastcrawl WHERE MBRContains(LineFromText(CONCAT( '(' , -100.000000 + 0.0005 * ( 111.1/cos(-100.000000)) , ' ' , 40.000000 + 0.0005/111.1 , ',' , -100.000000 - 0.0005/( 111.1/cos(40.000000)) , ' ' , 40.000000 - 0.0005/111.1 , ')' )) ,point)
我稍微修正了公式.
这个对我有用:
CREATE TABLE lastcrawl (id INT NOT NULL PRIMARY KEY, pnt POINT NOT NULL) ENGINE=MyISAM;插入进入最后一次爬行值(1,点(40,-100));设置@lat = 40;设置@lon = -100;选择 *来自最后一次爬行WHERE MBR包含(线串(观点(@lat + 10/111.1,@lon + 10/( 111.1/COS(RADIANS(@lat)))),观点 (@lat - 10/111.1,@lon - 10/( 111.1/COS(RADIANS(@lat))))),点);
I've based my query below to select points near a spatial point called point
on the other SO solution, but I've not been able to get it to return any results, for the past few hours, and I'm a bit edgy now...
My table lastcrawl
has a simple schema:
id (primary int, autoinc) point (spatial POINT)
(Added the spatial key via ALTER TABLE lastcrawl ADD SPATIAL INDEX(point);
)
$query = sprintf("SELECT * FROM lastcrawl WHERE MBRContains(LineFromText(CONCAT( '(' , %F + 0.0005 * ( 111.1 / cos(%F)) , ' ' , %F + 0.0005 / 111.1 , ',' , %F - 0.0005 / ( 111.1 / cos(%F)) , ' ' , %F - 0.0005 / 111.1 , ')' ) ,point)", $lng,$lng,$lat,$lng,$lat,$lat); $result = mysql_query($query) or die (mysql_error());
I've successfully inserted a few rows via:
$query = sprintf("INSERT INTO lastcrawl (point) VALUES( GeomFromText( 'POINT(%F %F)' ))",$lat,$lng);
But, I'm just not able to query the nearest points to get a non-null result set. How do you get the query to select the points nearest to a given spatial point?
After inserting $lat=40, $lng=-100
, trying to query this returns nothing as well (so, not even exact coordinates match):
SELECT * FROM lastcrawl WHERE MBRContains(LineFromText(CONCAT( '(' , -100.000000 + 0.0005 * ( 111.1 / cos(-100.000000)) , ' ' , 40.000000 + 0.0005 / 111.1 , ',' , -100.000000 - 0.0005 / ( 111.1 / cos(40.000000)) , ' ' , 40.000000 - 0.0005 / 111.1 , ')' )) ,point)
I've corrected the formula a little.
This one works for me:
CREATE TABLE lastcrawl (id INT NOT NULL PRIMARY KEY, pnt POINT NOT NULL) ENGINE=MyISAM;
INSERT
INTO lastcrawl
VALUES (1, POINT(40, -100));
SET @lat = 40;
SET @lon = -100;
SELECT *
FROM lastcrawl
WHERE MBRContains
(
LineString
(
Point
(
@lat + 10 / 111.1,
@lon + 10 / ( 111.1 / COS(RADIANS(@lat)))
),
Point (
@lat - 10 / 111.1,
@lon - 10 / ( 111.1 / COS(RADIANS(@lat)))
)
),
pnt
);
相关文章