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

Assume I already have a database with contractor name, address, and geocode (40.714224,-73.961452) of each address.
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,
solution1368Asked:
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.

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

I hope this SQL querey will help you

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
SELECT 'Madras (Chennai)',       13.04,       80.17

DECLARE @custLat DECIMAL = 9.58
DECLARE @custLng DECIMAL = 78.10;
 
 --Distance in Miles
SELECT City , ( 3960 * acos( cos( radians( @custLat ) ) *
  cos( radians( geoLoc1 ) ) * cos( radians(  geoLoc2  ) - radians( @custLng ) ) +
  sin( radians( @custLat ) ) * sin( radians(  geoLoc1  ) ) ) ) AS Distance
FROM @location
ORDER BY Distance ASC

--Distance in Kilemeter
SELECT City , ( 6371 * acos( cos( radians( @custLat ) ) *
  cos( radians( geoLoc1 ) ) * cos( radians(  geoLoc2  ) - radians( @custLng ) ) +
  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
solution1368Author 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
StephanLead Software EngineerCommented:
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.
https://developers.google.com/maps/documentation/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 ) ) *
  cos( radians( Geocode1) ) * cos( radians(  Geocode2) - radians( @custLng ) ) +
  sin( radians( @custLat ) ) * sin( radians(  Geocode1) ) ) ) AS Distance
FROM YourTableName
ORDER BY Distance ASC

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

Open in new window


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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.