Home

Details: Written by Stanko Milosev; Category: C#; Published: 29 May 2021; Hits: 546

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

Details: Written by Stanko Milosev; Category: C#; Published: 24 May 2021; Hits: 558

Here is my example of reverese geocoding using Google Cloud API.

For example I have a json like this:

[
	{
		"Latitude": 50.85583333333334,
		"Longitude": 6.971111111111111
	},
	{
		"Latitude": 50.73305555555556,
		"Longitude": 7.14638888888889
	},
	{
		"Latitude": 50.73305555555556,
		"Longitude": 7.100555555555555
	},
	{
		"Latitude": 50.73305555555556,
		"Longitude": 7.100555555555555
	},
	{
		"Latitude": 44.60615194444445,
		"Longitude": 10.07165611111111
	},
	{
		"Latitude": 44.60615194444445,
		"Longitude": 10.07165611111111
	},
	{
		"Latitude": 45.312777777777778,
		"Longitude": 19.800555555555556
	},
	{
		"Latitude": 45.212535833333337,
		"Longitude": 11.29848288888889
	},
	{
		"Latitude": 44.60631472222222,
		"Longitude": 10.071570277777778
	},
	{
		"Latitude": 45.528055555555557,
		"Longitude": 13.567777777777778
	},
	{
		"Latitude": 45.53,
		"Longitude": 13.563055555555556
	},
	{
		"Latitude": 15.006388888888889,
		"Longitude": 74.0125
	}
]

Now I want these GPS position to covert to city name and country name. Problem is that not every GPS coordinate can be translated to city, some of them will be translated rather to a comune (municipality), for example Terenzo 44°36'22.2"N 10°04'18.0"E, but in my case I wanted to have saved everything under one name, city name. The other problem is that I want to have only english names like "Cologne" but not "Köln", for example check out this JSON response, notice this part:

{
	"address_components": [
		{
			"long_name": "6A",
			"short_name": "6A",
			"types": [
				"street_number"
			]
		},
		{
			"long_name": "Drosselweg",
			"short_name": "Drosselweg",
			"types": [
				"route"
			]
		},
		{
			"long_name": "Rodenkirchen",
			"short_name": "Rodenkirchen",
			"types": [
				"political",
				"sublocality",
				"sublocality_level_1"
			]
		},
		{
			"long_name": "Köln",
			"short_name": "Köln",
			"types": [
				"locality",
				"political"
			]
		},
		{
			"long_name": "Kreisfreie Stadt Köln",
			"short_name": "Kreisfreie Stadt Köln",
			"types": [
				"administrative_area_level_3",
				"political"
			]
		},
		{
			"long_name": "Köln",
			"short_name": "K",
			"types": [
				"administrative_area_level_2",
				"political"
			]
		},
		{
			"long_name": "Nordrhein-Westfalen",
			"short_name": "NRW",
			"types": [
				"administrative_area_level_1",
				"political"
			]
		},
		{
			"long_name": "Germany",
			"short_name": "DE",
			"types": [
				"country",
				"political"
			]
		},
		{
			"long_name": "50997",
			"short_name": "50997",
			"types": [
				"postal_code"
			]
		}
	],
	"formatted_address": "Drosselweg 6A, 50997 Köln, Germany",
	"geometry": {
		"location": {
			"lat": 50.8557891,
			"lng": 6.971133699999999
		},
		"location_type": "ROOFTOP",
		"viewport": {
			"northeast": {
				"lat": 50.8571380802915,
				"lng": 6.972482680291502
			},
			"southwest": {
				"lat": 50.8544401197085,
				"lng": 6.969784719708497
			}
		}
	},
	"place_id": "ChIJ8WAVaNcjv0cRwA9j4gjo7eU",
	"plus_code": {
		"compound_code": "VX4C+8F Cologne, Germany",
		"global_code": "9F28VX4C+8F"
	},
	"types": [
		"street_address"
	]
}

Notice type:

"types": [
	"street_address"
]

