Using SQL to read from a web service

Recently, I needed to find correct addresses for physical locations that I had only latitude and longitude.   The general process to do this follows:

script to PROVE OUT the process for generating good addresses from JEA bad addresses thru the provided lat/long
(assumes, hopes the lat/long are good)
1. TMP table: Create a table of the addresses, lat and long
2. XY PROJECTION: create CTE using function on each row of tmp table to fill in XY projection
3. XY PROJECTION–>URL: create CTE with URL w/XY
4. CALL FOR RE: Using prior CTE… create CTE using function on each row to fill in new (correct) RE
Here’s the code for step 2… (below that code you’ll find code for step 3…then a script to show how to tie the two together…

--	Steve Schneider
ALTER function [dbo].[fn_LatLongtoXY]
(    @sUrl varchar(8000)      )
returns varchar(100)
as
/*
	Steve Schneider 11 Dec 2015
	This code from: http://stackoverflow.com/questions/17407338/how-can-i-make-http-request-from-sql-server

	declare @ret varchar(max) = null;
	exec  @ret = [dbo].fn_LatLongtoXY 'http://maps.coj.net/coj/rest/services/Utilities/Geometry/GeometryServer/project?inSR=4326&outSR=3857&geometries=-81.69716%2C30.17355%2C-81.700439%2C30.178105&transformation=&transformForward=true&f=pjson'
	select @ret

*/
BEGIN
    DECLARE @obj int 
    DECLARE @hr  int 
    DECLARE @msg varchar(8000)    

---------------------------SET THINGS UP ----------------------------------------------------
    exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
		if @hr <> 0 begin set @Msg = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' return @Msg end

    exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
		if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end

    exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
		if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end

    exec @hr = sp_OAMethod @obj, send, NULL, ''
		if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

---------------------------MAKE THE CALL ----------------------------------------------------
    EXEC @hr=sp_OAGetProperty @Obj,'ResponseText',@msg OUTPUT	--   <--THAT'S THE CALL TO THE SERVICE!!!
		IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj

    exec @hr = sp_OADestroy @obj

---------------------------PULL ONLY WHAT I WANT INTO THE RETURN------------------------------
    RETURN SUBSTRING(@msg, 
				CHARINDEX('x',@msg)+4,
				CHARINDEX(',',@msg)-CHARINDEX('x',@msg) -4) 
		+ ',' + replace(replace( rtrim (SUBSTRING(@msg, 
				CHARINDEX('y',@msg)+4,
				CHARINDEX('}',@msg,CHARINDEX('y',@msg))-CHARINDEX('y',@msg)-4)) ,CHaR(10),''),CHaR(13),'')

--remove carriage return/linefeed from the return...
--replace(replace(coords,CHaR(10),''),CHaR(13),'')
    eh:
    exec @hr = sp_OADestroy @obj
    return @msg
END  --end of function definition

Here’s the code for step 3…

ALTER function [dbo].[fn_XYtoAddress]
(    @sUrl varchar(8000)       )
	returns varchar(100) as
/*
	Steve Schneider 11 Dec 2015
	This originally came from: http://stackoverflow.com/questions/17407338/how-can-i-make-http-request-from-sql-server

	declare @ret varchar(max) = null;
	exec  @ret = [dbo].[fn_XYtoAddress] 'http://maps.coj.net/coj/rest/services/Geocode/WGS_COMPOSITE_i2t/GeocodeServer/reverseGeocode?location=-9083460.87728%2C3550385.02826&distance=&langCode=&outSR=&returnIntersection=false&f=pjson'
	select @ret
*/
BEGIN
    DECLARE @obj int 
    DECLARE @hr  int 
    DECLARE @msg varchar(8000)    

---------------------------SET THINGS UP ----------------------------------------------------
    exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
		if @hr <> 0 begin set @Msg = 'sp_OACreate MSXML2.ServerXMLHttp.3.0 failed' return @Msg end
    exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
		if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
    exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
		if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end
    exec @hr = sp_OAMethod @obj, send, NULL, ''
		if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end

---------------------------MAKE THE CALL ----------------------------------------------------
    EXEC @hr=sp_OAGetProperty @Obj,'ResponseText',@msg OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj

    exec @hr = sp_OADestroy @obj
---------------------------PULL ONLY WHAT I WANT INTO THE RETURN------------------------------
	return  rtrim (SUBSTRING(@msg, 
				CHARINDEX('street',@msg)+10,
				CHARINDEX(',',@msg)-CHARINDEX('street',@msg) -11) 
		+ '==' + rtrim (SUBSTRING(@msg, 
				CHARINDEX('"zip":',@msg)+8,5) )  )
--				CHARINDEX('Loc_name',@msg,CHARINDEX('zip',@msg))-CHARINDEX('zip',@msg)-4)) as Y
    eh:
    exec @hr = sp_OADestroy @obj
    return @msg
