根据用户的位置查询数据库值 [英] Query database values based on user's location

问题描述

如何根据用户的位置值对数据库执行查询？该应用程序是用HTML5，CSS，Javascript开发的，PHP有一个带有列的数据库，如下表所示。

How can I perform a query on the database as per the user's location value? The application was developed with HTML5, CSS, Javascript, PHP has a database with columns as in the below table.

在html网页上收集用户地理坐标，并将其与中的值进行比较数据库，用数据库中的位置查找用户最近的位置。

On the html webpage the users geo coordinates are collected and are to be compared with the values in the database to find the nearest place to the user with the places in the database.

请告诉我如何实现这一点。任何示例/示例都将受到赞赏。

Please let me know how to achieve this. Any examples / samples will be appreciated.

推荐答案

有一个问题可以比较各种空间数据库的功能，GIS：PostGIS / PostgreSQL vs. MySql vs. SQL Server？，Postgis在MySQL上的表现非常明显。

There is a question that compares the capabilities of various spatial databases, GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?, where Postgis comes out a pretty clear winner over MySQL.

无论您使用的是MySQL还是Postgis，如果可以的话，您可以将纬度和经度值存储为几何/地理（Point），这样会更好。可用于查找附近事物的函数， ST_Distance ， ST_Distance_Sphere 和更加模糊的< - > operator ，参见使用PostGIS查找给定Point的n个最近邻居？（例如用法）直接在几何/地理列上工作。更重要的是，您可以添加空间索引，这些功能需要正常工作，这将大大超过单独索引的纬度和经度列上的搜索（这将取决于表大小，但随着表格大小的增长会增长），

Whether you use MySQL or Postgis, you would be much better off, if you can, storing your latitude and longitude values as a geometry/geography (Point), as the functions that can be used to find things nearby, ST_Distance, ST_Distance_Sphere and the more obscure <-> operator, see Find n Nearest Neighbors for given Point using PostGIS? (for example usage) work directly on geometry/geography columns. Even more importantly, you can add a spatial index, which these functions need to work properly, which will outperform searches on separately indexed latitude and longitude columns by a large margin (this will depend on table size, but will grow as table size grows),

在Postgis中，你可以将lat和lon转换成几何形状：

In Postgis, you can convert lat and lon to a geometry with:

alter table mytable add column geom (Geometry, 4326);
update mytable set geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326)
create index ix_spatial_mytable_geom on mytable using gist(geom);

此时，您将能够非常有效地查询其他点附近的点，使用任何点以上链接中的示例。

At this point, you will be able to very efficient queries to find points near other points, using any of the examples in the above links.

您可以在MySQL中执行类似的操作，但是，它不支持空间参考系统，即上面的4326，这意味着lat / lon，它缺少ST_MakePoint函数，因此您需要使用STGeomFromText并将lat / lon连接在一起以生成POINT。正如克劳迪奥和其他人所说的那样，它也可以做平面坐标中的所有事情，这对Postgis来说不是问题。

You can do similar things in MySQL, although, it does not support a spatial reference system, ie, the 4326 above, which means lat/lon, and it lacks a ST_MakePoint function, so you would need to use STGeomFromText and concatenate the lat/lon together to make a POINT. It also does everything in planar coordinates, as Claudio and others have stated, which is not an issue with Postgis.

我为一个长期的，有些切线的答案道歉，但是在大量数据（MySQL，SQL Server和Postgres / GIS）的数据库之间进行了各种迁移，并在途中犯了很多错误，我希望我可以让你朝着正确的方向前进（并添加一些未来的证明，如果你想开始使用一些其他的空间功能，Postigs就是这样的。）

I apologize for a long and somewhat tangential answer, but having done various migrations between databases on large amounts of data (MySQL, SQL Server and Postgres/GIS) and made lots of mistakes on the way, I hope I can set you off in the right direction (and add a bit of future proofing, if you want to start using some other spatial functionality, which Postigs has in spades).

这篇关于根据用户的位置查询数据库值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！