Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Google Geocoding API + SQL  [new]
Mamkin_proger
Member

Откуда:
Сообщений: 2
Добрый день.
Вопрос достаточно тривиальный, но я новичок, поэтому не могу справиться без сторонней помощи - с синтаксисом беда.
Есть некая таблица [dbo].[temp_geo], в которой есть строковые [adr],[formatted_adr],[postal_code],[place_id].
[adr] - адреса в произвольном формате, остальные значения нужно заполнить.
Картинка с другого сайта.

Есть написанная процедура [dbo].[GeoCode], которая скармливает [adr] Google API и возвращает [formatted_adr],[postal_code],[place_id].
Картинка с другого сайта.

Процедуру полностью описывать не буду, суть:
[dbo].[GeoCode] @InputAdr varchar (max)
/*отправляем запрос гуглу, получаем ответ*/
SELECT @RespAddr as formatted_adr, @PosCode as postal_code, @PlaceID as place_id

Вопрос - как мне дополнить данными, получаемыми процедурой, таблицу [dbo].[temp_geo]?
Не пойму, как в коде процедуры написать update. В Excel-VBA пользуюсь этим скриптом давно, там это делается циклом быстро и прекрасно.
Спасибо заранее.
27 июн 19, 11:59    [21916063]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Mamkin_proger,

update tbl1 set f1 = @var1, f2 = @var2, f3 = @var3
from sourceTable1
where sourceTable1.key = tbl1.key
27 июн 19, 12:16    [21916087]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Думал об одном, написал другое...

update tbl1 set f1 = sourceTable1.var1, f2 = sourceTable1.var2, f3 = sourceTable1.var3
from sourceTable1
where sourceTable1.key = tbl1.key
27 июн 19, 12:18    [21916088]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
В стандартных T-SQL запросах (SELECT, INSERT, DELETE, UPDATE), практически всегда, в тех местах, где скалярное значение пожно подставить:
- константу
- скалярную переменную (Ваш случай)
- подзапрос, возвращающий скалярное значение

Т.е. пишите обычный UPDATE, который меняет ваши данные, а на месте VALUES подставляете вместо констант ваши переменные
27 июн 19, 12:18    [21916089]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Yuri Abele
... а на месте VALUES ...

Тьфуты! Я имел в виду в SET выражении.
Но Вам уже выше написали
27 июн 19, 12:20    [21916092]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Yuri Abele
Но Вам уже выше написали

Не, не совсем:

UPDATE dbo.temp_geo
SET
    formatted_adr = @RespAddr,
    postal_code = @PosCode,
    place_id = @PlaceID
WHERE
    [adr] = @InputAdr


Но вам тут очень сильно не зватает нормального ключевого поля
27 июн 19, 12:25    [21916096]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Mamkin_proger
Member

Откуда:
Сообщений: 2
Yuri Abele,
Всё равно не пойму. Вот процедура [dbo].[GeoCode]:
+

DECLARE
--	@InputAdr varchar (max),--адрес должен передаваться в процедуру из запроса
	@Response varchar (8000),
	@URL nvarchar(255),
	@XML xml,
	@Obj int,
	@Result int,
	@HTTPStatus int,
	@GoogleAPIKey varchar (max),
-- ниже переменные для ответа API, их должна возвращать процедура
	@RespStatus varchar(10),
	@RespAddr varchar(255),
	@PosCode varchar(6),
	@Country varchar(20),
	@Region varchar(255),
	@City varchar(255),
	@Street varchar(255),
	@BldNumb varchar(6),
	@GPSLatitude varchar(255),
	@GPSLongitude varchar(255),
	@PlaceID varchar(255)

--SET @InputAdr = 'Москва Колокольников пер 11'
SET @GoogleAPIKey = 'ключгугла'
SET @InputAdr = Replace(Replace(Replace(Replace(@InputAdr, ' ', '+'), '"', '+'), '<', '+'), '>', '+')
SET @URL = concat('https://maps.googleapis.com/maps/api/geocode/xml?address=',@InputAdr,'&language=ru&key=',@GoogleAPIKey)

EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT
-- разбираем ответ гугла
SET @XML = CAST(@Response AS XML)
SET @RespStatus = @XML.value('(/GeocodeResponse/status) [1]', 'varchar(255)')
SET @RespAddr = @XML.value('(/GeocodeResponse/result/formatted_address) [1]', 'varchar(255)')
SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type = "country"]/long_name) [1]', 'varchar(255)')
SET @PosCode = @XML.value('(/GeocodeResponse/result/address_component[type = "postal_code"]/long_name) [1]', 'varchar(255)')
SET @Region = @XML.value('(/GeocodeResponse/result/address_component[type = "administrative_area_level_1"]/long_name) [1]', 'varchar(255)')
SET @City = @XML.value('(/GeocodeResponse/result/address_component[type = "locality"]/long_name) [1]', 'varchar(255)')
SET @Street = @XML.value('(/GeocodeResponse/result/address_component[type = "route"]/long_name) [1]', 'varchar(255)')
SET @BldNumb = @XML.value('(/GeocodeResponse/result/address_component[type = "street_number"]/long_name) [1]', 'varchar(255)')
SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'varchar(255)')
SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'varchar(255)')
SET @PlaceID = @XML.value('(/GeocodeResponse/result/place_id) [1]', 'varchar(255)')
 
