<jupyter_start><jupyter_text>Геокодирование адресов*<NAME> 2021, <EMAIL>* >Геокодеры Visicom & Nominatim 1. Создайте книгу **excel**, формат файла **[.xlsx]**2. На первом листе книги, в ячейке 'A1' напишите имя заголовка: **location**3. Начиная с ячейки A2 ... An. внесите без пропусков строк, адреса для геокодирования>Желательный формат адреса: название населенного пункта (**разделитель запятая**) название области>>**Пример:** Анадоль, ДОНЕЦКАЯ Видео-инструкция как использовать геокодер, ниже под этой записью<jupyter_code>from IPython.display import HTML

HTML("""
    <iframe width="800" height="400"
        src="https://www.youtube.com/embed/vpg4aDxmfnU?playlist=vpg4aDxmfnU&loop=1">
    </iframe>
""")<jupyter_output><empty_output><jupyter_text>Скачайте пример файла с адресами<jupyter_code>import doexample
doexample.start("resources/addr.test")<jupyter_output><empty_output><jupyter_text>Нажмите на кнопку и загрузите файл<jupyter_code>import ipywidgets as widgets

uploader = widgets.FileUpload(
    accept='.xlsx',  # Accepted file extension e.g. '.txt', '.pdf', 'image/*', 'image/*,.pdf'
    multiple=False  # True to accept multiple files upload else False
)
uploader<jupyter_output><empty_output><jupyter_text>Предпросмотр загруженных адресов<jupyter_code>import io
import openpyxl
from pandas import ExcelFile

excel_data = ExcelFile(io.BytesIO(uploader.data[-1]), engine='openpyxl')
dataframe = excel_data.parse(excel_data.sheet_names[0])
dataframe<jupyter_output><empty_output><jupyter_text>Скрипт геокодирования для загруженного списка адресовСкорость геокодирования 1сек для 1 адреса, скорость ограничена сервером Nominatim<jupyter_code>import os
import io
import re
import json
import time
import datetime

import pytz
from dateutil.tz import tzutc, tzlocal

import requests
from requests import cookies

import openpyxl
from pandas import ExcelFile

import geocoder

import ipysheet
from ipywidgets import HTML
from ipyleaflet import Map, Marker, Popup
from tqdm.notebook import tqdm, trange


center = (50.4547, 30.5238)
show_map = Map(center=center, zoom=6, close_popup_on_click=False)

GOOGLE_API_KEY = None
sheet = ipysheet.sheet(rows=len(dataframe.index), columns=4)
sheet.column_headers = ['index', 'location', 'latitude', 'lontitude']

headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.0'}


class Epoch():

    def __init__(self):
        self.tzutc = tzutc()  # Время в UTC
        self.tzlocal = tzlocal()  # Локальное время
        self.winter_summer = time.daylight  # зимнее-летнее время
        self.__UNIX_EPOCH = datetime.datetime(1970, 1, 1, 0, 0,
                                              tzinfo=pytz.utc)
        self.complex_data = None

    def run(self):
        self.__year = self.complex_data[0]
        self.__month = self.complex_data[1]
        self.__day = self.complex_data[2]
        self.__hour = self.complex_data[3]
        self.__minute = self.complex_data[4]
        self.__second = self.complex_data[5]
        self.__microsecond = 0
        # Create local time
        naive = datetime.datetime(self.__year, self.__month,
                                  self.__day, self.__hour,
                                  self.__minute, self.__second,
                                  self.__microsecond,
                                  tzinfo=tzlocal())
        # Convert local time to UTC
        self.utc_datetime = naive.astimezone(pytz.utc)
        self.delta = self.utc_datetime - self.__UNIX_EPOCH
        self.seconds = self.delta.total_seconds()
        self.ms = self.seconds * 1000

        return self.seconds


