Mysql database coordinate persistence using Entity Framework

Question

I’m having trouble making an efficient select for geolocation coordinates in Mysql. In the bank I am saving two values related to latitude and longitude, so when I perform the Select the two values are considered in the Where, but needed to be interpreted as one.

I saw that Mysql accepts data like Spatial Data, but EF does not talk with this data.

This is the class that represents the die to be saved

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Nomadwork.Infra.Data.ObjectData
{
    [Table("Address")]
    public class AddressModelData : AEntity
    {
        [Column(TypeName = "varchar(15)")]
        public string Zipcode { get; set; }


        [ Column(TypeName = "varchar(200)")]
        public string Street { get; set; }


        [Column(TypeName = "varchar(10)")]
        public string Number { get; set; }


        [ Column(TypeName = "varchar(30)")]
        public string Coutry { get; set; }


        [ Column(TypeName = "varchar(30)")]
        public string State { get; set; }


        [Required, Column(TypeName = "decimal(12,9)")]
        public decimal Latitude { get; set; }


        [Required, Column(TypeName = "decimal(12,9)")]
        public decimal Longitude { get; set; }

    }
}

This is the Select

public IEnumerable<EstablishmentModelData> GetByLocation(decimal latitude, decimal longitude)
            => _context.Establishments
                       .Include(x => x.Address)
                       .Where(establismment
                => (decimal.Round(establismment.Address.Latitude, 3).Equals(decimal.Round(latitude, 3))
                    || decimal.Round(establismment.Address.Longitude, 3).Equals(decimal.Round(longitude, 3)))
                     && establismment.Active)
                    .ToHashSet()
                    .Take(20)
                    .ToList();

The solution in this case was to round the values by decreasing the accuracy of the coordinates provided to compare with the stored coordinates so that it could return the next addresses.

I appreciate any kind of help.

Answer 1

The temporary solution was to add two more properties with rounded values with less precision: follows code below Class

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Nomadwork.Infra.Data.ObjectData
{
    [Table("Address")]
    public class AddressModelData : AEntity
    {
        [Column(TypeName = "varchar(15)")]
        public string Zipcode { get; set; }


        [ Column(TypeName = "varchar(200)")]
        public string Street { get; set; }


        [Column(TypeName = "varchar(10)")]
        public string Number { get; set; }


        [ Column(TypeName = "varchar(30)")]
        public string Coutry { get; set; }


        [ Column(TypeName = "varchar(30)")]
        public string State { get; set; }


        [Required, Column(TypeName = "decimal(12,9)")]
        public decimal Latitude { get; set; }


        [Required, Column(TypeName = "decimal(12,9)")]
        public decimal Longitude { get; set; }

        // Propriedade Latitude com menor precisão
        [Required, Column(TypeName = "decimal(4,2)")]
        public decimal LatitudePrecision { get; set; }

        // Propriedade Longitude com menor precisão
        [Required, Column(TypeName = "decimal(4,2)")]
        public decimal LongitudePricision { get; set; }

    }
}

Select

 public IEnumerable<EstablishmmentModelData> GetByLocation(decimal latitude, decimal longitude)
            => _context.Establishments
                       .Include(x => x.Address)
                       .Where(establismment
                => (establismment.Address.LatitudePrecision.Equals(decimal.Round(latitude, 2))
                    && establismment.Address.LongitudePricision.Equals(decimal.Round(longitude, 2)))
                     && establismment.Active)
                    .ToHashSet()
                    .Take(20)
                    .ToList();