-- 安装扩展
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- 验证安装
SELECT na.address, na.streetname, na.streettypeabbrev, na.zip
FROM normalize_address('100 Main St, Anytown, OH 44000') AS na;

# 下载 TIGER 数据（以加州为例）
# 使用 PostGIS 提供的脚本
psql -d gisdb -c "SELECT tiger.loader_generate_script(ARRAY['CA'], 'gisdata')"

# 这会生成一个 shell 脚本，执行它来下载和导入 TIGER 数据
# 注意：TIGER 数据仅覆盖美国

-- 正向编码
SELECT g.rating, g.geomout, g.streetname, g.streettypeabbrev, g.zip
FROM geocode('100 Main St, San Francisco, CA 94102') AS g;

-- 反向编码
SELECT pprint_addy(r.addy) AS address, r.street
FROM reverse_geocode(ST_GeomFromText('POINT(-122.4194 37.7749)', 4326)) AS r;

-- 批量编码
WITH addresses AS (
    SELECT id, address FROM customers WHERE geom IS NULL
)
INSERT INTO customers (id, address, geom)
SELECT
    a.id,
    a.address,
    g.geomout
FROM addresses a
CROSS JOIN LATERAL geocode(a.address, 1) AS g
WHERE g.rating < 30;

-- 创建扩展以支持 HTTP 请求
CREATE EXTENSION IF NOT EXISTS http;

-- 创建编码结果表
CREATE TABLE geocode_cache (
    address TEXT PRIMARY KEY,
    lng DOUBLE PRECISION,
    lat DOUBLE PRECISION,
    formatted_address TEXT,
    province TEXT,
    city TEXT,
    district TEXT,
    geocoded_at TIMESTAMP DEFAULT now()
);

CREATE INDEX idx_geocode_cache_addr ON geocode_cache(address);

-- 创建编码函数（使用高德 API）
CREATE OR REPLACE FUNCTION geocode_amap(addr TEXT)
RETURNS TABLE(lng DOUBLE PRECISION, lat DOUBLE PRECISION, formatted TEXT) AS $$
DECLARE
    api_key TEXT := 'YOUR_AMAP_API_KEY';
    url TEXT;
    result JSONB;
BEGIN
    -- 检查缓存
    RETURN QUERY
    SELECT gc.lng, gc.lat, gc.formatted_address
    FROM geocode_cache gc
    WHERE gc.address = addr;

    IF FOUND THEN RETURN; END IF;

    -- 调用 API
    url := 'https://restapi.amap.com/v3/geocode/geo?address=' ||
           urlencode(addr) || '&key=' || api_key;

    result := (http_get(url))::jsonb;

    IF (result->>'status')::int = 1 AND jsonb_array_length(result->'geocodes') > 0 THEN
        DECLARE
            geo JSONB := result->'geocodes'->0;
            location TEXT := geo->>'location';
            pos INTEGER := position(',' IN location);
        BEGIN
            lng := substring(location, 1, pos - 1)::DOUBLE PRECISION;
            lat := substring(location, pos + 1)::DOUBLE PRECISION;
            formatted := geo->>'formatted_address';

            -- 写入缓存
            INSERT INTO geocode_cache (address, lng, lat, formatted_address)
            VALUES (addr, lng, lat, formatted)
            ON CONFLICT (address) DO NOTHING;

            RETURN NEXT;
        END;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM geocode_amap('北京市朝阳区建国门外大街1号');

# Python 示例：使用 requests 批量编码
import psycopg2
import requests
import time

conn = psycopg2.connect("dbname=gisdb user=postgres")
cur = conn.cursor()

# 获取未编码的地址
cur.execute("SELECT id, address FROM pois WHERE geom IS NULL")
rows = cur.fetchall()

for row_id, address in rows:
    # 调用高德 API
    resp = requests.get('https://restapi.amap.com/v3/geocode/geo', params={
        'address': address,
        'key': 'YOUR_API_KEY'
    })
    data = resp.json()

    if data['status'] == '1' and data['geocodes']:
        location = data['geocodes'][0]['location']
        lng, lat = location.split(',')
        cur.execute("""
            UPDATE pois
            SET geom = ST_SetSRID(ST_MakePoint(%s, %s), 4326)
            WHERE id = %s
        """, (float(lng), float(lat), row_id))

    time.sleep(0.1)  # 限速

conn.commit()
conn.close()

-- 创建编码任务表
CREATE TABLE geocode_tasks (
    id SERIAL PRIMARY KEY,
    address TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',  -- pending/done/failed
    retry_count INTEGER DEFAULT 0,
    result_lng DOUBLE PRECISION,
    result_lat DOUBLE PRECISION,
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now()
);

-- 查看待编码任务
SELECT count(*) FROM geocode_tasks WHERE status = 'pending';

-- 查看失败任务
SELECT address, error_msg, retry_count
FROM geocode_tasks
WHERE status = 'failed' AND retry_count < 3
ORDER BY created_at;

-- 反向编码：给定坐标，查询最近的已知地址
CREATE OR REPLACE FUNCTION reverse_geocode_simple(
    p_lng DOUBLE PRECISION,
    p_lat DOUBLE PRECISION,
    p_radius_m INTEGER DEFAULT 100
) RETURNS TABLE(
    nearest_address TEXT,
    distance_m DOUBLE PRECISION
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        address,
        ST_Distance(
            geom::geography,
            ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography
        )
    FROM pois
    WHERE ST_DWithin(
        geom::geography,
        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
        p_radius_m
    )
    ORDER BY geom <-> ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM reverse_geocode_simple(116.4074, 39.9042);

-- 查询坐标所属的多级行政区划
CREATE OR REPLACE FUNCTION get_admin_hierarchy(
    p_lng DOUBLE PRECISION,
    p_lat DOUBLE PRECISION
) RETURNS TABLE(
    province TEXT,
    city TEXT,
    district TEXT,
    town TEXT
) AS $$
DECLARE
    point_geom GEOMETRY;
BEGIN
    point_geom := ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326);

    RETURN QUERY
    SELECT
        p.name::TEXT,
        c.name::TEXT,
        d.name::TEXT,
        t.name::TEXT
    FROM districts p
    LEFT JOIN districts c ON ST_Contains(c.geom, point_geom) AND c.level = '市'
    LEFT JOIN districts d ON ST_Contains(d.geom, point_geom) AND d.level = '区县'
    LEFT JOIN districts t ON ST_Contains(t.geom, point_geom) AND t.level = '乡镇'
    WHERE ST_Contains(p.geom, point_geom) AND p.level = '省'
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_admin_hierarchy(116.4074, 39.9042);
-- 输出: 北京市 | 东城区 | 东华门街道

# docker-compose.yml
version: '3.8'

services:
  nominatim:
    image: mediagis/nominatim:4.4
    container_name: nominatim
    ports:
      - "8080:8080"
    environment:
      NOMINATIM_PASSWORD: "nominatim123"
      IMPORT_STYLE: "extratags"
      THREADS: 4
    volumes:
      - nominatim-data:/var/lib/postgresql/16/main
    restart: unless-stopped

volumes:
  nominatim-data:

# 下载中国数据并导入
# 从 https://download.geofabrik.de/asia/china.html 下载 .osm.pbf 文件
docker exec -it nominatim nominatim import --osm-file /data/china-latest.osm.pbf

# API 调用示例
# 正向编码
curl "http://localhost:8080/search?q=天安门&format=json&limit=1"

# 反向编码
curl "http://localhost:8080/reverse?lat=39.9042&lon=116.4074&format=json"

-- 如果 Nominatim 和 PostGIS 在同一 PostgreSQL 实例中
-- 可以直接查询 Nominatim 的数据库
SELECT
    get_address_by_language(place_id, ARRAY['zh']) AS address,
    ST_X(centroid) AS lng,
    ST_Y(centroid) AS lat
FROM placex
WHERE name->'name'->>'zh' = '天安门'
LIMIT 5;

-- 使用 address_standardizer 扩展
CREATE EXTENSION IF NOT EXISTS address_standardizer;

-- 标准化中国地址
-- 注意: 内置规则主要针对英文地址，中文需要自定义规则

-- 创建中文地址分词辅助表
CREATE TABLE address_tokens (
    id SERIAL PRIMARY KEY,
    raw_address TEXT,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    street VARCHAR(200),
    house_number VARCHAR(50),
    poi_name VARCHAR(200)
);

-- 简单的中文地址解析函数
CREATE OR REPLACE FUNCTION parse_chinese_address(addr TEXT)
RETURNS TABLE(
    province TEXT, city TEXT, district TEXT,
    street TEXT, house_number TEXT
) AS $$
DECLARE
    province_match TEXT;
    city_match TEXT;
    district_match TEXT;
BEGIN
    -- 提取省/市/区
    province_match := (regexp_match(addr, '(.{2,4}省|.{2,4}自治区)'))[1];
    city_match := (regexp_match(addr, '(.{2,6}市|.{2,6}州)'))[1];
    district_match := (regexp_match(addr, '(.{2,6}区|.{2,6}县|.{2,6}市)'))[1];

    RETURN QUERY SELECT
        COALESCE(province_match, '')::TEXT,
        COALESCE(city_match, '')::TEXT,
        COALESCE(district_match, '')::TEXT,
        ''::TEXT,
        ''::TEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 测试
SELECT * FROM parse_chinese_address('北京市朝阳区建国门外大街1号');
SELECT * FROM parse_chinese_address('广东省深圳市南山区科技园路1号');

-- 验证编码结果：检查是否落在预期的行政区划内
CREATE OR REPLACE FUNCTION validate_geocode(
    p_address TEXT,
    p_lng DOUBLE PRECISION,
    p_lat DOUBLE PRECISION
) RETURNS TABLE(is_valid BOOLEAN, reason TEXT) AS $$
DECLARE
    point_geom GEOMETRY;
    addr_city TEXT;
BEGIN
    point_geom := ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326);

    -- 从地址中提取城市名
    addr_city := (regexp_match(p_address, '(.{2,4}市)'))[1];

    -- 检查坐标是否落在该城市的行政区内
    IF addr_city IS NOT NULL THEN
        RETURN QUERY
        SELECT
            EXISTS(
                SELECT 1 FROM districts
                WHERE name = addr_city AND ST_Contains(geom, point_geom)
            ),
            CASE
                WHEN EXISTS(
                    SELECT 1 FROM districts
                    WHERE name = addr_city AND ST_Contains(geom, point_geom)
                ) THEN '坐标在' || addr_city || '范围内'
                ELSE '坐标不在' || addr_city || '范围内，可能偏移'
            END;
    ELSE
        RETURN QUERY SELECT TRUE, '无法验证（地址中未识别城市）'::TEXT;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 批量验证
SELECT address, lng, lat, is_valid, reason
FROM geocode_results
CROSS JOIN LATERAL validate_geocode(address, lng, lat);

-- 检测可能的重复编码结果
SELECT
    a.address AS addr_a,
    b.address AS addr_b,
    ST_Distance(a.geom::geography, b.geom::geography) AS distance_m
FROM geocode_results a, geocode_results b
WHERE a.id < b.id
  AND ST_DWithin(a.geom::geography, b.geom::geography, 10)
  AND a.address != b.address;

-- 从 CSV 导入 POI 并批量编码
CREATE TEMP TABLE poi_import (
    name TEXT,
    address TEXT,
    category TEXT
);

COPY poi_import FROM '/data/pois.csv' WITH (FORMAT csv, HEADER true);

-- 插入并标记待编码
INSERT INTO pois (name, address, category, geom)
SELECT name, address, category, NULL
FROM poi_import;

-- 后续通过应用层批量编码...
-- 编码完成后更新 geom 字段

-- 快递地址标准化与坐标提取
CREATE TABLE delivery_addresses (
    id SERIAL PRIMARY KEY,
    raw_address TEXT,
    normalized_address TEXT,
    geom GEOMETRY(Point, 4326),
    confidence NUMERIC(3,2),  -- 置信度 0-1
    status VARCHAR(20) DEFAULT 'pending'
);

-- 查询高置信度的已编码地址
SELECT raw_address, normalized_address, confidence
FROM delivery_addresses
WHERE status = 'done' AND confidence >= 0.8;