In my case query looks like:
SELECT *, ( 6371 * acos ( cos (radians(41.38035200000000000000)) * cos(radians( latitude )) * cos(radians( longitude) - radians(2.16106200000000000000)) + sin (radians(41.38035200000000000000)) * sin( radians(latitude ))) ) AS distance FROM gpslocations HAVING distance < 10 ORDER BY distanceDatabase looks like this (exported with HeidiSQL):
CREATE DATABASE IF NOT EXISTS `reversegeocoding` USE `reversegeocoding`; CREATE TABLE IF NOT EXISTS `cities` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, PRIMARY KEY (`ID`,`Name`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `countries` ( `ID` int NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, PRIMARY KEY (`ID`,`Name`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `gpslocations` ( `Latitude` decimal(23,20) NOT NULL, `Longitude` decimal(23,20) NOT NULL, `FileName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `CityID` int DEFAULT NULL, `CountryID` int DEFAULT NULL, PRIMARY KEY (`Latitude`,`Longitude`) USING BTREE, KEY `CityFK` (`CityID`), KEY `CountryFK` (`CountryID`), CONSTRAINT `CityFK` FOREIGN KEY (`CityID`) REFERENCES `cities` (`ID`), CONSTRAINT `CountryFK` FOREIGN KEY (`CountryID`) REFERENCES `countries` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `gpslocationsgroupedby10kmdistancs` ( `ID` int NOT NULL AUTO_INCREMENT, `Latitude` decimal(23,20) NOT NULL, `Longitude` decimal(23,20) NOT NULL, `FileName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `CityID` int DEFAULT NULL, `CountryID` int DEFAULT NULL, PRIMARY KEY (`ID`,`Latitude`,`Longitude`) USING BTREE, KEY `City10KmFK` (`CityID`), KEY `Country10KmFK` (`CountryID`), CONSTRAINT `City10KmFK` FOREIGN KEY (`CityID`) REFERENCES `cities` (`ID`), CONSTRAINT `Country10KmFK` FOREIGN KEY (`CountryID`) REFERENCES `countries` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Here is my Code:
using System; using System.Collections.Generic; using System.Linq; using MySql.Data.MySqlClient; namespace Radius { class Program { private static string ConnectionString => "SERVER=localhost;DATABASE=reversegeocoding;UID=root;PASSWORD=myPass;"; public class LatLngFileNameModel { public string Latitude { get; set; } public string Longitude { get; set; } public string FileName { get; set; } public string CityID { get; set; } public string CountryID { get; set; } } static void Main(string[] args) { List<LatLngFileNameModel> latLngFileNames = new List<LatLngFileNameModel>(); string sqlGpslocations = "select * from gpslocations"; using MySqlConnection mySqlConnection = new MySqlConnection(ConnectionString); using MySqlCommand mySqlCommand = new MySqlCommand(sqlGpslocations, mySqlConnection); mySqlConnection.Open(); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { LatLngFileNameModel latLngFileName = new LatLngFileNameModel { Latitude = mySqlDataReader["Latitude"].ToString().Replace(',', '.') , Longitude = mySqlDataReader["Longitude"].ToString().Replace(',', '.') , FileName = mySqlDataReader["FileName"].ToString() , CityID = mySqlDataReader["CityID"].ToString() , CountryID = mySqlDataReader["CountryID"].ToString() }; Console.WriteLine($"Geocoding: {latLngFileName.Latitude}, {latLngFileName.Longitude}, FileName: {latLngFileName.FileName}"); if (!IsWithinRadius(latLngFileName.Latitude, latLngFileName.Longitude, latLngFileNames, "10")) { using MySqlCommand gpslocationsgroupedby10kmdistancs = new MySqlCommand(); using MySqlConnection mySqlConnectionGpslocationsgroupedby10kmdistancs = new MySqlConnection(ConnectionString); mySqlConnectionGpslocationsgroupedby10kmdistancs.Open(); gpslocationsgroupedby10kmdistancs.Connection = mySqlConnectionGpslocationsgroupedby10kmdistancs; gpslocationsgroupedby10kmdistancs.CommandText = $"INSERT INTO reversegeocoding.gpslocationsgroupedby10kmdistancs (Latitude, Longitude, FileName, CityID, CountryID) VALUES " + $"('{latLngFileName.Latitude}'" + $", '{latLngFileName.Longitude}'" + $", '{latLngFileName.FileName}'" + $", '{latLngFileName.CityID}'" + $", '{latLngFileName.CountryID}'" + ");"; gpslocationsgroupedby10kmdistancs.ExecuteNonQuery(); latLngFileNames.Add(latLngFileName); } } } private static bool IsWithinRadius(string latitude, string longitude, List<LatLngFileNameModel> latLngFileNames, string distanceKm = "0.5") { if (latLngFileNames.Any()) { latitude = latitude.Replace(',', '.'); longitude = longitude.Replace(',', '.'); string sqlRadius = "SELECT *, " + "(6371 * acos (" + $"cos ( radians({latitude})) " + "* cos( radians( latitude )) " + "* cos( radians( longitude) " + $"- radians({longitude})) " + $"+ sin (radians({latitude})) " + "* sin( radians( latitude )) " + ")) AS distance " + "FROM gpslocations " + $"HAVING distance < {distanceKm} " + "ORDER BY distance "; using (MySqlConnection mySqlConnection = new MySqlConnection(ConnectionString)) using (MySqlCommand mySqlCommand = new MySqlCommand(sqlRadius, mySqlConnection)) { mySqlConnection.Open(); MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { LatLngFileNameModel latLngFileName = new LatLngFileNameModel { Latitude = mySqlDataReader["Latitude"].ToString().Replace(',', '.'), Longitude = mySqlDataReader["Longitude"].ToString().Replace(',', '.'), FileName = mySqlDataReader["FileName"].ToString() }; if (latLngFileNames.Any(latLng => latLng.Latitude == latLngFileName.Latitude && latLng.Longitude == latLngFileName.Longitude)) { return true; } } } } return false; } } }Example project download from here