locked
How can I create a query that gets closest approximation to GPS coordinates RRS feed

  • Question

  • User1782166566 posted

    A little background I am programming android but using MVC 4 web api and SQL server. I am creating a program that gets GPS coordinates from users phone: I obtain the latitude and longitude of their location every 20 minutes. I have an SQL database with about 20,000 rows which contains 4 fields: Latitude, Longitude, City, State. My question is given a particular latitude and longitude how can I query the database to obtain the closes latitude and longitude so that I can tell them which city/town they are in?

    This is what I got so far and it is wrong because I am looking for the closest approximation because all of the GPS lats,longs will not match the ones in the database exactly any suggestions would be great

            public ActionResult getLocation(double latitude,double longitude)
            {
                var myloc = (from s in db.zipss where s.latitudes == latitude && s.longitudes == longitude select s).FirstOrDefault();
                return View(myloc);
            }

    Monday, June 8, 2015 7:20 PM

Answers

  • User-271186128 posted

    Hi romeo407,

    My question is given a particular latitude and longitude how can I query the database to obtain the closes latitude and longitude so that I can tell them which city/town they are in?

    As for this issue, I suppose you could try to use the Math.abs method to get the absolute value. here is a sample, please refer to it.

                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("City", typeof(String)));
                dt.Columns.Add(new DataColumn("Latitude", typeof(Int32)));
                dt.Columns.Add(new DataColumn("Longitude", typeof(Int32)));
    
                dt.Rows.Add("AAA", 3, 4);
                dt.Rows.Add("BBB", 5, 9);
                dt.Rows.Add("CCC", 4, 7);
                int latitude = 4;
                int longitude = 6;
    
                var query2 = (from dd in
                                 (from cc in dt.AsEnumerable()
                                  select new
                                  {
                                      City = cc.Field<string>("City"),
                                      Latitude = Math.Abs(cc.Field<Int32>("Latitude") - latitude),
                                      Longitude = Math.Abs(cc.Field<Int32>("Longitude") - longitude)
                                  }
                                     )
                             orderby dd.Latitude, dd.Longitude
                             select dd.City).FirstOrDefault();
    
                Response.Write(query2); //output:CCC

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2015 6:41 AM

All replies

  • User2024324573 posted

    As you have only a single table you can write the LINQ as:

    var myloc = db.zipss.Where(s.latitudes == latitude && s.longitudes == longitude).FirstOrDefault();
    Monday, June 8, 2015 7:59 PM
  • User-271186128 posted

    Hi romeo407,

    My question is given a particular latitude and longitude how can I query the database to obtain the closes latitude and longitude so that I can tell them which city/town they are in?

    As for this issue, I suppose you could try to use the Math.abs method to get the absolute value. here is a sample, please refer to it.

                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("City", typeof(String)));
                dt.Columns.Add(new DataColumn("Latitude", typeof(Int32)));
                dt.Columns.Add(new DataColumn("Longitude", typeof(Int32)));
    
                dt.Rows.Add("AAA", 3, 4);
                dt.Rows.Add("BBB", 5, 9);
                dt.Rows.Add("CCC", 4, 7);
                int latitude = 4;
                int longitude = 6;
    
                var query2 = (from dd in
                                 (from cc in dt.AsEnumerable()
                                  select new
                                  {
                                      City = cc.Field<string>("City"),
                                      Latitude = Math.Abs(cc.Field<Int32>("Latitude") - latitude),
                                      Longitude = Math.Abs(cc.Field<Int32>("Longitude") - longitude)
                                  }
                                     )
                             orderby dd.Latitude, dd.Longitude
                             select dd.City).FirstOrDefault();
    
                Response.Write(query2); //output:CCC

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2015 6:41 AM
  • User1782166566 posted
    Thanks a lot for that it worked greatly
    Thursday, June 11, 2015 2:32 AM