# How can I approximate values in a query to get closest coordinates

• ### 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

• 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 Thursday, October 7, 2021 12:00 AM
Wednesday, June 17, 2015 2:00 AM