Now check this part of reult:

  {
	 "address_components" : [
		{
		   "long_name" : "Cologne",
		   "short_name" : "Cologne",
		   "types" : [ "locality", "political" ]
		},
		{
		   "long_name" : "Kreisfreie Stadt Köln",
		   "short_name" : "Kreisfreie Stadt Köln",
		   "types" : [ "administrative_area_level_3", "political" ]
		},
		{
		   "long_name" : "Cologne",
		   "short_name" : "K",
		   "types" : [ "administrative_area_level_2", "political" ]
		},
		{
		   "long_name" : "North Rhine-Westphalia",
		   "short_name" : "NRW",
		   "types" : [ "administrative_area_level_1", "political" ]
		},
		{
		   "long_name" : "Germany",
		   "short_name" : "DE",
		   "types" : [ "country", "political" ]
		}
	 ],
	 "formatted_address" : "Cologne, Germany",
	 "geometry" : {
		"bounds" : {
		   "northeast" : {
			  "lat" : 51.08496299999999,
			  "lng" : 7.1620628
		   },
		   "southwest" : {
			  "lat" : 50.8304427,
			  "lng" : 6.7725819
		   }
		},
		"location" : {
		   "lat" : 50.937531,
		   "lng" : 6.9602786
		},
		"location_type" : "APPROXIMATE",
		"viewport" : {
		   "northeast" : {
			  "lat" : 51.08496299999999,
			  "lng" : 7.1620628
		   },
		   "southwest" : {
			  "lat" : 50.8304427,
			  "lng" : 6.7725819
		   }
		}
	 },
	 "place_id" : "ChIJ5S-raZElv0cR8HcqSvxgJwQ",
	 "types" : [ "locality", "political" ]
  }

Notice type:

"types" : [ "locality", "political" ]

You can notice that if type is "street_address" name will be written in german "Kön", but when type is '"locality", "political"'' name will be in english "Cologne". That is why I am gonna always search for a name in a result first where types are "locality", "political".

Here is my code:

