# 1. 安装 PostgreSQL 仓库
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# 2. 导入仓库签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add -

# 3. 更新包列表
sudo apt update

# 4. 安装 PostgreSQL 16 和 PostGIS 3.4
sudo apt install postgresql-16 postgresql-16-postgis-3

# 5. 启动服务
sudo systemctl enable postgresql
sudo systemctl start postgresql

# 切换到 postgres 用户
sudo -u postgres psql

# 在数据库中启用 PostGIS
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
-- 可选：栅格支持
CREATE EXTENSION postgis_raster;
-- 可选：地址标准化
CREATE EXTENSION address_standardizer;

-- 查看 PostGIS 版本
SELECT PostGIS_Full_Version();

# 1. 安装 EPEL 和 PostgreSQL 仓库
sudo dnf install -y epel-release
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 2. 安装 PostgreSQL 和 PostGIS
sudo dnf install -y postgresql16-server postgresql16-postgis3

# 3. 初始化数据库
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# 4. 启动服务
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

# 5. 配置认证（修改 pg_hba.conf）
sudo sed -i 's/ident/md5/g' /var/lib/pgsql/16/data/pg_hba.conf
sudo systemctl restart postgresql-16

# 使用 Homebrew
brew install postgresql@16
brew install postgis

# 启动 PostgreSQL
brew services start postgresql@16

# 创建数据库并启用 PostGIS
createdb gisdb
psql -d gisdb -c "CREATE EXTENSION postgis;"

# 使用官方 PostGIS 镜像
docker run -d \
  --name postgis \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=gisdb \
  -p 5432:5432 \
  postgis/postgis:16-3.4

# 创建数据卷
docker volume create pgdata

docker run -d \
  --name postgis \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=gisdb \
  -v pgdata:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgis/postgis:16-3.4

version: '3.8'

services:
  postgis:
    image: postgis/postgis:16-3.4
    container_name: postgis
    environment:
      POSTGRES_USER: gisadmin
      POSTGRES_PASSWORD: ${PG_PASSWORD:-changeme}
      POSTGRES_DB: gisdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d  # 初始化脚本
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U gisadmin -d gisdb"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped

volumes:
  pgdata:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS postgis_raster;

# 启动
docker compose up -d

# 查看日志
docker compose logs -f postgis

# 连接测试
docker exec -it postgis psql -U gisadmin -d gisdb -c "SELECT PostGIS_Version();"

-- 创建专用空间数据库
CREATE DATABASE gisdb
    WITH ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    TEMPLATE template0;

-- 连接到 gisdb 后执行
\c gisdb

-- 安装核心扩展
CREATE EXTENSION IF NOT EXISTS postgis;           -- 核心空间功能
CREATE EXTENSION IF NOT EXISTS postgis_topology;  -- 拓扑支持
CREATE EXTENSION IF NOT EXISTS postgis_raster;    -- 栅格支持
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;     -- 模糊匹配（用于地名匹配）
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder; -- TIGER 地理编码
CREATE EXTENSION IF NOT EXISTS address_standardizer;   -- 地址标准化

-- 验证已安装的扩展
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

-- 查看可用升级路径
SELECT version();

-- 升级扩展（例如从 3.3 升级到 3.4）
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;

-- 验证升级结果
SELECT PostGIS_Full_Version();

# 1. 下载示例数据（全球国家边界）
wget https://naciscdn.org/naturalearth/110m/cultural/ne_110m_admin_0_countries.zip
unzip ne_110m_admin_0_countries.zip -d /tmp/world

# 2. 预览 SQL（不执行）
shp2pgsql -s 4326 -W UTF-8 /tmp/world/ne_110m_admin_0_countries.shp public.countries

# 3. 导入到数据库
shp2pgsql -s 4326 -W UTF-8 -I /tmp/world/ne_110m_admin_0_countries.shp public.countries \
  | psql -d gisdb -U postgres

#!/bin/bash
# batch_import.sh - 批量导入目录下所有 Shapefile

DB_NAME="gisdb"
DB_USER="postgres"
SRID="4326"
ENCODING="UTF-8"
SHAPEFILE_DIR="/data/shapefiles"

