如何将WFS GeoJSON文件导入PostgreSQL数据库？

Got it, let's solve your GeoJSON import problem—regular JSON methods don't work here because we need to handle spatial data, and you're on the right track thinking about PostGIS functions. Here are three practical approaches tailored to your workflow:

1. Use ogr2ogr (Recommended for Batch Imports) #

This is the most efficient way, since ogr2ogr (part of GDAL) is built specifically for spatial data conversions. First, make sure you have GDAL installed (it's included with most GIS tools like QGIS, or you can install it via package managers).

Single File Import Command #

ogr2ogr -f "PostgreSQL" PG:"dbname='data_base' user='XXX' password='XXX' host='XXX' port='5432'" D:/test_wfs/your_file.geojson -nln public.target_table -overwrite -t_srs EPSG:4326

• -nln: Specify your target table name (replace public.target_table)
• -overwrite: Replaces the table if it already exists (remove if you want to append data)
• -t_srs: Ensures the spatial reference matches your WFS (most use EPSG:4326, adjust if needed)

Batch Import Script (Windows Example) #

To import all your GeoJSON files at once:

@echo off
set PG_CONN=PG:"dbname='data_base' user='XXX' password='XXX' host='XXX' port='5432'"
for %%f in (D:/test_wfs/*.geojson) do (
    ogr2ogr -f "PostgreSQL" %PG_CONN% "%%f" -nln public.target_table -append
)

This appends all files to the same table—ideal if your GeoJSONs have consistent schemas.

2. Modify Your Python Script to Import Directly #

Instead of saving files locally first, you can download and import in one step to keep everything in your existing workflow.

First, enable PostGIS in your database (run this once):

CREATE EXTENSION IF NOT EXISTS postgis;

Then update your script:

import requests
import psycopg2
import json

def download_and_import(wfs_url, feature_id, db_cursor):
    response = requests.get(f"{wfs_url}'{feature_id}'")
    if response.ok:
        geojson = response.json()
        # Assume your WFS returns a FeatureCollection—adjust if it's a single Feature
        for feature in geojson.get("features", []):
            # Extract geometry and properties
            geom_json = json.dumps(feature["geometry"])
            props = feature["properties"]
            
            # Insert into database (customize columns to match your data)
            insert_sql = """
                INSERT INTO public.target_table (id, geom, property1, property2)
                VALUES (%s, ST_GeomFromGeoJSON(%s), %s, %s)
                ON CONFLICT (id) DO UPDATE SET 
                    geom = EXCLUDED.geom,
                    property1 = EXCLUDED.property1,
                    property2 = EXCLUDED.property2;
            """
            # Replace property1/2 with actual keys from your GeoJSON properties
            db_cursor.execute(insert_sql, (
                feature_id,
                geom_json,
                props.get("property1"),
                props.get("property2")
            ))

def main():
    conn = psycopg2.connect(
        user="XXX",
        password="XXX",
        database="data_base",
        host="XXX",
        port="5432"
    )
    conn.autocommit = True  # Auto-save changes (or call conn.commit() at the end)
    cur = conn.cursor()

    # Create target table if it doesn't exist (customize schema!)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS public.target_table (
            id TEXT PRIMARY KEY,
            geom GEOMETRY(Geometry, 4326), -- Match your spatial reference
            property1 TEXT,
            property2 NUMERIC
        );
    """)

    wfs_base_url = "mywfsurl"
    cur.execute("SELECT id FROM public.base")
    feature_ids = [item[0] for item in cur.fetchall()]

    for fid in feature_ids:
        download_and_import(wfs_base_url, fid, cur)
        print(f"Imported feature {fid} successfully")

    print("All imports completed!")
    cur.close()
    conn.close()

if __name__ == "__main__":
    main()

• Customize the target_table schema to match the properties in your GeoJSON
• Use ST_GeomFromGeoJSON (not ST_GeomFromText)—it's purpose-built for parsing GeoJSON geometry strings

3. Manual SQL Import (For Small Datasets) #

If you only have a few files, you can import directly via SQL:

-- Enable PostGIS if not already done
CREATE EXTENSION IF NOT EXISTS postgis;

-- Create your table
CREATE TABLE IF NOT EXISTS public.small_dataset (
    id TEXT PRIMARY KEY,
    geom GEOMETRY(Point, 4326),
    properties JSONB
);

-- Insert data (replace with your GeoJSON content)
INSERT INTO public.small_dataset (id, geom, properties)
VALUES (
    'feature_1',
    ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [121.5, 31.2]}'),
    '{"name": "Sample Location", "value": 123}'
);

Key Notes #

• Always verify your spatial reference system (SRS)—most WFS services use EPSG:4326 (WGS84)
• If you get geometry errors, use ST_MakeValid(ST_GeomFromGeoJSON(%s)) to fix invalid geometries
• For large datasets, ogr2ogr is faster than Python loops because it uses bulk inserts

内容的提问来源于stack exchange，提问作者Mr Greedy