locked
How can I approximate values in a query to get closest coordinates RRS feed

  • Question

  • User-677013288 posted

    I'm really stumped on this, I am essentially creating a program that gives you your location (City,State) based on latitudes and longitudes. I have hundredth of radius's in the database. If I have a coordinate of (31.424141,-82.017859) I would like to grab the closest latitude and longitude to that. I have 4 fields in the query South_Latitude, North_Latitude, East_Longitude, West_Longitude. The latitude falls inside South_Latitude, North_Latitude and the longitude falls inside East_Longitude, West_Longitude  .For instance this example

    select top 1 * from zipsmee 
      where 31.424141 >= South_Latitude 
      AND North_Latitude>= 31.424141 
      AND -82.017859 >=East_Longitude 
      AND West_Longitude>=-82.017859 
      order by city ASC

    The problem with the query above is that if a given latitude,longitude fall's outside of the range the results come back NULL and instead of coming back null I want the closest record/row nearest to the given latitude,longitude

    Update

    The query above has some conditional statements so if it falls outside of that condition it will give me a null. I will check for that and in the event that I get a null the program will go to a second query and in this second query I would like to get the row/record nearest to the latitude,longitude again I am using these 4 fields South_Latitude, North_Latitude, East_Longitude, West_Longitude . Any suggestions would be great

    Monday, June 15, 2015 7:49 PM

Answers

  • User-271186128 posted

    Hi castro305,

    As for this issue, I suggest you could use the ABS method to get the close coordinates. You could refer to the following code:

    create table Table1
    (
    City varchar(30) primary key,
    Latitude int,
    longitude int,
    )
    
    insert into Table1
    select 'AAA',3,4 union
    select 'BBB',5,9 union
    select 'CCC',4,7
    go
    
    select * from Table1 
    go
    
    declare @latitude int;
    declare @longitude int;
    set @latitude =4;
    set @longitude =6;
    select top 1 City, MIN(ABS(Latitude-@latitude)+ ABS(longitude-@longitude)) as CC from Table1 group by City order by CC

    The output:

    City	CC
    CCC	1
    

    Best Regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 17, 2015 2:00 AM