Radar - Search - SQLHTTP

Radar - Search

Retrieve Google Places by radius using SQL Server

Prerequisites:

See SQLHTTP easy setup for Google Places
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Google_Places_Radar_Search(
		@Profile varchar(100),
		@location nvarchar(100),
		@radius int,
		@keyword nvarchar(4000) = NULL,
		@language nvarchar(5) = NULL,
		@minprice tinyint = NULL,
		@maxprice tinyint = NULL,
		@name nvarchar(4000) = NULL,
		@opennow nvarchar(4000) = NULL,
		@type nvarchar(4000) = NULL,
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @URL nvarchar(MAX)
DECLARE @QueryString varchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://maps.googleapis.com/maps/api/place/radarsearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'location', @location,
				      'radius', @radius,
				      'keyword', @keyword,
				      'language', @language,
				      'minprice', @minprice,
				      'maxprice', @maxprice,
				      'name', @name,
				      'opennow', @opennow,
				      'type', @type,
				      'key', '#APIKey'

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'GET',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_Google_Places_Radar_Search(

@Profile varchar(100),

@location nvarchar(100),

@radius int,

@keyword nvarchar(4000) = NULL,

@language nvarchar(5) = NULL,

@minprice tinyint = NULL,

@maxprice tinyint = NULL,

@name nvarchar(4000) = NULL,

@opennow nvarchar(4000) = NULL,

@type nvarchar(4000) = NULL,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @URL nvarchar(MAX)

DECLARE @QueryString varchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://maps.googleapis.com/maps/api/place/radarsearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'location', @location,

'radius', @radius,

'keyword', @keyword,

'language', @language,

'minprice', @minprice,

'maxprice', @maxprice,

'name', @name,

'opennow', @opennow,

'type', @type,

'key', '#APIKey'

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Radar_Search 
		@Profile = 'My Google Places',
		@location = '32.9500462,-96.7684809',
		@radius = 500,
		@type = 'restaurant',
		@Response = @Response OUTPUT,
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	IF SQLHTTP.net.IsXmlValid(@Response) = 0
		BEGIN
			RAISERROR(@Response, 16, 1)
			RETURN
		END
	ELSE
		BEGIN

			DECLARE @X xml
			SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))

			--The XPath syntax below was easily generated by executing the following commands:
			--EXEC SQLHTTP.net.XQueryHelper @X
			--and then executing: 
			--EXEC SQLHTTP.net.XQueryHelper @X, 'PlaceSearchResponse' --
			--EXEC SQLHTTP.net.XQueryHelper @X, 'PlaceSearchResponse/result' --

			DECLARE @status nvarchar(MAX)

			SELECT  @status = T.C.value(N'status[1]', N'nvarchar(MAX)')
			FROM @X.nodes(N'/PlaceSearchResponse') T(C)

			IF @status <> 'OK'
				BEGIN
					RAISERROR('@Status', 16, 1)
					RETURN
				END
			
			SELECT  T.C.value(N'id[1]', N'nvarchar(MAX)') AS [id]
			       ,T.C.value(N'place_id[1]', N'nvarchar(MAX)') AS [place_id]
			       ,T.C.value(N'geometry[1]/location[1]/lat[1]', N'nvarchar(MAX)') AS [lat]
			       ,T.C.value(N'geometry[1]/location[1]/lng[1]', N'nvarchar(MAX)') AS [lng] 
			       ,T.C.value(N'reference[1]', N'nvarchar(MAX)') AS [reference]
			FROM @X.nodes(N'/PlaceSearchResponse/result') T(C)

		END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Radar_Search

@Profile = 'My Google Places',

@location = '32.9500462,-96.7684809',

@radius = 500,

@type = 'restaurant',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

IF SQLHTTP.net.IsXmlValid(@Response) = 0

BEGIN

RAISERROR(@Response, 16, 1)

RETURN

END

ELSE

BEGIN

DECLARE @X xml

SET @X = CONVERT(xml, CONVERT(varchar(MAX), @Response))

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'PlaceSearchResponse' --

--EXEC SQLHTTP.net.XQueryHelper @X, 'PlaceSearchResponse/result' --

DECLARE @status nvarchar(MAX)

SELECT @status = T.C.value(N'status[1]', N'nvarchar(MAX)')

FROM @X.nodes(N'/PlaceSearchResponse') T(C)

IF @status <> 'OK'

BEGIN

RAISERROR('@Status', 16, 1)

RETURN

END

SELECT T.C.value(N'id[1]', N'nvarchar(MAX)') AS [id]

,T.C.value(N'place_id[1]', N'nvarchar(MAX)') AS [place_id]

,T.C.value(N'geometry[1]/location[1]/lat[1]', N'nvarchar(MAX)') AS [lat]

,T.C.value(N'geometry[1]/location[1]/lng[1]', N'nvarchar(MAX)') AS [lng]

,T.C.value(N'reference[1]', N'nvarchar(MAX)') AS [reference]

FROM @X.nodes(N'/PlaceSearchResponse/result') T(C)

END

id place_id lat lng reference

--------------------- ---------------------------- ---------- ------------ -------------------

34e0f55c2a87bea38... ChIJefEUqskhTIYR9DR-h11qUrU 32.9489231 -96.7684847 CmRSAAAAeModlU3N...

70adb0a350a4e6d70... ChIJsSwQBMkhTIYR7emEbtHvIyw 32.9482360 -96.7683920 CmRRAAAA7FXtIF83...

41f237c338f4b61fb... ChIJsSwQBMkhTIYReX4iXc93JTY 32.9481170 -96.7683908 CmRRAAAAfa7femV9...



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.