END

Lastly, tie them together:
-- Code snippet to show use of two functions using  Web services...
-- drop table #httpxy
-- Create a tablel with a lat/long, and a URL of a web service that takes in lat/long, returning an XY plane
 -- 1. 
select 
	jAddress, 
	lat, long,
	'http://maps.coj.net/coj/rest/services/Utilities/Geometry/GeometryServer/project?inSR=4326&outSR=3857&geometries='
			+ convert(varchar(22),long) + ',' + convert(varchar(22),lat) + '&transformation=&transformForward=true&f=pjson' as HttpXY
into #httpxy
from PAMO.JEA_Shutoff2 
where	RealEstateNumber is null					--and jaddress like '%bunker%'
		and datepart(year,lastServiceDate) = 2014 

-- 2.    Change from Lat/long to XY in the row...   --------------------------------------------------------------------------------

;with 
XY as (
	select 
		jAddress , 
		[dbo].fn_LatLongtoXY ( HttpXY) as coords 
	from #httpxy
)

-- 3.    --convert the XY to http calls...   ----------------------------------------------------------------------------------------

, httpforAddress as (
	select 
		jAddress, 
		'http://maps.coj.net/coj/rest/services/QScend/QScend_Location/MapServer/0/query?where=1%3D1&text=&objectIds=&time=&geometry='+ coords + '&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=RE&returnGeometry=false&returnTrueCurves=false&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&resultOffset=&resultRecordCount=&f=pjson'  as httpcallforAddress
	from xy
)

-- 4.    --Make the web calls, getting a realestate#...   ----------------------------------------------------------------------------------------
,JEAmushed as (
	select 
		jAddress, 
		dbo.fn_XYtoRealEstateNumber (httpcallforAddress) as REplottedGIS
	from httpforAddress
)
--select * from JEAmushed
, JEAready as (
	select 
		jAddress,
		replace(REplottedGIS,' ','') REplottedGIS 
	from JEAmushed  
	where REplottedGIS not like 'RE"%' 
)
update PAMO.JEA_Shutoff2 	 
	set RealEstateNumber = replace (' ', '',jea.REplottedGIS) , srce_re = 'jea=GISWAIparcel_REplot'
--select *
from	PAMO.JEA_Shutoff2 j inner join 
		JEAready jea on j.jAddress = jea.jAddress
where  j.RealEstateNumber is null and datepart(year,lastServiceDate) = 2014  --order by 3


--select * from JEAready

About Steve Schneider

I was born (again) in June of 1983 when I came to know Jesus Christ as my saviour. You too, can know Him, and know Peace. We all have sinned, and fallen short of the grace of God. But, God demonstrates His love for us in this, that while we were still sinners, He died for us. Email me, and I can show you how to know Jesus. He is highly worth knowing!
This entry was posted in SQL 2012 and tagged , . Bookmark the permalink.

Leave a Reply...Really! I'll listen!