Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Zip code --> Distance in google maps Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 2:27 PM
Points: 1, Visits: 1
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?
Post #1473879
Posted Monday, July 15, 2013 5:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1473929
Posted Monday, July 15, 2013 10:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1473966
Posted Friday, August 23, 2013 1:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 1:03 AM
Points: 1, Visits: 3
So, where do we have to put this script I mean can I put it in my website or what? please explain little more

Countryflora Inc. 2010
Post #1487654
Posted Friday, August 23, 2013 10:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



 
Post #1487910
Posted Friday, August 23, 2013 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1487940
Posted Tuesday, August 27, 2013 4:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 10, Visits: 13
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
Post #1488988
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse