· 6 years ago · Oct 06, 2019, 09:46 PM
1USE [API_handling]
2GO
3/****** Object: StoredProcedure [dbo].[CallGoogleAPI] Script Date: 10/6/2019 6:08:09 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: <Author,,Name>
10-- Create date: <Create Date,,>
11-- Description: <Description,,>
12-- =============================================
13
14/*
15 exec dbo.CallGoogleAPI
16*/
17ALTER PROCEDURE [dbo].[CallGoogleAPI]
18 -- Add the parameters for the stored procedure here
19-- <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
20-- <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
21AS
22BEGIN
23 -- SET NOCOUNT ON added to prevent extra result sets from
24 -- interfering with SELECT statements.
25 SET NOCOUNT ON;
26
27 -- Insert statements for procedure here
28
29
30 Declare @tempDestination as Nvarchar(100);
31 Declare @tempOrigins as Nvarchar(100);
32 Declare @Destination as Nvarchar(50);
33 Declare @Origin as Nvarchar(max);
34 Declare @LocCode as Nvarchar(20);
35 Declare @FromCountry as Nvarchar(2);
36 Declare @FromPostal as Nvarchar(10);
37 Declare @ToCountry as Nvarchar(2);
38 Declare @ToPostal as Nvarchar(10);
39 Declare @toCoordinate as Nvarchar(100);
40 Declare @fromCoordinate as Nvarchar(100);
41 DECLARE @key nvarchar(100) = 'AIzaSyAH0EVfuhgSbggYGWM_mlJmU9t_I2ugX7s';
42 Declare @url as nvarchar(400);
43 Declare @Object as Int;
44 Declare @ResponseText as Varchar(8000);
45 Declare @toResponseText as Varchar(8000);
46 Declare @fromResponseText as Varchar(8000);
47 DECLARE @OrigCoordinate geography; -- Declare as POINT()
48 DECLARE @DestCoordinate geography; -- Declare as POINT()
49 Declare @tempLong nvarchar(100);
50 declare @tempLat nvarchar(100);
51 Declare @checkText nvarchar(50);
52
53 -- SELECT @source.STDistance(@target) Distance between two points
54
55 DECLARE dest_cursor CURSOR FOR
56 Select * from dbo.RDC_Input_to_Google
57
58
59
60 --Create table [googleResponse](
61 -- FromLocationCode nvarchar(100),
62 -- FromPostalCode nvarchar(100),
63 -- FromCountryCode nvarchar(100),
64 -- ToPostalCode nvarchar(100),
65 -- ToCountryCode nvarchar(100),
66 -- GoogleStatus nvarchar(100),
67 -- ResponseTo nvarchar(100),
68 -- ResponseFrom nvarchar(100),
69 -- GeoTo nvarchar(100),
70 -- GeoFrom nvarchar(100),
71 -- DistanceOnRoad nvarchar(100),
72 -- TimeOnRoad nvarchar(100),
73 -- [BirdDistance (km)] nvarchar(100)
74 --)
75 Open dest_cursor
76 FETCH NEXT FROM dest_cursor into @LocCode, @FromPostal, @FromCountry, @ToPostal, @ToCountry
77
78 WHILE @@FETCH_STATUS = 0
79 Begin
80 If Not exists (Select 1 from [dbo].[googleResponse] where [FromLocationCode] = @LocCode and [FromPostalCode] = @FromPostal and [FromCountryCode] = @FromCountry and [ToPostalCode] = @ToPostal and [ToCountryCode] = @ToCountry)
81 Begin
82 If exists (select [COUNTRYNAME] from dbo.CountryCodeTranslation where [TwoCharCountryCode] = @ToCountry) or exists(select [COUNTRYNAME] from dbo.CountryCodeTranslation where [TwoCharCountryCode] = @ToCountry)
83 Begin
84 Set @Destination = Replace(@ToPostal, ' ', '%20') + '+' + (SELECT TOP (1) [COUNTRYNAME] from dbo.CountryCodeTranslation where [TwoCharCountryCode] = @ToCountry)
85 Set @Origin = Replace(@FromPostal, ' ', '%20') + '+' + (SELECT TOP (1) [COUNTRYNAME] from dbo.CountryCodeTranslation where [TwoCharCountryCode] = @FromCountry)
86
87 -- Google maps according to routes
88
89 Set @URL = 'https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=' + @origin + '&destinations=' + @destination + '&key=' + @key
90
91
92 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
93 Exec sp_OAMethod @Object, 'open', NULL, 'get',
94 @url, --Your Web Service Url (invoked)
95 'false'
96 Exec sp_OAMethod @Object, 'send'
97 Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
98
99 Print 'Testing response text'
100 If (ISJSON(@ResponseText) = 0 )
101 Set @checkText = 'Non value'
102
103 Print 'Response Text: ' + @ResponseText
104
105
106 --Select @ResponseText
107
108 -- Generate coordinates for origin and destination
109
110 Set @url = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + @destination + '&key=' + @key
111
112 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
113 Exec sp_OAMethod @Object, 'open', NULL, 'get',
114 @url, --Your Web Service Url (invoked)
115 'false'
116 Exec sp_OAMethod @Object, 'send'
117 Exec sp_OAMethod @Object, 'responseText', @toResponseText OUTPUT
118
119 Print 'Testing to response text'
120 If (ISJSON(@toResponseText) = 0 )
121 Set @checkText = 'Non value'
122 Print 'To Response Text: ' + @toResponseText
123 --Select @toResponseText
124 Set @tempLat = JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lat');
125 Set @tempLong = JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lng');
126
127 --IF Cast(JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lat') as numeric(10,8)) >0.0
128 -- Set @tempLat = JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lat');
129
130 --Else
131 -- Set @tempLat = Null;
132
133 --If Cast(JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lng') as numeric(10,8)) >0.0
134 -- Set @tempLong = JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lng');
135
136 --Else
137 -- Set @tempLong = Null;
138
139 Set @DestCoordinate = geography::STGeomFromText('POINT(' + @tempLat + ' ' + @tempLong + ')', 4326);
140
141
142 Set @url = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + @Origin + '&key=' + @key
143
144
145 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
146 Exec sp_OAMethod @Object, 'open', NULL, 'get',
147 @url, --Your Web Service Url (invoked)
148 'false'
149 Exec sp_OAMethod @Object, 'send'
150 Exec sp_OAMethod @Object, 'responseText', @fromResponseText OUTPUT
151
152 Print 'Testing from response text'
153 If (ISJSON(@fromResponseText) = 0 )
154 Set @checkText = 'Non value'
155
156 Print 'From Response Text: ' + @fromResponseText
157 --Select @fromResponseText
158 Set @tempLat = JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lat');
159 Set @tempLong = JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lng');
160
161 --IF Cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lat') as numeric(10,8)) > 0.0
162 -- Set @tempLat = JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lat');
163 --Else
164 -- Set @tempLat = Null;
165
166 --If Cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lng') as numeric(10,8)) > 0.0
167 -- Set @tempLong = JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lng');
168 --Else
169 -- Set @tempLong = Null;
170
171 Set @OrigCoordinate = geography::STGeomFromText('POINT(' + @tempLat + ' ' + @tempLong + ')', 4326);
172 Set @ResponseText = @ResponseText + ' . Is empty?'
173
174 Print @LocCode
175 Print @FromPostal
176 Print @FromCountry
177 Print 'Error -2'
178 Print @ToPostal
179 Print 'Error -1'
180 Print @ToCountry
181 Print 'Error 0'
182 Print @ResponseText
183 Print ISJSON(@ResponseText)
184
185 Print ISJSON(JSON_QUERY(@ResponseText, '$.rows'))
186 PRINT ISJSON(JSON_QUERY(@ResponseText, '$.rows[0].elements'))
187 Print JSON_QUERY(@ResponseText, '$.rows[0].elements[0].status')
188 Print 'Error 1'
189 Print Cast(JSON_VALUE(@ResponseText, '$.destination_addresses[0]') as nvarchar(100))
190 Print 'Error 2'
191 Print Cast(JSON_VALUE(@ResponseText, '$.origin_addresses[0]') as nvarchar(100))
192 Print 'Error 3'
193 Print Cast(JSON_VALUE(JSON_QUERY( @toResponseText, '$.results[0].geometry.location'), '$.lat') as nvarchar(50)) + ' ' + cast(JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lng') as nvarchar(50))
194 Print 'Error 4'
195 Print Cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lat') as nvarchar(50)) + ' ' + cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lng') as nvarchar(50))
196 Print 'Error 5'
197 Print Cast(JSON_VALUE(JSON_QUERY(@ResponseText, '$.rows[0].elements[0]'),'$.distance.text') as nvarchar(100))
198 Print 'Error 6'
199 Print Cast(JSON_VALUE(JSON_QUERY(@ResponseText, '$.rows[0].elements[0]'),'$.duration.text') as nvarchar(100))
200 Print 'Error 7'
201 Print cast(@OrigCoordinate.STDistance(@DestCoordinate)/1000 as nvarchar(100))
202
203 If (@checkText != 'Non value')
204 Begin
205 INSERT INTO [googleResponse]
206 ([FromLocationCode] ,
207 [FromPostalCode],
208 [FromCountryCode] ,
209 [ToPostalCode] ,
210 [ToCountryCode] ,
211 [GoogleStatus],
212 [ResponseTo],
213 [ResponseFrom],
214 [GeoTo],
215 [GeoFrom],
216 [DistanceOnRoad] ,
217 [TimeOnRoad],
218 [BirdDistance (km)])
219 Values (@LocCode,
220 @FromPostal,
221 @FromCountry,
222 @ToPostal,
223 @ToCountry,
224 JSON_VALUE(JSON_QUERY(@ResponseText, '$.rows[0].elements[0]'),'$.status'),
225 Cast(JSON_VALUE(@ResponseText, '$.destination_addresses[0]') as nvarchar(100)),
226 Cast(JSON_VALUE(@ResponseText, '$.origin_addresses[0]') as nvarchar(100)),
227 Cast(JSON_VALUE(JSON_QUERY( @toResponseText, '$.results[0].geometry.location'), '$.lat') as nvarchar(50)) + ' ' + cast(JSON_VALUE(JSON_QUERY(@toResponseText, '$.results[0].geometry.location'), '$.lng') as nvarchar(50)),
228 Cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lat') as nvarchar(50)) + ' ' + cast(JSON_VALUE(JSON_QUERY(@fromResponseText, '$.results[0].geometry.location'), '$.lng') as nvarchar(50)),
229 Cast(JSON_VALUE(JSON_QUERY(@ResponseText, '$.rows[0].elements[0]'),'$.distance.text') as nvarchar(100)),
230 Cast(JSON_VALUE(JSON_QUERY(@ResponseText, '$.rows[0].elements[0]'),'$.duration.text') as nvarchar(100)),
231 cast(@OrigCoordinate.STDistance(@DestCoordinate)/1000 as nvarchar(100))
232 )
233 End
234 Set @tempLat = ''
235 Set @tempLong = ''
236 Set @ResponseText = ''
237 Set @toResponseText = ''
238 Set @fromResponseText = ''
239 Set @checkText = ''
240 End
241 End
242 Set @ResponseText = ''
243 FETCH NEXT FROM dest_cursor into @LocCode, @FromPostal, @FromCountry, @ToPostal, @ToCountry
244 End
245
246 Exec sp_OADestroy @Object
247 Close dest_Cursor
248 DEALLOCATE dest_cursor
249
250
251END