for shp in "$SHAPEFILE_DIR"/*.shp; do
    TABLE_NAME=$(basename "$shp" .shp | tr '[:upper:]' '[:lower:]' | tr '-' '_')
    echo "Importing $shp -> $TABLE_NAME ..."
    shp2pgsql -s "$SRID" -W "$ENCODING" -I -D "$shp" "public.$TABLE_NAME" \
      | psql -d "$DB_NAME" -U "$DB_USER" -q
    echo "Done: $TABLE_NAME"
done

echo "All shapefiles imported."

-- 创建原始数据表
CREATE TABLE raw_pois (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    category VARCHAR(50),
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    address TEXT
);

-- 使用 COPY 导入 CSV
COPY raw_pois (name, category, latitude, longitude, address)
FROM '/data/pois.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF-8');

-- 创建空间表
CREATE TABLE pois AS
SELECT
    id,
    name,
    category,
    address,
    ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) AS geom
FROM raw_pois;

-- 添加主键和空间索引
ALTER TABLE pois ADD PRIMARY KEY (id);
CREATE INDEX idx_pois_geom ON pois USING GIST (geom);

-- 验证数据
SELECT name, category, ST_AsText(geom) FROM pois LIMIT 5;

-- 使用 ogr2ogr 导入 GeoJSON（需安装 GDAL）
-- 命令行方式
ogr2ogr -f "PostgreSQL" \
  PG:"dbname=gisdb user=postgres" \
  /data/regions.geojson \
  -nln regions \
  -overwrite

-- 或在 SQL 中使用 ST_GeomFromGeoJSON
CREATE TABLE geojson_features (
    id SERIAL PRIMARY KEY,
    properties JSONB,
    geom GEOMETRY(Geometry, 4326)
);

-- 手动插入 GeoJSON
INSERT INTO geojson_features (properties, geom) VALUES
(
    '{"name": "测试区域", "type": "polygon"}',
    ST_GeomFromGeoJSON('{
        "type": "Polygon",
        "coordinates": [[[116.38, 39.90], [116.42, 39.90],
                         [116.42, 39.93], [116.38, 39.93],
                         [116.38, 39.90]]]
    }')
);

# 导出到 Shapefile
pgsql2shp -f /output/countries.shp -h localhost -u postgres gisdb \
  "SELECT name, iso_a3, geom FROM countries"

# 导出带 SRID 的数据
pgsql2shp -f /output/cities.shp -h localhost -u postgres gisdb \
  "SELECT name, population, geom FROM cities"

-- 单条记录导出
SELECT ST_AsGeoJSON(geom) AS geojson
FROM cities
WHERE name = '北京';

-- 导出完整 FeatureCollection
SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(feature)
) AS geojson
FROM (
    SELECT jsonb_build_object(
        'type', 'Feature',
        'id', id,
        'geometry', ST_AsGeoJSON(geom)::jsonb,
        'properties', jsonb_build_object(
            'name', name,
            'province', province,
            'population', population
        )
    ) AS feature
    FROM cities
) subquery;

# 导出为 GeoJSON
ogr2ogr -f "GeoJSON" /output/cities.geojson \
  PG:"dbname=gisdb" -sql "SELECT name, geom FROM cities"

# 导出为 GeoPackage（推荐，支持多图层）
ogr2ogr -f "GPKG" /output/gis_data.gpkg \
  PG:"dbname=gisdb" -sql "SELECT * FROM cities"

-- 慢：逐条 INSERT
INSERT INTO points VALUES (1, ST_GeomFromText('POINT(0 0)'));
INSERT INTO points VALUES (2, ST_GeomFromText('POINT(1 1)'));

-- 快：批量 COPY（先导入文本，后创建几何）
-- 步骤 1: 导入到临时表
CREATE TEMP TABLE tmp_points (
    id INTEGER,
    x DOUBLE PRECISION,
    y DOUBLE PRECISION
);

COPY tmp_points FROM '/data/points.csv' WITH (FORMAT csv, HEADER true);

-- 步骤 2: 批量转换并插入空间表
INSERT INTO points (id, geom)
SELECT id, ST_SetSRID(ST_MakePoint(x, y), 4326)
FROM tmp_points;

DROP TABLE tmp_points;

-- 1. 创建空间索引
CREATE INDEX idx_table_geom ON table_name USING GIST (geom);

-- 2. 更新表统计信息
ANALYZE table_name;

-- 3. 验证数据
SELECT Count(*) AS total,
       ST_IsValid(geom) AS is_valid,
       Count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_count
FROM table_name
GROUP BY ST_IsValid(geom);

-- 4. 修复无效几何
UPDATE table_name
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

-- 诊断脚本
SELECT version();                          -- PostgreSQL 版本
SELECT PostGIS_Full_Version();             -- PostGIS 完整版本
SELECT name, setting FROM pg_settings
WHERE name IN ('shared_preload_libraries', 'max_connections');