112 lines
4.4 KiB
Python
112 lines
4.4 KiB
Python
|
"""Import wholesalers from OpenStreetMap."""
|
||
|
|
from __future__ import annotations
|
||
|
|
|
||
|
|
import json
|
||
|
|
import time
|
||
|
|
import urllib.parse
|
||
|
|
import urllib.request
|
||
|
|
from typing import Any, Optional
|
||
|
|
|
||
|
|
from app.db import execute, fetch_one
|
||
|
|
|
||
|
|
OVERPASS_URL = "https://overpass.kumi.systems/api/interpreter"
|
||
|
|
|
||
|
|
WHOLESALE_BRANDS = [
|
||
|
|
"Sligro", "Hanos", "Makro", "Bidfood", "Metro", "Van Gelder",
|
||
|
|
"De Klok", "Hoogvliet Groothandel",
|
||
|
|
]
|
||
|
|
|
||
|
|
|
||
|
|
def _fetch(query: str) -> list[dict[str, Any]]:
|
||
|
|
data = urllib.parse.urlencode({"data": query}).encode()
|
||
|
|
req = urllib.request.Request(OVERPASS_URL, data=data, method="POST")
|
||
|
|
with urllib.request.urlopen(req, timeout=300) as resp:
|
||
|
|
payload = json.loads(resp.read().decode())
|
||
|
|
return payload.get("elements", [])
|
||
|
|
|
||
|
|
|
||
|
|
def _coords(el: dict[str, Any]) -> tuple[Optional[float], Optional[float]]:
|
||
|
|
if el.get("type") == "node":
|
||
|
|
return el.get("lat"), el.get("lon")
|
||
|
|
c = el.get("center") or {}
|
||
|
|
return c.get("lat"), c.get("lon")
|
||
|
|
|
||
|
|
|
||
|
|
def _normalize_pc(raw: Optional[str]) -> str:
|
||
|
|
if not raw:
|
||
|
|
return "0000AA"
|
||
|
|
c = raw.strip().upper().replace(" ", "")
|
||
|
|
return c if len(c) >= 6 else "0000AA"
|
||
|
|
|
||
|
|
|
||
|
|
def import_wholesalers() -> dict[str, Any]:
|
||
|
|
query = (
|
||
|
|
'[out:json][timeout:120];area["ISO3166-1"="NL"]->.nl;('
|
||
|
|
'node["shop"="wholesale"](area.nl);way["shop"="wholesale"](area.nl);'
|
||
|
|
'node["shop"="cash_and_carry"](area.nl);way["shop"="cash_and_carry"](area.nl);'
|
||
|
|
'node["wholesale"](area.nl);way["wholesale"](area.nl);'
|
||
|
|
');out center tags;'
|
||
|
|
)
|
||
|
|
elements: list[dict[str, Any]] = []
|
||
|
|
try:
|
||
|
|
elements = _fetch(query)
|
||
|
|
except Exception:
|
||
|
|
# Fallback: smaller per-brand queries
|
||
|
|
for brand in WHOLESALE_BRANDS[:4]:
|
||
|
|
q = (
|
||
|
|
f'[out:json][timeout:60];area["ISO3166-1"="NL"]->.nl;('
|
||
|
|
f'node["name"~"{brand}",i](area.nl);way["name"~"{brand}",i](area.nl);'
|
||
|
|
f');out center tags;'
|
||
|
|
)
|
||
|
|
try:
|
||
|
|
elements.extend(_fetch(q))
|
||
|
|
time.sleep(2)
|
||
|
|
except Exception:
|
||
|
|
continue
|
||
|
|
inserted = updated = 0
|
||
|
|
seen: set[str] = set()
|
||
|
|
for el in elements:
|
||
|
|
tags = el.get("tags") or {}
|
||
|
|
lat, lon = _coords(el)
|
||
|
|
if lat is None:
|
||
|
|
continue
|
||
|
|
external_id = f"osm:{el.get('type')}:{el.get('id')}"
|
||
|
|
if external_id in seen:
|
||
|
|
continue
|
||
|
|
seen.add(external_id)
|
||
|
|
name = tags.get("name") or tags.get("brand") or "Groothandel"
|
||
|
|
brand = tags.get("brand") or name.split()[0]
|
||
|
|
street = tags.get("addr:street") or ""
|
||
|
|
hn = tags.get("addr:housenumber") or ""
|
||
|
|
address = f"{street} {hn}".strip() or name
|
||
|
|
city = tags.get("addr:city") or tags.get("addr:town") or "Onbekend"
|
||
|
|
existing = fetch_one("SELECT id FROM wholesalers WHERE external_id = %s", (external_id,))
|
||
|
|
if existing:
|
||
|
|
execute(
|
||
|
|
"""UPDATE wholesalers SET name=%s, address=%s, postcode=%s, city=%s, province=%s,
|
||
|
|
latitude=%s, longitude=%s, phone=%s, email=%s, website=%s, last_updated=NOW()
|
||
|
|
WHERE external_id=%s""",
|
||
|
|
(
|
||
|
|
name[:255], address, _normalize_pc(tags.get("addr:postcode")), city[:100],
|
||
|
|
(tags.get("addr:province") or "")[:50], lat, lon,
|
||
|
|
(tags.get("phone") or "")[:20], (tags.get("email") or "")[:255],
|
||
|
|
(tags.get("website") or "")[:255], external_id,
|
||
|
|
),
|
||
|
|
)
|
||
|
|
updated += 1
|
||
|
|
else:
|
||
|
|
execute(
|
||
|
|
"""INSERT INTO wholesalers (external_id, name, address, postcode, city, province,
|
||
|
|
latitude, longitude, phone, email, website, data_source, product_categories)
|
||
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'openstreetmap',%s)""",
|
||
|
|
(
|
||
|
|
external_id, name[:255], address, _normalize_pc(tags.get("addr:postcode")),
|
||
|
|
city[:100], (tags.get("addr:province") or "")[:50], lat, lon,
|
||
|
|
(tags.get("phone") or "")[:20], (tags.get("email") or "")[:255],
|
||
|
|
(tags.get("website") or "")[:255], [brand],
|
||
|
|
),
|
||
|
|
)
|
||
|
|
inserted += 1
|
||
|
|
total = fetch_one("SELECT COUNT(*) AS n FROM wholesalers")
|
||
|
|
return {"fetched": len(elements), "inserted": inserted, "updated": updated, "total": int((total or {}).get("n") or 0)}
|