def begin_get():
    start_UNIX_EPOCH = datetime.datetime(1970, 1, 1, 0, 0, tzinfo=pytz.utc)
    naive = datetime.datetime.now()
    utc_datetime = naive.astimezone(pytz.utc)
    delta = utc_datetime - start_UNIX_EPOCH
    seconds = delta.total_seconds()
    ms = int(seconds * 1000)
    ms = '1581407448242'

    jar = cookies.RequestsCookieJar()
    jar.set('timestamps', str(ms))
    jar.set('lang', 'ru')

    url = 'https://maps.visicom.ua/c/34.41184,47.49205,16/f/ADR3JRG6CVRUKHNPU3'
    payload = {'lang': 'ru'}

    ret = requests.get(url, headers=headers, params=payload, cookies=jar, timeout=60)
    assert ret.status_code == 200
    res = ret.content.decode('utf-8')

    return ms, url


def geocode_visicom(adr, ms, ref):
    decode_json = None
    name_error = None
    url = 'https://maps.visicom.ua/ru/fts'
    payload = {'q': adr,
               'c': '34.37622,48.83218,7',
               'co': 'ua'}

    jar = cookies.RequestsCookieJar()
    jar.set('_ga', "GA1.2.1366087715.1580981745")
    jar.set('_gid', "GA1.2.1572761778.1580981745")
    jar.set('_gat', '1')
    jar.set('timestamp', str(ms))
    jar.set('lang', 'ru')

    headers_two = headers
    headers_two['Referer'] = ref
    err = "200 OK"

    try:
        ret = requests.get(url, headers=headers_two, params=payload, cookies=jar, timeout=60)
        assert ret.status_code == 200
        res = ret.content.decode('utf-8')
        decode_json = json.loads(res)

    except requests.ConnectionError as ex:
        name_error = 'Ошибка подключения: ' + str(ex)
    except requests.Timeout as ex:
        name_error = 'Ошибка, время ожидания истекло: ' + str(ex)
    except AssertionError as ex:
        name_error = 'Ошибка 403 (Forbidden)'
    except Exception as ex:
        print(str(ex))
    finally:
        return decode_json, name_error


def get_free_visicom(adr):
    ms, ref = begin_get()
    # adr = 'Украина, Вишневое, <NAME>, 12'
    result, name_error = geocode_visicom(adr, ms, ref)

    return result


def extract_lat_long_google_address(address_or_zipcode):
    lat, lng = None, None
    base_url = "https://maps.googleapis.com/maps/api/geocode/json"
    endpoint = f"{base_url}?address={address_or_zipcode}&key={GOOGLE_API_KEY}"
    r = requests.get(endpoint)
    if r.status_code not in range(200, 299):
        return None, None
    try:
        results = r.json()['results'][0]
        lat = results['geometry']['location']['lat']
        lng = results['geometry']['location']['lng']
    except Exception as ex:
        print(r.json()["error_message"])

    return lng, lat


def get_osm_lat_long(city):
    g = geocoder.osm(city)
    return g.json


def get_visicom_lat_long(raw_res):
    rs = None
    coordinates = None

    if raw_res:
        feat = raw_res['type']
        if 'Feature' == feat:
            feat = raw_res['properties']

            if 'settlement' in feat:
                rs = feat['settlement']

            if 'geo_centroid' in raw_res:
                geo_centroid = raw_res['geo_centroid']
                coordinates = geo_centroid['coordinates']

        elif 'FeatureCollection' == feat:
            feat = raw_res['features']
            box = feat[0]
            prop = box['properties']
            if 'settlement' in prop:
                rs = prop['settlement']
                rsname = prop['name']
                rsid = prop['settlement_id']

            if 'geo_centroid' in box:
                geo_centroid = box['geo_centroid']
                coordinates = geo_centroid['coordinates']

    return rs, coordinates


def add_marker_leaflet(jp, addr, lon, lat):
    
    global show_map
    
    marker = Marker(location=(lat, lon))
    show_map.add_layer(marker)
    
    message2 = HTML()
    message2.value = ', '.join([str(jp), addr])
    message2.placeholder = addr
    marker.popup = message2


def get_progress():
    
    global dataframe
    
    with trange(len(dataframe.index)) as pbar:
        pbar.container.children[-2].style.bar_color = '#003366'  # or 'yellow'
        for i in pbar:
            yield 0
    

def get_file_jupyter():

    global dataframe
    
    for index, row in dataframe.iterrows():
        yield [index, row]


