Nearby - Search

Search for places within a specified area using SQL Server

Prerequisites:

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

Stored Procedure:


CREATE PROCEDURE usp_Google_Places_Nearby_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,
		@rankby nvarchar(4000) = NULL,
		@type nvarchar(4000) = NULL,
		@pagetoken nvarchar(MAX) = 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/nearbysearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'location', @location,
				      'radius', @radius,
				      'keyword', @keyword,
				      'language', @language,
				      'minprice', @minprice,
				      'maxprice', @maxprice,
				      'name', @name,
				      'opennow', @opennow,
				      'rankby', @rankby,
				      'type', @type,
				      'pagetoken', @pagetoken,
				      '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_Nearby_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,

@rankby nvarchar(4000) = NULL,

@type nvarchar(4000) = NULL,

@pagetoken nvarchar(MAX) = 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/nearbysearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'location', @location,

'radius', @radius,

'keyword', @keyword,

'language', @language,

'minprice', @minprice,

'maxprice', @maxprice,

'name', @name,

'opennow', @opennow,

'rankby', @rankby,

'type', @type,

'pagetoken', @pagetoken,

'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_NearBy_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)
			DECLARE @pagetoken nvarchar(MAX)

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

			--Continue calling the above stored procedure with the @pagetoken parameter
			--to retrieve additional records (when not NULL)

			IF @status <> 'OK'
				BEGIN
					RAISERROR('@Status', 16, 1)
					RETURN
				END
			
			SELECT  T.C.value(N'name[1]', N'nvarchar(MAX)') AS [name]
				   ,T.C.value(N'vicinity[1]', N'nvarchar(MAX)') AS [vicinity]
				   ,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'rating[1]', N'nvarchar(MAX)') AS [rating]
				   ,T.C.value(N'opening_hours[1]/open_now[1]', N'nvarchar(MAX)') AS [open_now]
				   ,T.C.value(N'price_level[1]', N'nvarchar(MAX)') AS [price_level]
			FROM @X.nodes(N'/PlaceSearchResponse/result') T(C)
		END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_NearBy_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)

DECLARE @pagetoken nvarchar(MAX)

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

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

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

--Continue calling the above stored procedure with the @pagetoken parameter

--to retrieve additional records (when not NULL)

IF @status <> 'OK'

BEGIN

RAISERROR('@Status', 16, 1)

RETURN

END

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

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

,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'rating[1]', N'nvarchar(MAX)') AS [rating]

,T.C.value(N'opening_hours[1]/open_now[1]', N'nvarchar(MAX)') AS [open_now]

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

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

END

name vicinity lat lng rating open_now price_level

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

Thai Soon Restaurant 101 South Coit Road #401, Ri... 32.9500462 -96.7684809 4.5 true 2

Chipotle Mexican Grill 14715 North Coit Road, Dalla... 32.9497096 -96.7694931 4.0 true 1

Pizza Hut Spring Creek Village Shop Ct... 32.9532383 -96.7694419 3.1 true 1



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.