CODEWITHSUNDEEP
c#mysqlasp.netsql
mmmoustache
mmmoustache

Reputation: 2323

Calculating distances in MySQL and displaying the distance between them in .NET

I'm trying to use MySQL's spatial extensions to calculate a queried set of coordinates to the coordinates of a table of places, but I would also like to display the distance between the set of points. The results are then added to a dynamic list to be iterated later on.

The result will be something like this: Place 1 (3 miles away), Place 2 (5 miles away) etc...

Firstly, is there a way of creating a variable from the distance calculation so I can display it similarly to the above? Secondly, How should I amend my sql statement to query the coordinates columns of the places?

Here's my code so far:

List<dynamic> Items = new List<dynamic>();
var longitude = 90.0000;            
var latitude = 0.0000;
var radius = 50000;

using(MySqlConnection con = new MySqlConnection("server=localhost;database=database;user id=user;password=pass"))
{   
    string sql = "SELECT *,(((acos(sin((@latitude*pi()/180)) * sin((`Latitude`*pi()/180))+cos((@latitude*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((@longitude- `Longitude`)* pi()/180))))*180/pi())*60*1.1515) as distance FROM items HAVING distance <= @radius";
    con.Open(); 

    MySqlCommand cmd = new MySqlCommand(sql,con);
    cmd.Parameters.Add(new MySqlParameter("@latitude", latitude));
    cmd.Parameters.Add(new MySqlParameter("@longitude", longitude));
    cmd.Parameters.Add(new MySqlParameter("@radius", radius));

    using(MySqlDataReader reader = cmd.ExecuteReader()){
        while(reader.Read()){ 
            Items.Add(new {
                Name = reader["Name"].ToString(),
                Latitude = reader["Latitude"].ToString(),
                Longitude = reader["Longitude"].ToString()
            });
        }   
    }

    con.Close();
}

Upvotes: 0

Views: 151

Answers (1)

Rowland Shaw
Rowland Shaw

Reputation: 38130

From that code, it should be as simple as:

Items.Add(new {
    Name = reader["Name"].ToString(),
    Latitude = reader["Latitude"].ToString(),
    Longitude = reader["Longitude"].ToString(),
    Distance = reader["distance"]
});

Upvotes: 2

Related Questions