Geek Answers Handbook

Query Database Values ​​Based on User Location

How can I execute a query in the database by user location value? The application was developed using HTML5, CSS, Javascript, PHP has a database with columns, as in the table below.

database structure

On the html web page, user geocommands are assembled and compared with the values ​​in the database to find the closest place for the user with places in the database.

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

+4
source share
2 answers

, , GIS: PostGIS/PostgreSQL MySql SQL Server?, Postgis MySQL.

MySQL Postgis, , , / (Point), , , ST_Distance, ST_Distance_Sphere ↔ operator, . n PostGIS? (, ) /. , , , ( , ),

Postgis lat lon :

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);

, .

MySQL, , .. 4326, lat/lon, ST_MakePoint, STGeomFromText lat/lon , POINT. , , Postgis.

, (MySQL, SQL Server Postgres/GIS) , , ( , , ).

+3

- ( , ).

. :

distLat = abs(userLat - placeLat)
distLong = abs(userLong - placeLong)

, . :

distance = squareRoot(distLat * distLat + distLong * distLong)

, - , .

MySQL, , :

SELECT * FROM places ORDER BY MIN(SQRT((p.latitude - userLatitude) * (p.latitude - userLatitude) + (p.longitude - userLongitude) * (p.longitude - userLongitude))) LIMIT 1

, , , . .

, . MySQL- , MySQL 5.6. ST_DISTANCE, - , , . , MySQL 5.6, , ST_DISTANCE, , , , "" .

. : http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc

OP, , .

, , . .

, , : 40.704391, -73.994675. :

sqrt((40.704391 - 40.689167)^2 + (-73.994675 - -74.044444)^2) = 0.052045399

( 40.7179666, -73.9670125), :

sqrt((40.7179666 - 40.689167)^2 + (-73.9670125 - -74.044444)^2) = 0.082613886

, , . , 4 , 1,6 .

. , . , (, 48.858360, 2.294460):

 sqrt((48.858360 - 40.689167)^2 + (2.294460 - -74.044444)^2) = 76.77476134

(, 41.890238, 12.492242):

sqrt((41.890238 - 40.689167)^2 + (12.492242 - -74.044444)^2) = 86.54502063

, , , 8000 , Tour Eiffel 800 .

, , - , . , . , , , ( 61.252240, -149.896769) , ( 63.049797, 179.310011). 1500 , :

sqrt((61.252240 - 63.049797)^2 + (-149.896769 - 179.310011)^2) = 329.2116875

1500 : - 50.

, , , 0 . , " ".

, , . 0 : , . - 180 . , 180 , : http://www.bouwman.com/world/Formilab-180.html. .

, :

distance = squareRoot(min((userLat - placeLat)^2, (userLat - placeLat - 360)^2) + (userLong - placeLong)^2)

, 360, -180 180.

, 180 , , , 180 . - : 30.84564166.

, , . "" : https://en.wikipedia.org/wiki/Geographical_distance: D

+2

More articles:


All Articles