使用MySQL空间扩展选择范围内的项目? [英] Select items within range using MySQL spatial extensions?
问题描述
我对MySQL空间扩展完全陌生,所以请原谅我对这个话题的无知。
场景:
1)我通过纬度和经度收集用户的位置。我将该位置存储在MySQL表中。
2)然后我显示该用户范围内的任何其他用户。范围会很小 - 比方说1000英尺
问题:
存储位置点的最佳做法是什么?查询和选择离用户最近的点的最佳做法是什么? 使用 POINT 空间数据类型来存储坐标。
结构如下所示:
CREATE TABLE gps name varchar(0),`location` point NOT NULL;
请注意栏位'位置'的类型。
之后,您必须为位置列定义一个SPATIAL索引。 code> CREATE SPATIAL INDEX sp_index ON gps(`location`);
插入数据(经度和纬度)
INSERT INTO gps(name,location)VALUES('India',GeomFromText('POINT(31.5 42.2)'))
函数 GeomFromText 接受一个字符串并返回一个Geometry对象。这里31.5和42.2分别是经度和纬度。请注意,它们之间存在无逗号。它们由空格分隔。
阅读数据
SELECT name,AsText(location)FROM gps;
AsText 函数将几何体的内部表示转换为字符串格式。
注意:您必须使用MySQL 5.0或更高版本的空间扩展支持。还可以使用'MyIsam'数据库引擎加快查询速度。
I am completely new to MySQL spatial extensions, so please excuse my ignorance on the topic.
The scenario:
1) I collect a user's location via latitude and longitude. I store that location in a MySQL table.
2) I then display any other users within a range of that user. The range would be small - let's say 1000 ft.
Question: What is the best practice for storing the location points? What is the best practice for querying and selecting the points nearest that user?
Use the POINT Spatial datatype to store the coordinates.
The structure is like this:
CREATE TABLE gps name varchar(0), `location` point NOT NULL;
Please note the type of column 'location'.
After that, you have to define a SPATIAL index for the 'location' column.
CREATE SPATIAL INDEX sp_index ON gps(`location`);
Inserting data (latitude and longitude)
INSERT INTO gps (name, location) VALUES ( 'India' , GeomFromText( ' POINT(31.5 42.2) ' ) )
The function GeomFromText takes a string and returns a Geometry Object. Here 31.5 and 42.2 are latitude and longitude respectively. Note that there is NO comma between them. They are delimited by a space.
Reading data
SELECT name, AsText(location) FROM gps;
The AsText function converts the internal representation of a geometry to a string format.
Note: You have to use MySQL 5.0 or above the spatial extension support. Also use 'MyIsam' db engine for faster queries.
这篇关于使用MySQL空间扩展选择范围内的项目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!