Place Predictions - Fetch

Retrieve Google Places place predictions 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_Place_Autocomplete_Fetch(
		@Profile varchar(100),
		@input nvarchar(4000),
		@offset int = NULL,
		@location nvarchar(100) = NULL,
		@radius int = NULL,
		@language nvarchar(5) = NULL,
		@types nvarchar(4000) = NULL,
		@components nvarchar(4000) = NULL,
		@strictbounds 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/autocomplete/xml'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'input', @input,
				      'offset', @offset,
				      'location', @location,
				      'radius', @radius,
				      'language', @language,
				      'types', @types,
				      'components', @components,
				      'strictbounds', @strictbounds,
				      '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_Place_Autocomplete_Fetch(

@Profile varchar(100),

@input nvarchar(4000),

@offset int = NULL,

@location nvarchar(100) = NULL,

@radius int = NULL,

@language nvarchar(5) = NULL,

@types nvarchar(4000) = NULL,

@components nvarchar(4000) = NULL,

@strictbounds 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/autocomplete/xml'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'input', @input,

'offset', @offset,

'location', @location,

'radius', @radius,

'language', @language,

'types', @types,

'components', @components,

'strictbounds', @strictbounds,

'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_Place_Autocomplete_Fetch
		@Profile = 'My Google Places',
		@input = 'Amoeba',
		@location = '37.76999,-122.44696',
		@types = 'establishment',
		@radius = 500,
		@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, 'AutocompletionResponse' --
			--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse/prediction' --

			DECLARE @status nvarchar(MAX)

			SELECT  @status = T.C.value(N'status[1]', N'nvarchar(MAX)')
			FROM @X.nodes(N'/AutocompletionResponse') 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'id[1]', N'nvarchar(MAX)') AS [id]
			       ,T.C.value(N'description[1]', N'nvarchar(MAX)') AS [description]
			       ,T.C.value(N'place_id[1]', N'nvarchar(MAX)') AS [place_id]
			       ,T.C.value(N'term[1]/value[1]', N'nvarchar(MAX)') AS [value]
			       ,T.C.value(N'term[1]/offset[1]', N'nvarchar(MAX)') AS [offset]
			FROM @X.nodes(N'/AutocompletionResponse/prediction') T(C)
		END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Place_Autocomplete_Fetch

@Profile = 'My Google Places',

@input = 'Amoeba',

@location = '37.76999,-122.44696',

@types = 'establishment',

@radius = 500,

@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, 'AutocompletionResponse' --

--EXEC SQLHTTP.net.XQueryHelper @X, 'AutocompletionResponse/prediction' --

DECLARE @status nvarchar(MAX)

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

FROM @X.nodes(N'/AutocompletionResponse') 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'id[1]', N'nvarchar(MAX)') AS [id]

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

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

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

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

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

END

id description place_id value offset

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

2358a6f08845... Amoeba Music, Haight Street,... ChIJ5YQQf1GHhYARPKG7... Amoeba Music 0

8a40726d6983... Amoeba Music, Telegraph Avenu... ChIJr7uwwy58hYARBY-e... Amoeba Music 0

8512d4f54c17... Amoeba Music, Sunset Boulevard.. ChIJRdmfADq_woARYaVh... Amoeba Music 0

57f91ab3d03d... Amoeba, Jayanagar East, Jaya... ChIJgVZhUr0VrjsRMfKx... Amoeba 0



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.