# asp.net, c#, mvc, zip code finder

and it has around 100 records only.

Database schema.

Contractor Name
Address, City, State, Zip, Geocode1, GeoCode2

And now I want to create asp.net / c# / mvc app that allow end user to enter zip code, distance mile like 1 to 5
and allow the end user to search all of the contractors and sorted by shortest distance in mileage.

Can you show me how the query should look like? Thanks,
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
you can calculate the distance between locations using "Haversine formula"

DECLARE @location TABLE (
city nvarchar(100),
geoLoc1 DECIMAL(18,10) ,
geoLoc2  DECIMAL(18,10)
)
INSERT INTO @location

SELECT 'Coimbatore',11.00,       77.00
UNION ALL
SELECT 'Erode', 11.20,       77.46
UNION ALL
SELECT 'Tiruchchirappalli',       10.50,      78.46
UNION ALL

DECLARE @custLat DECIMAL = 9.58
DECLARE @custLng DECIMAL = 78.10;

--Distance in Miles
SELECT City , ( 3960 * acos( cos( radians( @custLat ) ) *
sin( radians( @custLat ) ) * sin( radians(  geoLoc1  ) ) ) ) AS Distance
FROM @location
ORDER BY Distance ASC

--Distance in Kilemeter
SELECT City , ( 6371 * acos( cos( radians( @custLat ) ) *
sin( radians( @custLat ) ) * sin( radians(  geoLoc1  ) ) ) ) AS Distance
FROM @location
ORDER BY Distance ASC

ref: http://www.stormconsultancy.co.uk/blog/development/code-snippets/the-haversine-formula-in-c-and-sql/
0
Author Commented:
I don't understand what you try to tell me. Please read my question carefully.
I don't need insert sql. The table already existed and given the column names as well.
0
When you want to get nearest addresses from your local database, you need geocode (longitude and latitude). You already mentioned you have them in the database.
So you need to get the longitude and latitude of the given zipcode, this can be tricky because each country treats the zipcode differently like USA (11011) and Netherlands (1234AA).

What you can do is make httpwebrequest to let's say google geocoding and get the geocode of the address that has been entered, see here for documentation for google geocoding.

Please note that Google Geocoding has a limit of 2,500 requests p. day

When you have the longitude and latitude, you can use the following select query to get the nearest address:
`````` --Distance in Miles
SELECT City , ( 3960 * acos( cos( radians( @custLat ) ) *
sin( radians( @custLat ) ) * sin( radians(  Geocode1) ) ) ) AS Distance
FROM YourTableName
ORDER BY Distance ASC

--Distance in Kilemeter
SELECT City , ( 6371 * acos( cos( radians( @custLat ) ) *
sin( radians( @custLat ) ) * sin( radians(  Geocode1) ) ) ) AS Distance
FROM YourTableName
ORDER BY Distance ASC
``````

I don't see the tablename you are using, so you need to replace it.
Also the sql parameters needs to be filled on the info you retreived from the entered address.

@custLat and @custLng
0

Experts Exchange Solution brought to you by