CODEWITHSUNDEEP
c#asp.net-mvcgeocoding
Nika Javakhishvili
Nika Javakhishvili

Reputation: 21

How to calculate the bounding box for a given lat/lng location and querying the database in c#

I have given a location defined by latitude and longitude. Now i want to calculate a bounding box within e.g. 500 meters of that point.

The bounding box should be defined as latmin, lngmin and latmax, lngmax.

My database has almost 152.000 records ( locations of objects defined by lat long), and if the user enters the specific object in the search bar (search by name), I should find objects to the specific radius e.g. within 500 meters.

For example this is one record: Latitude = 41.72213058512578, Longitude = 44.77684020996094, Name = company1

If I find latmin, lngmin and latmax, lngmax how to query the database? I want to achieve this in C#.

Upvotes: 2

Views: 770

Answers (1)

Douglas
Douglas

Reputation: 177

Look into the Haversine Formula.

Create this query in your application or Stored Procedure from your database. It will return the 15 closest locations to given latpoint and longpoint within a given radius. The radius is now set to 50 feel free to change that.

 SELECT z.zip,
        z.primary_city,
        z.latitude, z.longitude,
        p.distance_unit
                 * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.latitude))
                 * COS(RADIANS(p.longpoint) - RADIANS(z.longitude))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.latitude)))) AS distance_in_meters
  FROM zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
                50.0 AS radius,      111054.0 AS distance_unit
    ) AS p ON 1=1
  WHERE z.latitude
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.longitude
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
  ORDER BY distance_in_meters
  LIMIT 15

change 111054.0 to 69.0 for distance_in_miles

Something like this in your C# application to call a stored procedure and get the data back from the query in a list. 

private List<DataModel> getData(double latitude, double longitude, int radius)
{ 
    SqlParameter[] param = 
                     {
                        new SqlParameter("@lat", latitude),
                        new SqlParameter("@lon", longitude),
                        new SqlParameter("@rad", radius)
                     };          
  //Call stored procedure
  var data db.Database.SqlQuery<DataModel("Haversine",param)
  return data.ToList();
}

Upvotes: 1

Related Questions