0

A table with elements (id, name, lat, lon). Via a query I get the lat and lon of the user. Now I want to make a query against my database (MariaDB). Give me all elements that are within 1000 meters of these coordinates.

There are ~300.000 elements in the database. My problem right now is that unfortunately I don't know how to make the query.

Can someone please give me an abstract sketch of how to make the query?

KrassVerpeilt
  • 437
  • 3
  • 10
  • 2
    This article is about longitude, latitude and indexing, seems worth reading. https://mariadb.com/kb/en/latitudelongitude-indexing/ – Luuk Mar 26 '22 at 18:02
  • please check this url https://stackoverflow.com/questions/29553895/querying-mysql-for-latitude-and-longitude-coordinates-that-are-within-a-given-mi – Rahul Biswas Mar 26 '22 at 18:44
  • Does this answer your question? [Querying MySQL for latitude and longitude coordinates that are within a given mile radius](https://stackoverflow.com/questions/29553895/querying-mysql-for-latitude-and-longitude-coordinates-that-are-within-a-given-mi) – yivi Apr 04 '22 at 14:53

1 Answers1

0

Use function below to get radius then check on the returned value

CREATE FUNCTION GetRadius(IN latitude1 INT, IN longitude1 INT, IN latitude2 INT, IN longitude2 INT)
RETURNS BIGINT
BEGIN

/* line below represent the calculation to get radius using 
 2 points (latitude, longitude)  */ 
RETURN 6371 * ACOS((SIN(latitude1) *  SIN(latitude2)) + (COS(latitude1) * COS(latitude2) * COS(longitude2 - longitude1)));

END;

Assum that latitude1 and longitude1 are the values that you get from the user and latitude2 and longitude2 represent the values in each record in database

then your final query should be like that

Declare @latitudeUserValue INT = 10; -- replace with user value
Declare @longitudeUserValue INT = 10; -- replace with user value

SELECT ALL FROM elements WHERE GetRadius(@latitudeUserValue, @longitudeUserValue, lat, lon) <= 1000;
Ahmed Ashour
  • 1
  • 1