public void WriteLocationName(LocationCoordinate locationCoordinateSearch, LocationCoordinateAndDescription locationInDatabaseCoordinateAndDescription) { Mono.Data.Sqlite.SqliteTransaction sqlTransaction; do { sqlTransaction = dbTools.TransactionBegin(); #region INSERT INTO LocationName string sqlCommand = "INSERT INTO LocationName (Latitude, Longitude, Name, City, Province, Country) " + "Values (@Latitude, @Longitude, @Name, @City, @Province, @Country)"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { locationInDatabaseCoordinateAndDescription.Description.Name = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Name) ? null : locationInDatabaseCoordinateAndDescription.Description.Name; locationInDatabaseCoordinateAndDescription.Description.Region = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Region) ? null : locationInDatabaseCoordinateAndDescription.Description.Region; locationInDatabaseCoordinateAndDescription.Description.City = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.City) ? null : locationInDatabaseCoordinateAndDescription.Description.City; locationInDatabaseCoordinateAndDescription.Description.Country = string.IsNullOrEmpty(locationInDatabaseCoordinateAndDescription.Description.Country) ? null : locationInDatabaseCoordinateAndDescription.Description.Country; //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@Latitude", locationInDatabaseCoordinateAndDescription.Coordinate.Latitude); commandDatabase.Parameters.AddWithValue("@Longitude", locationInDatabaseCoordinateAndDescription.Coordinate.Longitude); commandDatabase.Parameters.AddWithValue("@Name", locationInDatabaseCoordinateAndDescription.Description.Name); commandDatabase.Parameters.AddWithValue("@City", locationInDatabaseCoordinateAndDescription.Description.City); commandDatabase.Parameters.AddWithValue("@Province", locationInDatabaseCoordinateAndDescription.Description.Region); commandDatabase.Parameters.AddWithValue("@Country", locationInDatabaseCoordinateAndDescription.Description.Country); int rowAffted = commandDatabase.ExecuteNonQuery(); // Execute the query LocationCoordinateAndDescriptionUpdate(locationCoordinateSearch, locationInDatabaseCoordinateAndDescription.Coordinate, locationInDatabaseCoordinateAndDescription.Description); } #endregion } while (!dbTools.TransactionCommit(sqlTransaction)); }
private LocationCoordinateAndDescription ReadLocationName(LocationCoordinate locationCoordinateSearch, float locationAccuracyLatitude, float locationAccuracyLongitude) { LocationCoordinateAndDescription locationCoordinateAndDescriptionInDatabase = null; Mono.Data.Sqlite.SqliteTransaction sqlTransactionSelect; do { sqlTransactionSelect = dbTools.TransactionBeginSelect(); #region SELECT FROM LocationName string sqlCommand = "SELECT Latitude, Longitude, Name, City, Province, Country, " + "Max(Abs(Latitude - @Latitude), Abs(Longitude - @Longitude)) AS Distance " + "FROM LocationName WHERE Latitude >= (@Latitude - @LocationAccuracyLatitude) AND Latitude <= (@Latitude + @LocationAccuracyLatitude) " + "AND Longitude >= (@Longitude - @LocationAccuracyLongitude) AND Longitude <= (@Longitude + @LocationAccuracyLongitude) " + "ORDER BY Distance LIMIT 1"; using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransactionSelect)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@Latitude", locationCoordinateSearch.Latitude); commandDatabase.Parameters.AddWithValue("@Longitude", locationCoordinateSearch.Longitude); commandDatabase.Parameters.AddWithValue("@LocationAccuracyLatitude", locationAccuracyLatitude); commandDatabase.Parameters.AddWithValue("@LocationAccuracyLongitude", locationAccuracyLongitude); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { locationCoordinateAndDescriptionInDatabase = new LocationCoordinateAndDescription(); locationCoordinateAndDescriptionInDatabase.Coordinate.Latitude = (float)dbTools.ConvertFromDBValFloat(reader["Latitude"]); locationCoordinateAndDescriptionInDatabase.Coordinate.Longitude = (float)dbTools.ConvertFromDBValFloat(reader["Longitude"]); locationCoordinateAndDescriptionInDatabase.Description.Name = dbTools.ConvertFromDBValString(reader["Name"]); locationCoordinateAndDescriptionInDatabase.Description.City = dbTools.ConvertFromDBValString(reader["City"]); locationCoordinateAndDescriptionInDatabase.Description.Region = dbTools.ConvertFromDBValString(reader["Province"]); locationCoordinateAndDescriptionInDatabase.Description.Country = dbTools.ConvertFromDBValString(reader["Country"]); locationCoordinateAndDescriptionInDatabase.Description.Name = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Name) ? null : locationCoordinateAndDescriptionInDatabase.Description.Name; locationCoordinateAndDescriptionInDatabase.Description.Region = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Region) ? null : locationCoordinateAndDescriptionInDatabase.Description.Region; locationCoordinateAndDescriptionInDatabase.Description.City = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.City) ? null : locationCoordinateAndDescriptionInDatabase.Description.City; locationCoordinateAndDescriptionInDatabase.Description.Country = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatabase.Description.Country) ? null : locationCoordinateAndDescriptionInDatabase.Description.Country; LocationCoordinateAndDescriptionUpdate(locationCoordinateSearch, locationCoordinateAndDescriptionInDatabase.Coordinate, locationCoordinateAndDescriptionInDatabase.Description); } } } #endregion } while (!dbTools.TransactionCommitSelect(sqlTransactionSelect)); return(locationCoordinateAndDescriptionInDatabase); }
private void UpdateLocationName(LocationCoordinate locationCoordinateInDatabase, LocationCoordinateAndDescription locationCoordinateAndDescriptionInDatbase, float locationAccuracyLatitude, float locationAccuracyLongitude) { DeleteLocationName(locationCoordinateInDatabase, locationAccuracyLatitude, locationAccuracyLongitude); WriteLocationName(locationCoordinateInDatabase, locationCoordinateAndDescriptionInDatbase); //string sqlCommand = // "UPDATE LocationName SET " + // "Name = @Name, " + // "City = @City, " + // "Province = @Province, " + // "Country = @Country " + // //"WHERE Latitude = @Latitude AND Longitude = @Longitude"; // "WHERE Latitude = (SELECT Latitude FROM (" + // "SELECT Latitude, Name, Max(Abs(Latitude-@Latitude), Abs(Longitude - @Longitude)) AS Distance " + // "FROM LocationName WHERE Latitude >= (@Latitude - @LocationAccuracyLatitude) AND Latitude <= (@Latitude + @LocationAccuracyLatitude) " + // "AND Longitude >= (@Longitude - @LocationAccuracyLongitude) AND Longitude <= (@Longitude + @LocationAccuracyLongitude) " + // "ORDER BY Distance DESC LIMIT 1 " + // ")) AND " + // "Longitude = (SELECT Longitude FROM (" + // "SELECT Longitude, Name, Max(Abs(Latitude-@Latitude), Abs(Longitude - @Longitude)) AS Distance " + // "FROM LocationName WHERE Latitude >= (@Latitude - @LocationAccuracyLatitude) AND Latitude <= (@Latitude + @LocationAccuracyLatitude) " + // "AND Longitude >= (@Longitude - @LocationAccuracyLongitude) AND Longitude <= (@Longitude + @LocationAccuracyLongitude) " + // "ORDER BY Distance DESC LIMIT 1))"; //using (CommonSqliteCommand commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) //{ // locationCoordinateAndDescriptionInDatbase.Description.Name = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatbase.Description.Name) ? null : locationCoordinateAndDescriptionInDatbase.Description.Name; // locationCoordinateAndDescriptionInDatbase.Description.Region = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatbase.Description.Region) ? null : locationCoordinateAndDescriptionInDatbase.Description.Region; // locationCoordinateAndDescriptionInDatbase.Description.City = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatbase.Description.City) ? null : locationCoordinateAndDescriptionInDatbase.Description.City; // locationCoordinateAndDescriptionInDatbase.Description.Country = string.IsNullOrEmpty(locationCoordinateAndDescriptionInDatbase.Description.Country) ? null : locationCoordinateAndDescriptionInDatbase.Description.Country; // //commandDatabase.Prepare(); // commandDatabase.Parameters.AddWithValue("@Latitude", locationCoordinateInDatabase.Latitude); // commandDatabase.Parameters.AddWithValue("@Longitude", locationCoordinateInDatabase.Longitude); // commandDatabase.Parameters.AddWithValue("@Name", locationCoordinateAndDescriptionInDatbase.Description.Name); // commandDatabase.Parameters.AddWithValue("@City", locationCoordinateAndDescriptionInDatbase.Description.City); // commandDatabase.Parameters.AddWithValue("@Province", locationCoordinateAndDescriptionInDatbase.Description.Region); // commandDatabase.Parameters.AddWithValue("@Country", locationCoordinateAndDescriptionInDatbase.Description.Country); // commandDatabase.Parameters.AddWithValue("@LocationAccuracyLatitude", locationAccuracyLatitude); // commandDatabase.Parameters.AddWithValue("@LocationAccuracyLongitude", locationAccuracyLongitude); // int affetedRows = commandDatabase.ExecuteNonQuery(); // Execute the query // if (affetedRows <= 0) // { // //DEBUG // } // LocationCoordinateAndDescriptionUpdate(locationCoordinateInDatabase, locationCoordinateAndDescriptionInDatbase.Coordinate, locationCoordinateAndDescriptionInDatbase.Description); //} }
public LocationCoordinateAndDescription AddressLookupAndReverseGeocoder( LocationCoordinate locationCoordinateSearch, float locationAccuracyLatitude, float locationAccuracyLongitude, bool onlyFromCache, bool canReverseGeocoder, LocationDescription metadataLocationDescription, bool forceReloadUsingReverseGeocoder) { if (locationCoordinateSearch == null) { //DEBUG return(null); } #region Only From Cache - Nothing in cache - ** return null ** if (onlyFromCache && !LocationCoordinateAndDescriptionExsistInCache(locationCoordinateSearch)) { return(null); } #endregion if (!forceReloadUsingReverseGeocoder) { #region If exist - ** Return ** location from Cache or Database LocationCoordinateAndDescription locationInDatbaseCoordinateAndDescription = ReadLocationNameFromDatabaseOrCache(locationCoordinateSearch, locationAccuracyLatitude, locationAccuracyLongitude); if (locationInDatbaseCoordinateAndDescription != null) { return(locationInDatbaseCoordinateAndDescription); } #endregion #region If not exist in Database, but Metadata has Location info, save it and ** return it *** if (metadataLocationDescription != null) { locationInDatbaseCoordinateAndDescription = new LocationCoordinateAndDescription( locationCoordinateSearch, metadataLocationDescription); WriteLocationName(locationCoordinateSearch, locationInDatbaseCoordinateAndDescription); return(locationInDatbaseCoordinateAndDescription); } #endregion } else { #region When: Force to read from ReverseGeocode - Delete old data and continue to ReverseGeocode ReadLocationNameFromDatabaseOrCache(locationCoordinateSearch, locationAccuracyLatitude, locationAccuracyLongitude); DeleteLocation(locationCoordinateSearch, locationAccuracyLatitude, locationAccuracyLongitude); #endregion } if (canReverseGeocoder) { #region ReverseGeocoder LocationCoordinateAndDescription locationInDatbaseCoordinateAndDescription; try { var y = new ReverseGeocoder(); var r2 = y.ReverseGeocode(new ReverseGeocodeRequest { Longitude = locationCoordinateSearch.Longitude, Latitude = locationCoordinateSearch.Latitude, BreakdownAddressElements = true, ShowExtraTags = true, ShowAlternativeNames = true, ShowGeoJSON = true, PreferredLanguages = PreferredLanguagesString }); r2.Wait(); if (r2.IsCompleted && !r2.IsFaulted && r2.Result != null && r2.Result.Address != null) { locationInDatbaseCoordinateAndDescription = new LocationCoordinateAndDescription(); locationInDatbaseCoordinateAndDescription.Coordinate.Latitude = locationCoordinateSearch.Latitude; locationInDatbaseCoordinateAndDescription.Coordinate.Longitude = locationCoordinateSearch.Longitude; locationInDatbaseCoordinateAndDescription.Description.City = (r2.Result.Address.City + " " + r2.Result.Address.Town + " " + r2.Result.Address.Village).Trim().Replace(" ", " "); locationInDatbaseCoordinateAndDescription.Description.Country = r2.Result.Address.Country; locationInDatbaseCoordinateAndDescription.Description.Name = (r2.Result.Address.Road + " " + r2.Result.Address.HouseNumber).Trim(); locationInDatbaseCoordinateAndDescription.Description.Region = (r2.Result.Address.State + " " + r2.Result.Address.County + " " + r2.Result.Address.Suburb + " " + r2.Result.Address.District + " " + r2.Result.Address.Hamlet).Trim().Replace(" ", " "); locationInDatbaseCoordinateAndDescription.Description.Name = string.IsNullOrEmpty(locationInDatbaseCoordinateAndDescription.Description.Name) ? null : locationInDatbaseCoordinateAndDescription.Description.Name; locationInDatbaseCoordinateAndDescription.Description.Region = string.IsNullOrEmpty(locationInDatbaseCoordinateAndDescription.Description.Region) ? null : locationInDatbaseCoordinateAndDescription.Description.Region; locationInDatbaseCoordinateAndDescription.Description.City = string.IsNullOrEmpty(locationInDatbaseCoordinateAndDescription.Description.City) ? null : locationInDatbaseCoordinateAndDescription.Description.City; locationInDatbaseCoordinateAndDescription.Description.Country = string.IsNullOrEmpty(locationInDatbaseCoordinateAndDescription.Description.Country) ? null : locationInDatbaseCoordinateAndDescription.Description.Country; WriteLocationName(locationCoordinateSearch, locationInDatbaseCoordinateAndDescription); return(locationInDatbaseCoordinateAndDescription); } else { return(null); } } catch (Exception ex) { Logger.Error(ex, "AddressLookup"); } #endregion } return(null); }
public void AddressUpdate(LocationCoordinate locationCoordinateInDatabase, LocationCoordinateAndDescription locationCoordinateAndDescription, float locationAccuracyLatitude, float locationAccuracyLongitude) { UpdateLocationName( locationCoordinateInDatabase: locationCoordinateInDatabase, locationCoordinateAndDescriptionInDatbase: locationCoordinateAndDescription, locationAccuracyLatitude, locationAccuracyLongitude); }