добавить координаты к адресам

18.06.202318.06.2023 admin 0 Comments

Получение координат по адресам, через сервис Google API

Программа позволяет загрузить координаты для списка адресов в таблице Excel

Исходными данными выступает столбец с адресами объектов (город, улица, номер дома)

Для уменьшения вероятности бана со сторону Google (из-за большого количества запросов),
можно задать паузу (интервал) между запросами (от 0 до 60 секунд)

К ячейкам с адресами объектов добавляются гиперссылки, позволяющие открыть найденную в Google информацию по объекту.

Есть также решение для ЯндексКарт, — если нужно, пишите на почту, прикрепив к письму файл с адресами, и указав, куда надо вставлять координаты

работает на win10, но не работает на Win7, хотя excel с одного установщика. На mac os в Excel16 (2020) не работает, к сожалению. Пишет нет данных, как и в Win7/ Может ест у кого решение для google docs?

Есть база яндекса в эксель с гео координатами (долгота и широта) 5000 компаний, нужно найти индексы. Сколько стоит чтобы вы мне выслали такой файл уже с индексами?

Теперь без ключа из Гугла геоданные тянуть невозможно. R.I.P.

Приобрести файл без ограничений можно, оплатив мне 1000 руб удобным вам способом:
http://excelvba.ru/payments
А насчет координат Москвы, — это уж какие вы адреса пропишете, те координаты и получите после запуска макроса.
(или наоборот, адреса по координатам)

Нужен полнофункциональный файл с координатами Москвы. Как можно приобрести?

добрый день попробовал, версия еще рабочая? выдает координаты но немного не те

Мои макросы работают только в Excel под Windows
На Mac можно пользоваться под Parallels

Добрый день! На mac, версия excel 15.27 пишет: Ошибка компиляции в скрытом модуле: modMain. Как правило, эта ошибка возникает, если код несовместим с версией, платформой или архитектурой приложения. Выходит, файл несовместим с excel на mac? Или проблема в другом?

да
тот же макрос, но без ограничения по количеству строк

Если я покупаю эту программу то вы нам отправляете другой документ без ограничений?

Если покупаю, то номер адресов неограниченный? или все же есть ограничения, на пример вставить в документ 10000 адресов

Сколько стоит программа GoogleMapsAPI? работа в ексэль с адресами и координатами,

Отличная программа, вот только если вариант получения координат в системе координат Геотреста по Москве для последующего перевода в программу Автокада.

Скрипт не перетирает старые координаты в столбцах LON и LAT, из-за этого получилась подстава. Плохо. Недоработано.

Так и не получил от Вас информации по стоимости аналога с Яндексом.

Игорь, пожалуйста на пишите сколько будет стоить под заказ для Яндекса?

Нет, этот макрос только для Google
Для Яндекса по-другому делается (можем сделать под заказ). Но там может капча выскакивать (запрос текста с картинки)

Здравствуйте! Мне очень понравился Ваш макрос. Подскажите, с яндекс картами может ли работать данный макрос?

Макрос кажется частично решает мою задачу. Могу ли я с его помощью автоматически считать расстояние между двумя разными адресами по полученным координатам?

Есть набор функций и макросов Excel, определяющих по адресу координаты, полную адресную строку, а также ближайшие станции метро по яндекс картам. Пишите, если интересно.

Здравствуйте, Александр.
Нет, никакой поддержки не предоставляется
(и даже не могу гарантировать, что за денежку исправлю)

Скажите, если я куплю данный файл, могу ли я рассчитывать на бесплатную корректировку макроса в случае изменений в сервисе google?

Источник

Геокодирование. Как привязать 250 тысяч адресов к координатам за 10 минут?

В этой статье я хотел бы поделиться опытом решения маленькой проблемы с большим количеством адресов. Если вы когда-либо работали с API геокодирования или пользовались онлайн инструментами, то думаю вы разделяете мою боль ожидания результата в течение нескольких часов, а то и больше.

Речь идет не о сложных алгоритмах оптимизации, а об использовании сервиса пакетного геокодирования, который принимает на вход список адресов и возвращает файл с результатами. Тем самым можно сократить время обработки с нескольких часов до минут.

Обо всем по порядку:

Предыстория

Пакетное геокодирование