using System;
using System.IO;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace ReverseGeocoding
{
  class Program
  {
    static void Main(string[] args)
    {
      string json = "gpsCoordinates.json";
      JsonSerializer serializer = new JsonSerializer();

      using (FileStream s = File.Open(json, FileMode.Open))
      using (StreamReader sr = new StreamReader(s))
      using (JsonReader reader = new JsonTextReader(sr))
      {
        while (reader.Read())
        {
          if (reader.TokenType == JsonToken.StartObject)
          {
            LatLng latLng = serializer.Deserialize(reader);

            if (latLng is not null)
            {
              Console.WriteLine(
                $"Geocode: {latLng.Latitude}, {latLng.Longitude}");

              string url =
                $@"https://maps.googleapis.com/maps/api/geocode/json?latlng={latLng.Latitude},{latLng.Longitude}&language=en&key=myKey";

              string reverseGeocodeJsonResponse;
              using (System.Net.WebClient client = new System.Net.WebClient())
              {
                reverseGeocodeJsonResponse = client.DownloadString(url);
              }

              JObject reverseGeocodeJObjectResponse = JObject.Parse(reverseGeocodeJsonResponse);

              if (reverseGeocodeJObjectResponse.Count >= 3 &&
                  reverseGeocodeJObjectResponse["status"]?.ToString().ToLower() != "ok")
              {
                throw new Exception($"Error: {reverseGeocodeJObjectResponse["error_message"]}");
              }

              string city = string.Empty;
              string country = string.Empty;

              //first try to find a city and country where "types": ["locality","political"] in "address_components"
              if (reverseGeocodeJObjectResponse["results"] is not null)
              {

                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where "types": ["locality","political"] in "address_components"
                  if (result["types"] is not null
                      && result["types"].Count() > 1
                      && result["types"][0] is not null
                      && string.Equals(result["types"][0].ToString(), "locality",
                        StringComparison.InvariantCultureIgnoreCase)
                      && result["types"][1] is not null
                      && string.Equals(result["types"][1].ToString(), "political",
                        StringComparison.InvariantCultureIgnoreCase))
                  {

                    if (result["address_components"] is not null)
                    {
                      foreach (JToken addressComponent in result["address_components"])
                      {
                        if (addressComponent["types"] is not null
                            && addressComponent["types"].Count() > 1
                            && addressComponent["types"][0] is not null
                            && string.Equals(addressComponent["types"][0].ToString(), "locality",
                              StringComparison.InvariantCultureIgnoreCase)
                            && addressComponent["types"][1] is not null
                            && string.Equals(addressComponent["types"][1].ToString(), "political",
                              StringComparison.InvariantCultureIgnoreCase))
                        {
                          city = addressComponent["long_name"] is null
                            ? string.Empty
                            : addressComponent["long_name"].ToString();
                          city = city.Replace("'", "''");
                        }
                      }
                    }
                  }

                  //try to find a country where "types": ["country","political"] in "address_components"
                  if (result["types"] is not null
                      && result["types"].Count() > 1
                      && result["types"][0] is not null
                      && string.Equals(result["types"][0].ToString(), "country",
                        StringComparison.InvariantCultureIgnoreCase)
                      && result["types"][1] is not null
                      && string.Equals(result["types"][1].ToString(), "political",
                        StringComparison.InvariantCultureIgnoreCase))
                  {
                    if (result["address_components"] is not null)
                    {
                      foreach (JToken addressComponent in result["address_components"])
                      {
                        country = addressComponent["long_name"] is null
                          ? string.Empty
                          : addressComponent["long_name"].ToString();
                        country = country.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                if (reverseGeocodeJObjectResponse["results"] is not null)
                {
                  foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                  {

                    //try to find a city where type of "address_components" is "plus_code"
                    if (result["types"] is not null
                        && result["types"][0] is not null
                        && string.Equals(result["types"][0].ToString(), "plus_code",
                          StringComparison.InvariantCultureIgnoreCase))
                    {
                      if (result["address_components"] is not null)
                      {
                        foreach (JToken addressComponent in result["address_components"])
                        {
                          if (addressComponent["types"] is not null
                              && addressComponent["types"].Count() > 1
                              && addressComponent["types"][0] is not null
                              && string.Equals(addressComponent["types"][0].ToString(), "locality",
                                StringComparison.InvariantCultureIgnoreCase)
                              && addressComponent["types"][1] is not null
                              && string.Equals(addressComponent["types"][1].ToString(), "political",
                                StringComparison.InvariantCultureIgnoreCase))
                          {
                            city = addressComponent["long_name"] is null
                              ? string.Empty
                              : addressComponent["long_name"].ToString();
                            city = city.Replace("'", "''");
                          }
                        }
                      }
                    }
                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where type is "street_address"
                  if (string.Equals(result["types"][0].ToString(), "street_address",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 1 &&
                          string.Equals(addressComponent["types"][0].ToString(), "locality",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        city = addressComponent["long_name"].ToString();
                        city = city.Replace("'", "''");
                      }
                    }

                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where "types": ["administrative_area_level_3","political"] in "address_components" where type is "plus_code"
                  if (string.Equals(result["types"][0].ToString(), "plus_code",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 1
                          && string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_3",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        city = addressComponent["long_name"].ToString();
                        city = city.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where "types": ["administrative_area_level_1","political"] in "address_components" where type is "plus_code"
                  if (string.Equals(result["types"][0].ToString(), "plus_code",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 1
                          && string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_1",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        city = addressComponent["long_name"].ToString();
                        city = city.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where "types": ["route"] in "address_components" where "types": ["administrative_area_level_1", "political"]
                  if (string.Equals(result["types"][0].ToString(), "route",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 1
                          && string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_1",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        city = addressComponent["long_name"].ToString();
                        city = city.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              //if city was not found
              if (string.IsNullOrWhiteSpace(city))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a city where "type" is "plus_code" and "types": ["political", "sublocality", "sublocality_level_1"] in "address_components"
                  if (string.Equals(result["types"][0].ToString(), "plus_code",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 2
                          && string.Equals(addressComponent["types"][0].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "sublocality",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][2].ToString(), "sublocality_level_1",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        city = addressComponent["long_name"].ToString();
                        city = city.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              //if country was not found
              if (string.IsNullOrWhiteSpace(country))
              {
                foreach (JToken result in reverseGeocodeJObjectResponse["results"])
                {

                  //try to find a country where "type" is "plus_code" and "types": ["country", "political"] in "address_components"
                  if (string.Equals(result["types"][0].ToString(), "plus_code",
                    StringComparison.InvariantCultureIgnoreCase))
                  {
                    foreach (JToken addressComponent in result["address_components"])
                    {
                      if (addressComponent["types"].Count() > 1
                          && string.Equals(addressComponent["types"][0].ToString(), "country",
                            StringComparison.InvariantCultureIgnoreCase)
                          && string.Equals(addressComponent["types"][1].ToString(), "political",
                            StringComparison.InvariantCultureIgnoreCase))
                      {
                        country = addressComponent["long_name"].ToString();
                        country = country.Replace("'", "''");
                      }
                    }
                  }
                }
              }

              Console.WriteLine($"City: {city}, country: {country}");

            }
          }
        }
      }
    }

    class LatLng
    {
      public string Latitude { get; set; }
      public string Longitude { get; set; }
    }
  }
}

This part of code will be executed with ~~50°43'59.0"N 7°08'47.0"E, JSON response you can see here~~, better example is 50°51'21.0"N 6°58'16.0"E(50.855833, 6.971111), JSON response you can see here:

//try to find a city where "types": ["locality","political"] in "address_components"
if (result["types"] is not null
	&& result["types"].Count() > 1
	&& result["types"][0] is not null
	&& string.Equals(result["types"][0].ToString(), "locality",
	StringComparison.InvariantCultureIgnoreCase)
	&& result["types"][1] is not null
	&& string.Equals(result["types"][1].ToString(), "political",
	StringComparison.InvariantCultureIgnoreCase))
{
	if (result["address_components"] is not null)
	{
		foreach (JToken addressComponent in result["address_components"])
		{
			if (addressComponent["types"] is not null
				&& addressComponent["types"].Count() > 1
				&& addressComponent["types"][0] is not null
				&& string.Equals(addressComponent["types"][0].ToString(), "locality",
				StringComparison.InvariantCultureIgnoreCase)
				&& addressComponent["types"][1] is not null
				&& string.Equals(addressComponent["types"][1].ToString(), "political",
				StringComparison.InvariantCultureIgnoreCase))
			{
				city = addressComponent["long_name"] is null
					? string.Empty
					: addressComponent["long_name"].ToString();
				city = city.Replace("'", "''");
			}
		}
	}
}

This part of code will be executed with 44°36'22.2"N 10°04'18.0"E, JSON response you can see here

//try to find a city where "types": ["administrative_area_level_3","political"] in "address_components" where type is "plus_code"
if (string.Equals(result["types"][0].ToString(), "plus_code",
	StringComparison.InvariantCultureIgnoreCase))
{
	foreach (JToken addressComponent in result["address_components"])
	{
		if (addressComponent["types"].Count() > 1
			&& string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_3",
				StringComparison.InvariantCultureIgnoreCase)
			&& string.Equals(addressComponent["types"][1].ToString(), "political",
				StringComparison.InvariantCultureIgnoreCase))
		{
			city = addressComponent["long_name"].ToString();
			city = city.Replace("'", "''");
		}
	}
}

This part of code will be executed with 45°18'46.0"N 19°48'02.0"E, JSON response you can see here:

//try to find a city where type of "address_components" is "plus_code"
if (result["types"] is not null
	&& result["types"][0] is not null
	&& string.Equals(result["types"][0].ToString(), "plus_code",
		StringComparison.InvariantCultureIgnoreCase))
{
	if (result["address_components"] is not null)
	{
		foreach (JToken addressComponent in result["address_components"])
		{
			if (addressComponent["types"] is not null
				&& addressComponent["types"].Count() > 1
				&& addressComponent["types"][0] is not null
				&& string.Equals(addressComponent["types"][0].ToString(), "locality",
					StringComparison.InvariantCultureIgnoreCase)
				&& addressComponent["types"][1] is not null
				&& string.Equals(addressComponent["types"][1].ToString(), "political",
					StringComparison.InvariantCultureIgnoreCase))
			{
				city = addressComponent["long_name"] is null
					? string.Empty
					: addressComponent["long_name"].ToString();
				city = city.Replace("'", "''");
			}
		}
	}
}

This part of code will be executed with 45°12'45.1"N 11°17'54.5"E, JSON response you can see here:

//try to find a city where type is "street_address"
if (string.Equals(result["types"][0].ToString(), "street_address",
	StringComparison.InvariantCultureIgnoreCase))
{
	foreach (JToken addressComponent in result["address_components"])
	{
		if (addressComponent["types"].Count() > 1 &&
			string.Equals(addressComponent["types"][0].ToString(), "locality",
				StringComparison.InvariantCultureIgnoreCase)
			&& string.Equals(addressComponent["types"][1].ToString(), "political",
				StringComparison.InvariantCultureIgnoreCase))
		{
			city = addressComponent["long_name"].ToString();
			city = city.Replace("'", "''");
		}
	}
}

This part of code will be executed with 45°31'41.0"N 13°34'04.0"E, JSON response you can see here:

  //try to find a city where "types": ["administrative_area_level_1","political"] in "address_components" where type is "plus_code"
  if (string.Equals(result["types"][0].ToString(), "plus_code",
	StringComparison.InvariantCultureIgnoreCase))
  {
	foreach (JToken addressComponent in result["address_components"])
	{
	  if (addressComponent["types"].Count() > 1
		  && string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_1",
			StringComparison.InvariantCultureIgnoreCase)
		  && string.Equals(addressComponent["types"][1].ToString(), "political",
			StringComparison.InvariantCultureIgnoreCase))
	  {
		city = addressComponent["long_name"].ToString();
		city = city.Replace("'", "''");
	  }
	}
  }

This part of code will be executed with 45°31'48.0"N 13°33'47.0"E, JSON response you can see here:

  //try to find a city where "types": ["route"] in "address_components" where "types": ["administrative_area_level_1", "political"]
  if (string.Equals(result["types"][0].ToString(), "route",
	StringComparison.InvariantCultureIgnoreCase))
  {
	foreach (JToken addressComponent in result["address_components"])
	{
	  if (addressComponent["types"].Count() > 1
		  && string.Equals(addressComponent["types"][0].ToString(), "administrative_area_level_1",
			StringComparison.InvariantCultureIgnoreCase)
		  && string.Equals(addressComponent["types"][1].ToString(), "political",
			StringComparison.InvariantCultureIgnoreCase))
	  {
		city = addressComponent["long_name"].ToString();
		city = city.Replace("'", "''");
	  }
	}
  }

This part of code will be executed with 15°00'23.0"N 74°00'45.0"E, JSON response you can see here:

  //try to find a city where "type" is "plus_code" and "types": ["political", "sublocality", "sublocality_level_1"] in "address_components"
  if (string.Equals(result["types"][0].ToString(), "plus_code",
	StringComparison.InvariantCultureIgnoreCase))
  {
	foreach (JToken addressComponent in result["address_components"])
	{
	  if (addressComponent["types"].Count() > 2
		  && string.Equals(addressComponent["types"][0].ToString(), "political",
			StringComparison.InvariantCultureIgnoreCase)
		  && string.Equals(addressComponent["types"][1].ToString(), "sublocality",
			StringComparison.InvariantCultureIgnoreCase)
		  && string.Equals(addressComponent["types"][2].ToString(), "sublocality_level_1",
			StringComparison.InvariantCultureIgnoreCase))
	  {
		city = addressComponent["long_name"].ToString();
		city = city.Replace("'", "''");
	  }
	}
  }

Example project download from here

Details: Written by Stanko Milosev; Category: C#; Published: 28 February 2021; Hits: 1591

If in Visual Studio I just change "Output path", VS will always add target framework to folder structure, like "netcoreapp3.1" ("C:\projects\EFCore\tools\netcoreapp3.1"), in order to avoid this, right click on the project, click on "Edit Project File" and add:

<AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>

From here

Details: Written by Stanko Milosev; Category: C#; Published: 27 February 2021; Hits: 1473

core

Mostly I was using this web site for my example.

In my case I have created the project C:\projects\ReverseGeoCoding, then open PowerShell, and first, if you don't have EF tool installed, execute following:

dotnet tool install --global dotnet-ef

Go to project folder, like cd "C:\projects\ReverseGeoCoding", and add packages "Microsoft.EntityFrameworkCore.Design" and "MySql.EntityFrameworkCore" like:

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package MySql.EntityFrameworkCore --version 5.0.0-m8.0.23

Now scaffold:

dotnet ef dbcontext scaffold "SERVER=localhost;DATABASE=myDb;UID=uid;PASSWORD=pass;" MySql.EntityFrameworkCore -o C:\projects\ReverseGeoCoding -f

Now, I have made my own console applicatcation for scaffolding:

using System.Diagnostics;
using System.Threading;

namespace MySQLScaffold
{
  class Program
  {
    static void Main(string[] args)
    {
      // Use ProcessStartInfo class
      ProcessStartInfo startInfo = new ProcessStartInfo
      {
        CreateNoWindow = false,
        UseShellExecute = false,
        FileName = "dotnet",
        WindowStyle = ProcessWindowStyle.Hidden,
        Arguments = "ef dbcontext scaffold"
                    + " \"SERVER=localhost;DATABASE=myDb;UID=uid;PASSWORD=pass;\""
                    + " MySql.EntityFrameworkCore -o"
                    + " C:\\projects\\ReverseGeoCoding"
                    + " -f -v"
                    + " -p C:\\projects\\ReverseGeoCoding"
      };

      Mutex myMutex;
      if (!Mutex.TryOpenExisting("testMutex", out myMutex))
      {
        myMutex = new Mutex(true, "testMutex");
        myMutex.WaitOne();
        try
        {
          // Start the process with the info we specified.
          // Call WaitForExit and then the using statement will close.
          using (Process exeProcess = Process.Start(startInfo))
          {
            exeProcess?.WaitForExit();
          }
        }
        catch
        {
          // Log error.
        }
        finally
        {
          myMutex.ReleaseMutex();
        }
      }

      //Console.WriteLine("Press any key");
      //Console.ReadKey();
    }
  }
}

Then I have created Class library application "EFCore", where in prebuild event I have added

C:\projects\MySQLScaffold\MySQLScaffold\bin\Debug\netcoreapp3.1\MySQLScaffold.exe

I had to add package "Microsoft.EntityFrameworkCore.Design", and I had to reference my "ReverseGeoCoding" project, here is how my "EFCore.csproj" look like:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <Target Name="PreBuild" BeforeTargets="PreBuildEvent">
    <Exec Command="C:\projects\MySQLScaffold\MySQLScaffold\bin\Debug\netcoreapp3.1\MySQLScaffold.exe" />
  </Target>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>

  <ItemGroup>
    <ProjectReference Include="..\..\ReverseGeoCoding\ReverseGeoCoding.csproj" />
  </ItemGroup>

</Project>

Start
Prev
1
2
3
4
5
6
7
8
9
10
Next
End

Check if GPS position is within radius

Reverse geocoding

Change output path without adding target framework

Entity framework Core MySql scaffolding example

Subcategories

C#

ASP.NET

Azure

JavaScript

Software Development Philosophy

MS SQL

IBM WebSphere MQ

MySQL

Joomla

Delphi

PHP

Windows

Life

Lazarus

Downloads

Android

CSS

Chrome