- Details
- Written by: Stanko Milosev
- Category: C#
- Hits: 2488
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
- Details
- Written by: Stanko Milosev
- Category: C#
- Hits: 2623
[
{
"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
//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#
- Hits: 3421
<AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>From here
- Details
- Written by: Stanko Milosev
- Category: C#
- Hits: 3466
dotnet tool install --global dotnet-efGo 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.23Now scaffold:
dotnet ef dbcontext scaffold "SERVER=localhost;DATABASE=myDb;UID=uid;PASSWORD=pass;" MySql.EntityFrameworkCore -o C:\projects\ReverseGeoCoding -fNow, 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.exeI 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>