Выбор провайдера пакетного геокодера

Гайд по работе с сервисом на Python

Полное описание класса Batch

Анализ результатов

Предыстория

Поступила задача – “Привязать к координатам 24 тысячи адресов”. В голову пришло только два варианта решения проблемы:

Следовательно нужно использовать REST API геокодера для написания собственного скрипта, с сохранением результатов (это не совсем легальный способ и нужно читать условия использования сервиса). Возникает новая проблема – одно дело когда мы используем адресный поиск в приложении и сразу получаем результат, но когда стоит задача обработать более десяти тысяч адресов с сохранением, работа скрипта сильно затягивается. Можно подождать час или два, но миллион адресов придется геокодировать “необъятное время”, поэтому нужно искать другое решение и оно есть!

Крупные провайдеры геолокационных сервисов, помимо обычного сервиса геокодирования, предлагают пакетный геокодер (Batch Geocoder), как раз для того чтобы за один запрос выполнить обработку большого количества адресов.

Пакетное геокодирование

Название сервиса говорит само за себя – у нас имеется пакет (например csv файл со списком адресов в виде таблицы), который мы загружаем на сервер, и он делает всю работу за нас.

Процесс пошагово выглядит так:

Следующий шаг – это выбор компании с наиболее лояльными условиями использования пакетного геокодера. Во-первых, далеко не у всех такой сервис есть, другие позволяют протестировать работу сервиса с серьезным ограничением. Также если у вас очень большие объемы, нужно обратить внимание на стоимость дополнительных транзакций, в случае превышения лимита бесплатного пакета.

Выбор провайдера сервиса пакетного геокодирования

На мировом рынке геолокационных сервисов лидирующие позиции занимают:

Я взял за основу следующие параметры для выбора провайдера:

Google Maps

Для того чтобы начать работу с геосервисами Google, первым делом требуется добавить в свой аккаунт информацию о банковской карте. Ежемесячный лимит 200 виртуальных долларов, далее идет оплата дополнительных транзакций с привязанной карты. В рамках этого лимита можно пользоваться различными сервисами, но транзакции у каждого считаются по-разному. Например одна тысяча запросов на геокодирование будет стоить 5$, но сервис построения маршрутов стоит в два раза дороже. Более детально можно ознакомиться на сайте, нас интересует лишь сервис геокодирования.

Если 200$ в месяц, то несложно посчитать бесплатное количество транзакций – 40 000 (сервис геокодирования). Пакетный геокодер среди сервисов отсутствует. Это значит, что придется писать собственный скрипт и результат будет примерно 1 адрес в секунду, а это шесть часов для 24 тысяч адресов. Чтобы ускорить процесс, можно попробовать запустить скрипт на платформе Google Cloud APIs, но я решил поискать альтернативные решения. Ограничений по количеству транзакций в день нет, поэтому все сорок тысяч можно потратить за один раз.

HERE Technologies

В прошлом Nokia Maps и в еще более глубоком прошлом Navteq, дает каждый месяц 250 тысяч транзакций бесплатно. Аналогично Google Maps это количество распространяется на все сервисы и каждый считается по-разному. При использовании бесплатного пакета, банковскую карту привязывать не надо. Если вы превысили лимит, то за каждую дополнительную тысячу транзакций необходимо заплатить 1$.

Важно наличие пакетного геокодера как отдельного сервиса, который входит в бесплатный план. Транзакции в нем учитываются по той же модели что и в обычном, то есть каждый адрес в файле, пакетный геокодер будет воспринимать за одну транзакцию.
По названию статьи понятно, что я использовал пакетный геокодер HERE, так как можно потратить все транзакции на геокодер и выполнить 250 000 операций геокодирования в месяц. Но это не единственная опция, поэтому смотрим, что есть у других компаний.

MapBox

При использовании геокодера MapBox, доступно 100 тысяч транзакций в месяц. Компания придерживается той же модели монетизации с оплатой дополнительных транзакций. Только есть интересная опция для “оптовиков” – чем больше у вас транзакций, тем меньше они стоят (конечно есть лимит снижения цены). Например от 100 тыс. до 500 тыс. дополнительная тысяча запросов будет стоить 0.75$, от 500 тыс. до 1 млн – 0.60$ и тд., подробнее можно ознакомиться на сайте. К сожалению пакетный геокодер доступен только в платном аккаунте.

