Text - Search - SQLHTTP

Text - Search

Retrieve information about a set of places based on a string 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_Text_Search(	
		@Profile varchar(100),
		@query nvarchar(4000),
		@region nvarchar(5) = NULL,
		@location nvarchar(100) = NULL,
		@radius int = NULL,
		@language nvarchar(5) = NULL,
		@minprice tinyint = NULL,
		@maxprice tinyint = NULL,
		@opennow 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/textsearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'query', @query,
				      'region', @region,
				      'location', @location,
				      'radius', @radius,
				      'language', @language,
				      'minprice', @minprice,
				      'maxprice', @maxprice,
				      'opennow', @opennow,
				      '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_Text_Search(

@Profile varchar(100),

@query nvarchar(4000),

@region nvarchar(5) = NULL,

@location nvarchar(100) = NULL,

@radius int = NULL,

@language nvarchar(5) = NULL,

@minprice tinyint = NULL,

@maxprice tinyint = NULL,

@opennow 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/textsearch/xml'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'query', @query,

'region', @region,

'location', @location,

'radius', @radius,

'language', @language,

'minprice', @minprice,

'maxprice', @maxprice,

'opennow', @opennow,

'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_Text_Search
		@Profile = 'My Google Places',
		@query = 'Los Angeles movie studios',
		@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'formatted_address[1]', N'nvarchar(MAX)') AS [formatted_address]
					,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]
			FROM @X.nodes(N'/PlaceSearchResponse/result') T(C)
		END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Google_Places_Text_Search

@Profile = 'My Google Places',

@query = 'Los Angeles movie studios',

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

,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]

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

END

name formatted_address lat lng

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

Sunset Las Palmas Studios 1040 N Las Palmas Ave, Los Angeles, CA ... 34.0896960 -118.3350490

Paramount Pictures Studi... 5515 Melrose Ave, Los Angeles, CA 90038... 34.0837186 -118.3209528

Fox Studios 10201 W Pico Blvd, Los Angeles, CA 9006... 34.0517900 -118.4125106



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.