Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Google Geocoding API + SQL / 16 сообщений из 16, страница 1 из 1
27.06.2019, 11:59
    #39831046
Mamkin_proger
Mamkin_proger
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Добрый день.
Вопрос достаточно тривиальный, но я новичок, поэтому не могу справиться без сторонней помощи - с синтаксисом беда.
Есть некая таблица [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 пользуюсь этим скриптом давно, там это делается циклом быстро и прекрасно.
Спасибо заранее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 12:16
    #39831064
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Mamkin_proger,

Код: sql
1.
2.
3.
update tbl1 set f1 = @var1, f2 = @var2, f3 = @var3
from sourceTable1
where sourceTable1.key = tbl1.key
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 12:18
    #39831065
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Думал об одном, написал другое...

Код: sql
1.
2.
3.
update tbl1 set f1 = sourceTable1.var1, f2 = sourceTable1.var2, f3 = sourceTable1.var3
from sourceTable1
where sourceTable1.key = tbl1.key
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 12:18
    #39831066
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
В стандартных T-SQL запросах (SELECT, INSERT, DELETE, UPDATE), практически всегда, в тех местах, где скалярное значение пожно подставить:
- константу
- скалярную переменную (Ваш случай)
- подзапрос, возвращающий скалярное значение

Т.е. пишите обычный UPDATE, который меняет ваши данные, а на месте VALUES подставляете вместо констант ваши переменные
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 12:20
    #39831068
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri Abele... а на месте VALUES ...
Тьфуты! Я имел в виду в SET выражении.
Но Вам уже выше написали
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 12:25
    #39831072
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri AbeleНо Вам уже выше написали
Не, не совсем:

Код: sql
1.
2.
3.
4.
5.
6.
7.
UPDATE dbo.temp_geo
SET
    formatted_adr = @RespAddr,
    postal_code = @PosCode,
    place_id = @PlaceID
WHERE
    [adr] = @InputAdr



Но вам тут очень сильно не зватает нормального ключевого поля
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 13:25
    #39831128
Mamkin_proger
Mamkin_proger
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri Abele,
Всё равно не пойму. Вот процедура [dbo].[GeoCode]:
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
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 я использовал цикл, а что сделать здесь?

Надеюсь на понимание, спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 15:26
    #39831208
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Самой последней строчкой.

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

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

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

Вот GEO расширения MSSQL использовать, это дело! Например, чтобы получить из базы показывать на карте только те точки, которые в видимый регион попадать. У нас тысячи таких точек, а показываться за раз, хорошо если десяток будет, поэтому такое очень актуально.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 15:33
    #39831213
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
И еще раз - вам остро необходимо нормальное (INT, BIGINT или GUID) ключевое поле.
Не стоит использовать Business Information в роли Primary Key
http://lissianski.narod.ru/zamyslov/sk1.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 16:08
    #39831249
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Mamkin_progerВот процедура [dbo].[GeoCode]:переделай ХП на табл.функцию, и всё станет на порядок проще

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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] из неё
И всё это по одной записи, в цикле курсора
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 17:55
    #39831320
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
courtпеределай ХП на табл.функцию
Не выдет, у него EXEC внутри, да еще и не один
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 21:40
    #39831408
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri Abele,

Поддержу в плане совать это в sql.слой в appl или любой шедулер а в базе только хранение
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.06.2019, 23:57
    #39831427
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri Abelecourtпеределай ХП на табл.функцию
Не выдет, у него EXEC внутри, да еще и не одинspOA вроде можна
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.06.2019, 13:29
    #39831631
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
courtYuri Abeleпропущено...

Не выдет, у него EXEC внутри, да еще и не одинspOA вроде можна
Да само слово EXEC уже запрещено
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.06.2019, 13:43
    #39831641
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
Yuri Abelecourtпропущено...
spOA вроде можна
Да само слово EXEC уже запрещенобрэхня :)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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



Код: sql
1.
2.
3.
4.
5.
6.
7.
select * from [dbo].[fnHabaHaba]('100000+500')

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

(затронута одна строка)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.07.2019, 17:06
    #39832416
Yuri Abele
Yuri Abele
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Google Geocoding API + SQL
О блин! Аж мурашки по спине! Не дай бог присниться такое :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Google Geocoding API + SQL / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]