Find lat and long based on cust_table with address

1

What is the best way to create a procedure which takes each address from the CUST_TABLE AND RETURNS LAT AND LONG?

CUST_TALBLE has the following fields: CUST_ID, ADDRESS,CITY,POSTAL CODE, LATITUDE,LONGITUDE.

THANKS.

2

I would think that you want to query for latitude and longitude based on cust_id rather than address. Cust_ID likely is an integer - which is unambiguous. Address can be mistyped, or have commas or periods etc.

CREATE PROCEDURE dbo.GetCustomerCoordinates
	@Cust_ID INT 
AS
	SET NOCOUNT,XACT_ABORT ON;
	
	SELECT Latitude, Longitude
	FROM Cust_Table
	WHERE @Cust_ID = Cust_ID;
GO

If you do want to use address, the proc would be similar. Just replace the parameter, its data type and the WHERE clause with corresponding values for address.

3

I want to find lat and long based on customer address, geocode the address and insert lat and long into the database fields.

4

Do you mean that you want to lookup the long/lat from an external source based on addresses?

5

Yes and once found needs to be inserted into the customer table.

6

OK -- so what is your external source?

7

I don't think I have one . To GeoCode manually I go to the following website http://www.gpsvisualizer.com/geocoder/, using my own BING key.

8

you'll likely have to purchase a subscription from a commercial vendor