July 15, 2013 at 1:50 pm
Hi, is there a Google SQL service where I could connect, query for two zipcodes and retrieve the distance I would normaly get from the web interface?
July 15, 2013 at 5:25 pm
si.brulotte (7/15/2013)
Hi, is there a Google SQL service where I could connect, query for two zipcodes and retrieve the distance I would normaly get from the web interface?
Here's a stored procedure that calls the Google geocoding service which will work on full address or a zip code (center of zone) returning the lat/lon for the location. The second procedure lets you enter an origin and destination address (or zip) and returns the miles between the two points based on the lat/lon returned by the first procedure.
CREATE PROCEDURE [dbo].[GeocodeAddress]
@Address VARCHAR(80) = NULL OUTPUT
,@City VARCHAR(40) = NULL OUTPUT
,@State VARCHAR(40) = NULL OUTPUT
,@PostalCode VARCHAR(20) = NULL OUTPUT
AS
BEGIN
/*
Must run this code on the server to activate the API
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
And the user must be created on [master]
USE [master]
GO
CREATE USER [USERNAME] FOR LOGIN [USERNAME] WITH DEFAULT_SCHEMA=[dbo]
GO
Then, give EXECUTE permission to the following system stored procedures
sp_OACreate
sp_OAMethod
sp_OAGetProperty
sp_OADestroy
*/
SET NOCOUNT ON
DECLARE
@OrigCity VARCHAR(40)
,@OrigState VARCHAR(40)
,@OrigPostalCode VARCHAR(20)
,@isMatch BIT
,@County VARCHAR(40) = NULL
,@GPSLatitude NUMERIC(9,6) = NULL
,@GPSLongitude NUMERIC(9,6) = NULL
,@MapURL VARCHAR(1024) = NULL
,@Country VARCHAR(40) = NULL
SET @OrigCity = @City
SET @OrigState = @State
SET @OrigPostalCode = @PostalCode
SET @isMatch = 0
DECLARE @URL VARCHAR(MAX)
SET @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&address='
+ CASE
WHEN @Address IS NOT NULL THEN @Address
ELSE ''
END
+ CASE
WHEN @City IS NOT NULL THEN ', ' + @City
ELSE ''
END
+ CASE
WHEN @State IS NOT NULL THEN ', ' + @State
ELSE ''
END
+ CASE
WHEN @PostalCode IS NOT NULL THEN ', ' + @PostalCode
ELSE ''
END
+ CASE
WHEN @Country IS NOT NULL THEN ', ' + @Country
ELSE ''
END
SET @URL = REPLACE(@URL, ' ', '+')
DECLARE
@Response VARCHAR(8000)
,@XML XML
,@Obj INT
,@Result INT
,@HTTPStatus INT
,@ErrorMsg VARCHAR(MAX)
EXEC @Result = sp_OACreate
'MSXML2.ServerXMLHttp'
,@Obj OUT
BEGIN TRY
EXEC @Result = sp_OAMethod
@Obj
,'open'
,NULL
,'GET'
,@URL
,false
EXEC @Result = sp_OAMethod
@Obj
,'setRequestHeader'
,NULL
,'Content-Type'
,'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod
@Obj
,send
,NULL
,''
EXEC @Result = sp_OAGetProperty
@Obj
,'status'
,@HTTPStatus OUT
EXEC @Result = sp_OAGetProperty
@Obj
,'responseXML.xml'
,@Response OUT
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH
EXEC @Result = sp_OADestroy
@Obj
IF (@ErrorMsg IS NOT NULL)
OR (@HTTPStatus <> 200)
BEGIN
SET @ErrorMsg = 'Error in spGeocode: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS VARCHAR(10)))
RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
RETURN
END
SET @XML = CAST(@Response AS XML)
SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')
SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')
SET @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)')
SET @State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]', 'varchar(40)')
SET @PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)')
SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/short_name) [1]', 'varchar(40)')
SET @County = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_2"]/short_name) [1]', 'varchar(40)')
SET @Address = ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]', 'varchar(40)'), '???') + ' ' + ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]', 'varchar(40)'), '???')
SET @MapURL = 'http://maps.google.com/maps?f=q&hl=en&q=' + CAST(@GPSLatitude AS VARCHAR(20)) + '+' + CAST(@GPSLongitude AS VARCHAR(20))
SELECT
@GPSLatitude AS Latitude
,@GPSLongitude AS Longitude
END
GO
CREATE PROCEDURE [dbo].[GeocodeGetLatLonDistanceBetweenPoints]
@Address1 VARCHAR(80) = NULL OUTPUT
,@City1 VARCHAR(40) = NULL OUTPUT
,@State1 VARCHAR(40) = NULL OUTPUT
,@PostalCode1 VARCHAR(20) = NULL OUTPUT
,@Address2 VARCHAR(80) = NULL OUTPUT
,@City2 VARCHAR(40) = NULL OUTPUT
,@State2 VARCHAR(40) = NULL OUTPUT
,@PostalCode2 VARCHAR(20) = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@pointA GEOGRAPHY
,@pointB GEOGRAPHY
,@OrigLat DECIMAL(9,6)
,@OrigLon DECIMAL(9,6)
,@DestLat DECIMAL(9,6)
,@DestLon DECIMAL(9,6)
,@g1 VARCHAR(MAX)
,@g2 VARCHAR(MAX)
IF OBJECT_ID('tempdb..#TempLatLon') IS NOT NULL
DROP TABLE #TempLatLon
CREATE TABLE #TempLatLon (
[ID] INT IDENTITY(1,1) NOT NULL,
[OrigLat] DECIMAL(9,6) NULL,
[OrigLon] DECIMAL(9,6) NULL,
[DestLat] DECIMAL(9,6) NULL,
[DestLon] DECIMAL(9,6) NULL,
PRIMARY KEY (ID))
INSERT INTO #TempLatLon
([OrigLat],[OrigLon])
EXEC dbo.GeocodeAddress
@Address1
,@City1
,@State1
,@PostalCode1
INSERT INTO #TempLatLon
([DestLat],[DestLon])
EXEC dbo.GeocodeAddress
@Address2
,@City2
,@State2
,@PostalCode2
SELECT
@OrigLat = OrigLat
,@OrigLon = OrigLon
FROM
#TempLatLon
WHERE
ID = 1
SELECT
@DestLat = DestLat
,@DestLon = DestLon
FROM
#TempLatLon
WHERE
ID = 2
SET @g1 = CAST(@OrigLon AS VARCHAR(20))+' '+CAST(@OrigLat AS VARCHAR(20))
SET @G2 = CAST(@DestLon AS VARCHAR(20))+' '+CAST(@DestLat AS VARCHAR(20))
SET @pointA = GEOGRAPHY::STGeomFromText('POINT('+@g1+')',4326);
SET @pointB = GEOGRAPHY::STGeomFromText('POINT('+@g2+')',4326);
SELECT CAST(@pointA.STDistance(@pointB) * 0.000621371192 AS DECIMAL(7,2)) AS Miles
/*
USAGE EXAMPLES:
EXEC dbo.GeocodeGetLatLonDistanceBetweenPoints
Parameters
@Address1 VARCHAR(80) = NULL OUTPUT
,@City1 VARCHAR(40) = NULL OUTPUT
,@State1 VARCHAR(40) = NULL OUTPUT
,@PostalCode1 VARCHAR(20) = NULL OUTPUT
,@Address2 VARCHAR(80) = NULL OUTPUT
,@City2 VARCHAR(40) = NULL OUTPUT
,@State2 VARCHAR(40) = NULL OUTPUT
,@PostalCode2 VARCHAR(20) = NULL OUTPUT
EXEC dbo.GeocodeGetLatLonDistanceBetweenPoints
'Capitol Driveway Northwest'
,'Washington'
,'DC'
,'20004'
,'500 Lee Avenue #5228'
,'Fort Lee'
,'VA'
,'23801'
EXEC dbo.GeocodeGetLatLonDistanceBetweenPoints
NULL
,NULL
,NULL
,'20004'
,NULL
,NULL
,NULL
,'23801'
*/
END
GO
July 15, 2013 at 10:02 pm
Steven Willis (7/15/2013)
Here's a stored procedure that calls the Google geocoding service which will work on full address or a zip code (center of zone) returning the lat/lon for the location. The second procedure lets you enter an origin and destination address (or zip) and returns the miles between the two points based on the lat/lon returned by the first procedure.
Haven't tried it but very cool. That bad boy just found a home in my briefcase. Thanks, Steven.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2013 at 1:17 am
August 23, 2013 at 10:06 am
countryflora13 (8/23/2013)
So, where do we have to put this script I mean can I put it in my website or what? please explain little more
One thing I use it for is on store location forms. The user submits an address or zip code on a form and the parameters are passed to the procedure(s). The first procedure returns the latitude and longitude. In the case of a store locator I usually have a table of locations which has each location's lat/lon. But you can also take the lat/lon and using the Google or other mapping service have the API display the location in real time.
So for example, the end-user enters an address. The first procedure gives you their lat/lon which you could use for comparing to a predefined list of locations. Then take the submitted address as Address1 and compare it to the address of locations within a certain number of miles to create a descending list of locations from the submitted address.
Here's another example that plots the location of entries on the form (this is from my dev site...the live campaign was in 2011).
Lowe's Million Tree Giveaway Earth Day 2011[/url]
August 23, 2013 at 11:10 am
countryflora13 (8/23/2013)
So, where do we have to put this script I mean can I put it in my website or what? please explain little more
Considering the generic nature of this post and the fact that this OP called a stored procedure a "script", I'd be real surprised if this post isn't just clever spam. I guess we'll see.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2013 at 4:58 pm
Does this give you miles "as the crow flies" or does it give driving miles? I just want to be clear.
-Nevermind I see it now
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply