Written by: Stanko Milosev
Category: C#
Hits: 2043
Now I want to have GPS positions in one database where distance of each will be more then 10km. Query I took from here and here
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 distance 
Database 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