TomTom

Платформа дает возможность выполнить 2500 транзакций в день, примерно 75 000 в месяц. При тестировании и разработке, ограничение по дням выглядит не очень привлекательно по сравнению с конкурентами, но оплата дополнительных транзакций наиболее гибкая. Имеется 8 вариантов оплаты дополнительной тысячи запросов и цена снижается с 0.5$ до 0.42$.

Среди сервисов есть пакетный геокодер с возможностью обработать до 10 тысяч адресов за один запрос (однако надо учитывать ограничение в день).

Yandex Technologies

Модель с ограничением транзакций по дням и у Yandex, но более лояльная 25 тысяч запросов. Если умножить это число на количество дней в месяце, то получиться внушительная цифра – 750 тысяч. На сайте представлены цены за дополнительную тысячу транзакций в рублях варьируются от 120 руб. до 11 руб.

Пакетный геокодер как сервис не представлен, поэтому добиться какой-то оптимизации не получится.

Очень заманчивый бесплатный план с 1 миллионом транзакций в месяц. Также компания начисляет каждому аккаунту 50 кредитов (примерный эквивалент 5$). Стоит отметить, что это самый лояльный план по использованию геолокационных сервисов. Также имеется сервис пакетного геокодирования, но воспользоваться им можно только при наличии корпоративного аккаунта на платформе ArcGIS Online.

Что в итоге выбрать?

Проще всего сделать выбор составив небольшую таблицу:

В итоге мой выбор пал на HERE так как для решения моей задачи это оптимальный вариант. Конечно, я сделал далеко не полный анализ, в идеале нужно прогнать свой датасет через все геокодеры для оценки качества. Плюс если у вас несколько миллионов адресов, стоит задуматься о платном пакете и тогда нужно брать во внимание стоимость доп. транзакций.

Цель статьи не сравнение компаний, а решение проблемы оптимизации геокодирования большого объема адресов. Я лишь показал ход своих мыслей при выборе провайдера сервиса.

Гайд по работе с сервисом на Python

Для начала необходимо создать аккаунт на портале для разработчиков и сгенерировать в разделе проектов REST API KEY.

Теперь можно работать с платформой. Я опишу лишь часть функционала, которым располагает пакетный геокодер HERE: загрузка данных, проверка статуса, сохранение результатов.

Итак, начнем с импорта необходимых библиотек:

Далее если не возникло ошибок, создаем класс:

То есть при инициализации классу необходимо передать собственный ключ для REST API.
Переменная SERVICE_URL – это базовый URL для работы с сервисом пакетного геокодирования.
И в jobId будет храниться идентификатор текущей работы геокодера.

Важный пункт – правильная структура данных при запросе. Файл должен содержать две обязательные колонки: recId и searchText. В противном случае сервис вернет ответ с информацией об ошибке при загрузке.

Вот пример датасета:

Функция для загрузки файла в облако:

Все достаточно просто, открываем на чтение файл со списоком адресов, формируем словарь параметров GET запроса. Некоторые параметры стоит объяснить:

Следующий шаг – проверка статуса работы. Запрос формируется аналогичным образом, только необходимо передать Id операции. Параметр action должен содержать значение “status”. Функция __stats выводит в консоль полную статистику для оценки времени завершения работы геокодера.

Одна из самых главных функций – сохранение результата. Для удобства работы лучше сразу разархивировать файл, который приходит с сервера. Запрос на сохранение файла идентичен проверке статуса просто в конце добавляем /result.

Финальная функция для парсинга ответа сервиса. Также ее задача состоит в том, чтобы сохранить идентификатор текущей задачи геокодирования:

Для тестирования работы достаточно запустить Python интерпретатор в папке со скриптом. Класс Batch находится в файле geocoder.py:

Отлично работа началась. Проверим статус:

Мы видим что обработка датасета завершена. Всего за семь минут удалось прогеокодировать 250 тысяч адресов (без учета ошибок — errorcount). Осталось сохранить результаты:

Полное описание класса Batch

Я думаю, что не помешает добавить скрипт полностью:

Анализ результатов

В итоге я прошел путь от медленно работающих онлайн приложений до сервисов пакетного геокодирования. Выбор провайдера геосервисов полностью зависит от задач, которые стоят перед вами. У меня регулярно появляются запросы на обработку большого количества адресов и подход описанный в статье помог существенно сократить время.

Надеюсь, что данная статья будет полезна и конечно я открыт к комментариям и дополнениям!

Источник

Как наладить поиск адреса по координатам (и где взять нужный справочник)

Весной мы добавили в API DaData.ru фичу «Обратное геокодирование», она же «Адрес по координатам». Название намекает: метод принимает геокоординаты и отдает данные об адресе.

Солидный продукт с той же функциональностью предлагает «Яндекс» — он называется «Геокодер». Но сервис «Яндекса» бесплатен только для открытых некоммерческих проектов. Стандартный же тариф — от 120 000 ₽ в год — подходит не всем.

Мы подумали — если сделать бесплатную или недорогую альтернативу «Геокодеру», разработчики наверняка скажут спасибо. И сделали. В статье расскажу, как устроен «Адрес по координатам»: как мы наладили поиск, собрали справочник и упаковали в готовый метод.

Где берем данные и чем ищем адрес

Подступаясь к задаче, мы изучили готовые решения: где взять справочник координат с адресами и как потом искать по этому справочнику географические объекты. Оказалось, за нужными инструментами даже не придется далеко ходить.

Адресные объекты берем в ФИАС — Федеральной информационной адресной системе. Это самый полный из открытых и официальных адресных справочников. Подробно о нем мы уже писали на «Хабре», а сейчас важны четыре факта:

Координаты загружаем из OpenStreetMap (OSM). OSM — проект со свободной лицензией: энтузиасты собирают координаты всевозможных объектов и выкладывают для всех желающих.

Если по-простому, OSM — это набор точек, линий и полигонов на карте. У каждого объекта свое описание, тип и набор координат. Данные OSM для России лежат по адресам needgeo.com, osm.sbin.ru/osm_dump/ и osmosis.svimik.com/latest/.

_{Список источников публикуют на специальной странице в «Вики» проекта}

Выгрузки состоят из PBF-файлов — этот формат используют вместо XML как более компактный. Превратить PBF в OSM XML ничего не стоит, с этим справится куча одобренных сообществом утилит.

Для собственного справочника мы берем адресные объекты из ФИАС, а затем ищем их координаты в OSM. Если нашли, сохраняем объединенные данные. Получается такое пересечение ФИАС и OSM.

И все это замечательно, но есть одна проблема: с качеством данных в OSM дела обстоят непросто. Координаты объектов часто не соответствуют реальности. Например, полигоны для регионов и районов адекватны. А для городов и ниже — уже не очень.

_{Полигоны — это многоугольники, ограничивающие площади на карте. Они состоят из связанного набора точек с координатами. Полигонами обозначают границы регионов, районов, городов и даже зданий}

Основная работа, и с большим отрывом — собрать из OSM адекватные данные и отсеять брак. Задача настолько объемная, что я отвел под нее в статье отдельный раздел.

Дома, которых нет в ФИАС, загружаем тоже из OSM. Выше я уже говорил, что в ФИАС отсутствуют десятки тысяч домов. Это даже не проблема, а просто реальность, фон. Поэтому мы пополняем свой справочник домами из OSM. Но только теми, для которых в ФИАС существует улица. У пришедших из OSM зданий нет ФИАС ID, поэтому мы идентифицируем их как ФИАС-код родителя + номер дома.

По справочнику ищем с помощью прекрасного Lucene — нашего многолетнего помощника. За наводку спасибо сведущему индийцу, написавшему пост Indexing Geographical Data With Lucene (хорошее дополнение — материал A dive into spatial search algorithms — о k-d-деревьях, на которых построен алгоритм поиска).

Как только мы узнали об у́дали Lucene, проблема с поиском решилась почти сама. Делов осталось — пройтись наждачкой.

Как собрали базу координат и адресов

Для начала выложу багаж: прочитав статью, быстро сделать подобный справочник не получится. Мы собираем его с 2014 года, постоянно дополняя. Об этом чертовски длинном пути я и расскажу.

Самое сложное при составлении справочника — перебрать кординаты, которые пришли из OSM. На старте мы выверяли их как могли, в том числе руками. Главная цель тогда — получить опорные точки в крупных городах и сделать из них эталонный справочник. Теперь, когда таких точек много, проверять новые данные вручную почти не приходится. За раз мы добавляем в эталонный справочник 200 000–300 000 адресов с координатами, и вот как это делаем.

Формируем из OSM-тегов полные адреса́. В OSM-выгрузках составные части адресов разбросаны по разным тегам:

Прогоняем каждый новый адрес через API стандартизации «Дадаты». Сервис приводит адреса к единому формату «Как в ФИАС»:

142718, Московская обл, Ленинский р-н, с Булатниково, ул Центральная, д 103.

Адрес	ФИАС ID	Широта	Долгота
142718, Московская обл, Ленинский р-н, с Булатниково, ул Центральная, д 103	a8b6a52f-e96d-4ec3-a0ff-641013ab0445	55.558773	37.667103
119034, г Москва, пер Турчанинов, д 6 стр 2	8c925e61-9173-48b3-999e-dc85c86d89e7	55.737096	37.597190

Разбираем адреса, которые «Дадата» не стандартизовала. Адреса́, которые не получилось сопоставить с ФИАС, сервис помечает флажком. Их проверяем вручную, вариантов здесь несколько.

Проверяем, насколько адекватны загруженные координаты. Для этого специальной утилитой смотрим, попадают ли координаты нового объекта в полигон родительского региона или района. Если адрес сообщает, что объект находится в Омской области, будь добр по координатам попадать в ее полигон. Вхождения в город не требуем — не все города точно освещены в OSM, для многих данные не обновляют.

Эталонные полигоны мы загружаем из OSM и храним как есть — в формате GeoJSON. Чтобы выбрать, к какому полигону примерить точку, смотрим в отдельную таблицу. В ней мы сопоставили префиксы КЛАДР-кодов и ID полигонов: находишь для адреса КЛАДР-код и видишь, какой полигон выбрать.

_{КЛАДР-код — это уникальный идентификатор, который использовали еще до появления ФИАС. Находить этот код для адреса умеет миллион сервисов}

Утилита разрешает объекту отстоять от положенного полигона на 1 700 метров. Это правило добавили из-за шоссе, которые часто выходят за границы региона. Но расстояние больше 1 700 метров — признак ошибки, так говорит статистика.

На этом для городов и улиц проверка заканчивается.

Еще раз, построже, проверяем загруженные координаты домов. В дело снова вступает упомянутая утилита, и вот что она делает.

Проверку проходят только дома, которые удалены от надежных коллег не более чем на 150 метров. Причем каждый новый одобренный дом мы учитываем при разборе следующих. Вот как это работает.

Допустим, в эталонном справочнике хранятся дома № 1, 2 и 3 по улице Коммунаров. В новых данных пришли дома № 5, 6 и 7 по той же улице. Судя по координатам, новые дома стоят рядом. Утилита видит, что дом № 5 находится рядом с домами № 1, 2 и 3 и добавляет его в эталонный справочник. Значит, дома № 6 и 7 тоже проходят проверку.

А дальше решается судьба пришедших из OSM данных:

_{В первой таблице — все объекты c ФИАС ID до домов: регионы, населенные пункты, улицы. Во второй — дома́ и ссылка на родителя из первой таблицы}

Две таблицы нужны, чтобы назначить ключи отсутствующим в ФИАС домам. У них нет собственного ФИАС-кода, поэтому делаем вот как:

Справочник готов, осталось протестировать. За ночь прогоняем сервис по функциональным тестам и тестируем производительность. Скорость проверяем на Москве, запрашивая все дома в радиусе трех километров. Чтобы уж наверняка. Конечно, обложили все автотестами.

Главное после обновления — чтобы не стало хуже.

Обратное геокодирование глазами пользователя

На вход метод принимает три параметра: координаты, количество результатов и радиус поиска. Радиус по умолчанию — 100 метров, максимальный — километр. Точное значение задают в настройках.

Обратно метод возвращает найденные объекты: дома, улицы и населенные пункты. При этом сортирует их по убыванию точности.

После всех этих рокировок метод наконец-то возвращает объекты, которые нашел.

Внутри — много разного о найденных объектах: строки с полным и сокращенным адресом, актуальное и устаревшие названия, почтовый индекс, ФИАС-код родительского объекта и так далее.

_{Все данные, которые отдает метод — в документации}

Покрытие по координатам для разных регионов разное, вот так с домами:

Уже думаем, что добавить к методу: разрешить фильтрацию по типам объектов, возвращать расстояния до заданной точки, еще что-нибудь. Следим за спросом и решаем, вкладывать ли силы.

А в остальном все уже на проде. До 10 000 запросов в сутки — бесплатно, больше — по подписке от 5 000 ₽ в год. Если нужны адреса по координатам для коммерческого проекта, а «Геокодер» слишком дорог — попробуйте API «Дадаты».

Оригинал статьи опубликован в блоге HFLabs.

Источник

Добавить координаты к адресам

Для начала необходимо создать таблицу и занести в нее адреса, для которых требуется определить координаты. Например, назовем ее addresses.

Внимание. Если у вас есть таблица, содержащая адреса, которые необходимо геокодировать, то вы можете добавить в нее поле coords и перейти к настройке скрипта.

Таблица имеет три поля:

Далее заполните таблицу адресами, например, так:

INSERT INTO `addresses` VALUES (1, ‘Москва, ул. Льва Толстого, 16’, »);

INSERT INTO `addresses` VALUES (2, ‘Санкт-Петербург, Свердловская набережная, 44’, »);

INSERT INTO `addresses` VALUES (3, ‘Екатеринбург, ул. Хохрякова, 10’, »);

INSERT INTO `addresses` VALUES (4, ‘Новосибирск, ул. Красноярская, 35’, »);

INSERT INTO `addresses` VALUES (5, ‘Казань, ул. Спартаковская 6, 11’, »);

INSERT INTO `addresses` VALUES (6, ‘Киев, ул. Луначарского, 4’, »);

INSERT INTO `addresses` VALUES (7, ‘Одесса, пр. Шевченко, дом 4-Д’, »);

INSERT INTO `addresses` VALUES (8, ‘Симферополь, ул. Героев Аджимушкая, 6/13’, »);

INSERT INTO `addresses` VALUES (9, ‘абракадабра’, »);

mysql_select_db($config[‘dbname’]) or die(‘Невозможно выбрать БД с именем \»‘.$config[‘dbname’].’\»: ‘.mysql_error()

mysql_query(‘SET NAMES utf8’);

// Выборка данных из таблицы

$addresses = mysql_query(‘SELECT * FROM ‘.$config[‘tablename’]) or die(‘Ошибка при выполнении запроса к таблице \»‘.$config[‘tablename’].’\»: ‘.mysql_error()

// Общее количество адресов и количество адресов, в обработке которых произошла ошибка

while ($row = mysql_fetch_assoc($addresses)) <

// Обращение к http-геокодеру

// Если геокодировать удалось, то записываем в БД

mysql_query(\»UPDATE `<$config['tablename']>` SET coords = ‘\».mysql_real_escape_string($coords).\»‘ WHERE or die(\»Ошибка при обновлении данных в таблице: \».mysql_error()

// Закрытие соеденинения с сервером

// Вывод общего количество прогеокодированных результатов

Перед запуском скрипта необходимо прописать ваши параметры доступа к БД, а также указать API-ключ (получить ключ можно с помощью специальной формы

‘apikey’ => ‘ВАШ API-КЛЮЧ’

После внесения изменений выполните скрипт.

В окно браузера будет выведены найденные координаты и сообщения об ошибках, если некоторые адреса прогеокодировать не удалось. Также скрипт обновит записи в таблице, добавив найденные координаты.

Рассмотренный скрипт демонстрирует пример геокодирования списка адресов. Вы можете его модернизировать и изменять по своему усмотрению.

Если у вас возникли вопросы при использовании предложенного скрипта, то вы их всегда можете задать в нашем клубе разработчиков.

В данной статье будет рассмотрен вариант получения координат для списка адресов с использованием PHP и MySQL. Полученные координаты можно будет использовать для отображения на карте списка ваших объектов (например, местоположение офисов компании, пользователей и пр.). Если вы хотите обрабатывать массивы адресов на клиенте, обратите внимание на статью «Множественное геокодирование».