def pull_jupyter(provider, current_provider, jpindex, jpdata):

    global sheet
    
    def additional_cells(jpval, addr, coord_lon, coord_lat):
        ipysheet.cell(jpval, 0, jpval)
        ipysheet.cell(jpval, 1, addr)
        ipysheet.cell(jpval, 2, coord_lat)
        ipysheet.cell(jpval, 3, coord_lon)
    
    res = {}
    coord_point = 'нет данных'
    location_address = jpdata['location']

    if current_provider == 'nominatim':
        res = get_osm_lat_long(location_address)

        if res:
            if 'lng' in res:
                lng = res['lng']
                lat = res['lat']
                coord_point = [lng, lat]
        else:
            current_provider = provider[1]

    if current_provider == 'visicom':
        raw_res = get_free_visicom(location_address)
        res, coordinates = get_visicom_lat_long(raw_res)
        coord_point = coordinates

    if current_provider == 'google':
        res = extract_lat_long_google_address(location_address)
        if res:
            lng = res[0]
            lat = res[1]
            coord_point = [lng, lat]

    if coord_point:
        
        additional_cells(jpindex, location_address, coord_point[0], coord_point[1])
        add_marker_leaflet(jpindex, location_address, coord_point[0], coord_point[1])
        
    else:
        additional_cells(jpindex, location_address, coord_point, coord_point)
        print(jpindex, location_address, coord_point, coord_point, sep=' | ')

    time.sleep(1)


if __name__ == "__main__":

    provider = ('visicom', 'nominatim', 'google')

    br = get_progress()
    
    print('Geocoding started..')
    
    for b in get_file_jupyter():
        next(br)
        pull_jupyter(provider, provider[0], b[0], b[1])
    
    try:
        next(br)
    except Exception:
        pass
        
    print('Done..')<jupyter_output>Geocoding started..<jupyter_text>Выделите данные в таблице и скопируйте через Ctrl+C<jupyter_code>sheet<jupyter_output><empty_output><jupyter_text>Отображение координат на картеДля просмотра адреса нажмите на **маркер**<jupyter_code>show_map<jupyter_output><empty_output><jupyter_text>Скачать координаты в CSV, UTF-8, delimiter ';'<jupyter_code>import base64
from IPython.display import FileLink

def create_download_link(df, title = "Скачать координаты в CSV file ", filename = "data.csv"):  
    csv = df.to_csv(sep=';', encoding='utf-8-sig', index=False)
    b64 = base64.b64encode(csv.encode('utf-8-sig'))
    payload = b64.decode()
    html = '<a download="{filename}" href="data:text/csv;base64,{payload}" target="_blank">{title}</a>'
    html = html.format(payload=payload,title=title,filename=filename)
    return HTML(html)

new_df = ipysheet.to_dataframe(sheet)
new_df.pop('index')

create_download_link(new_df)<jupyter_output><empty_output><jupyter_text>Скачать координаты в Excel .xlsx<jupyter_code>import base64
from IPython.display import FileLink
from pandas import ExcelFile, ExcelWriter

def create_download_link_excel(df, title = "Скачайте координаты адресов в Excel file", filename = "coords.xlsx"):  
    output = io.BytesIO()
    writer = ExcelWriter(output, engine='openpyxl')
    df.to_excel(writer, sheet_name='data', index=False)
    writer.save()
    
    excel_data = output.getvalue()
    b64 = base64.b64encode(excel_data)
    payload = b64.decode()
    
    html = '<a download="{filename}" href="data:text/xml;base64,{payload}" target="_blank">{title}</a>'
    html = html.format(payload=payload,title=title,filename=filename)
    
    return HTML(html)

new_df = ipysheet.to_dataframe(sheet)
new_df.pop('index')

create_download_link_excel(new_df)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11751392757660167, 'fraction_numerical': 0.022893454038997216, 'mean_word_length': 3.0755587087619722, 'pattern_counts': {'":': 1, '<': 45, '<?xml version=': 0, '>': 49, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '659174', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4216, 'n_tokens_neox': 4071, 'n_words': 812, 'path': 'index.ipynb', 'repo_name': 'sharkevolution/requirements', 'size': '11513'}