SELECT @RespAddr as formatted_adr, @PosCode as postal_code, @PlaceID as place_id

В какое место я должен там апдейт ставить? Она же работает с одним значением, переданным в переменной. А в таблице, допустим, 100 адресов. В Excel я использовал цикл, а что сделать здесь?

Надеюсь на понимание, спасибо.
27 июн 19, 13:25    [21916168]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
Самой последней строчкой.

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

P.S.
Но, вообще, я бы, как архитектор, только без обид, за такое руки оторвал!

Мы на проектах тоже Google GEO API используем. Но не внутри базы данных!!!
Вот то место, откуда это все из Middle Layer (что у вас там - .NET, Access VBA, etc.) вызывается, вот оттуда и дёргать внешний сервис.
В "нормальных" инфраструктурах Productive серверы, особенно RDBMS, вообще выхода в Internet не имеют.

Вот GEO расширения MSSQL использовать, это дело! Например, чтобы получить из базы показывать на карте только те точки, которые в видимый регион попадать. У нас тысячи таких точек, а показываться за раз, хорошо если десяток будет, поэтому такое очень актуально.
27 июн 19, 15:26    [21916315]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
И еще раз - вам остро необходимо нормальное (INT, BIGINT или GUID) ключевое поле.
Не стоит использовать Business Information в роли Primary Key
http://lissianski.narod.ru/zamyslov/sk1.html
27 июн 19, 15:33    [21916325]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
court
Member

Откуда:
Сообщений: 1956
Mamkin_proger
Вот процедура [dbo].[GeoCode]:
переделай ХП на табл.функцию, и всё станет на порядок проще

update a
SET
    a.formatted_adr = b.RespAddr,
    a.postal_code = b.PosCode,
    a.place_id = b.PlaceID
from [dbo].[temp_geo] a
cross apply [dbo].[GeoCode] (a.adr) b
/* where a.place_id is null */


------
а с ХП придётся "крутить" cursor, сбрасывать результат ХП во времянку и обновлять [dbo].[temp_geo] из неё
И всё это по одной записи, в цикле курсора
27 июн 19, 16:08    [21916374]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
court
переделай ХП на табл.функцию

Не выдет, у него EXEC внутри, да еще и не один
27 июн 19, 17:55    [21916481]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Yuri Abele,

Поддержу в плане совать это в sql.слой в appl или любой шедулер а в базе только хранение
27 июн 19, 21:40    [21916643]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
court
Member

Откуда:
Сообщений: 1956
Yuri Abele
court
переделай ХП на табл.функцию

Не выдет, у него EXEC внутри, да еще и не один
spOA вроде можна
27 июн 19, 23:57    [21916672]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
court
Yuri Abele
пропущено...

Не выдет, у него EXEC внутри, да еще и не один
spOA вроде можна

Да само слово EXEC уже запрещено
28 июн 19, 13:29    [21917005]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
court
Member

Откуда:
Сообщений: 1956
Yuri Abele
court
пропущено...
spOA вроде можна

Да само слово EXEC уже запрещено
брэхня :)

create FUNCTION [dbo].[fnHabaHaba]
(
	@txt		varchar(4000)		
)
RETURNS @t table (res varchar(100)) 
as
begin
	declare @Result varchar(8000)
	declare @object int, @object2 int
	declare @hr int

	set @txt='<body><script>document.write(' + @txt + ');</script></body>'

	exec @hr=sp_OACreate 'HTMLfile', @object out
	exec @hr=sp_OAMethod @object, 'write', null, @txt
	exec @hr=sp_OAGetProperty @object, 'body', @object2 out 
	exec @hr=sp_OAGetProperty @object2, 'innerText', @Result out 

	exec @hr=sp_OADestroy @object2
	exec @hr=sp_OADestroy @object
	
	insert into @t values (@Result)
	
	return 
end


select * from [dbo].[fnHabaHaba]('100000+500')

res
----------------------------------------------------------------------------------------------------
100500

(затронута одна строка)
28 июн 19, 13:43    [21917027]     Ответить | Цитировать Сообщить модератору
 Re: Google Geocoding API + SQL  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
О блин! Аж мурашки по спине! Не дай бог присниться такое :-)
1 июл 19, 17:06    [21918371]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить