如何在插入数据时将同一表中GeoJSON列的几何值转换后存入对应geometry类型列

Got it, let's break this down clearly. Since you're working with spatial geometry columns in PostgreSQL, you'll need the PostGIS extension (it's the standard toolset for handling geographic data in PostgreSQL). Here's exactly how to handle the GeoJSON-to-geometry conversion and insert your data properly:

Step 1: Ensure PostGIS is Enabled #

First, make sure the PostGIS extension is installed and activated in your database. Run this SQL once if you haven't already:

CREATE EXTENSION IF NOT EXISTS postgis;

Step 2: Use ST_GeomFromGeoJSON() for Conversion #

PostGIS provides the ST_GeomFromGeoJSON() function that directly converts a valid GeoJSON string into a PostgreSQL geometry object. Perfectly, this function returns NULL if you pass a NULL GeoJSON value, which matches your use case where some buffer fields are empty.

Option 1: Direct SQL Insert #

If you're writing raw SQL to insert data, here's a working example using your table structure:

INSERT INTO grid_cell_data (
    isTreatment,
    isBuffer,
    fourCornersRepresentativeToTreatmentAsGeoJSON,
    fourCornersRepresentativeToBufferAsGeoJSON,
    distanceFromCenterPointOfTreatmentToNearestEdge,
    distanceFromCenterPointOfBufferToNearestEdge,
    areasOfCoveragePerWindowForCellsRepresentativeToTreatment,
    areasOfCoveragePerWindowForCellsRepresentativeToBuffer,
    averageHeightsPerWindowRepresentativeToTreatment,
    averageHeightsPerWindowRepresentativeToBuffer,
    geometryOfCellRepresentativeToTreatment,
    geometryOfCellRepresentativeToBuffer
) VALUES (
    TRUE,
    FALSE,
    '{"type": "Polygon", "coordinates": [[[0,0], [0,1], [1,1], [1,0], [0,0]]]}', -- Sample valid GeoJSON
    NULL,
    5.2,
    NULL,
    10.0,
    NULL,
    2.5,
    NULL,
    ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[[0,0], [0,1], [1,1], [1,0], [0,0]]]}'),
    ST_GeomFromGeoJSON(NULL) -- Automatically returns NULL
);

Option 2: Python Insert (Using psycopg2) #

Since you mentioned using json.dumps() in Python, here's how to integrate the conversion into your Python workflow with psycopg2 (the standard PostgreSQL adapter for Python):

import psycopg2
import json

# Assume these are your precomputed data lists
fourCornersOfKeyWindowAsGeoJSON = [{"type": "Polygon", "coordinates": [[[0,0], [0,1], [1,1], [1,0], [0,0]]]}]
distancesFromCenterPointsToNearestEdge = [5.2]
areasOfCoveragePerWindow = [10.0]
averageHeightsPerWindow = [2.5]

# Connect to your database
conn = psycopg2.connect(
    dbname="your_database_name",
    user="your_username",
    password="your_password",
    host="your_host"
)
cur = conn.cursor()

# Loop through your data and insert each row
for i in range(len(fourCornersOfKeyWindowAsGeoJSON)):
    # Prepare your variables
    isTreatment = True
    isBuffer = False
    fourCorners_treatment = json.dumps(fourCornersOfKeyWindowAsGeoJSON[i])
    fourCorners_buffer = None
    distance_treatment = distancesFromCenterPointsToNearestEdge[i]
    distance_buffer = None
    area_treatment = areasOfCoveragePerWindow[i]
    area_buffer = None
    height_treatment = averageHeightsPerWindow[i]
    height_buffer = None

    # Define the insert query with ST_GeomFromGeoJSON conversion
    insert_query = """
        INSERT INTO grid_cell_data (
            isTreatment,
            isBuffer,
            fourCornersRepresentativeToTreatmentAsGeoJSON,
            fourCornersRepresentativeToBufferAsGeoJSON,
            distanceFromCenterPointOfTreatmentToNearestEdge,
            distanceFromCenterPointOfBufferToNearestEdge,
            areasOfCoveragePerWindowForCellsRepresentativeToTreatment,
            areasOfCoveragePerWindowForCellsRepresentativeToBuffer,
            averageHeightsPerWindowRepresentativeToTreatment,
            averageHeightsPerWindowRepresentativeToBuffer,
            geometryOfCellRepresentativeToTreatment,
            geometryOfCellRepresentativeToBuffer
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
            ST_GeomFromGeoJSON(%s),
            ST_GeomFromGeoJSON(%s)
        );
    """

    # Execute the query (note we pass the GeoJSON strings twice: once for the text column, once for conversion)
    cur.execute(insert_query, (
        isTreatment, isBuffer,
        fourCorners_treatment, fourCorners_buffer,
        distance_treatment, distance_buffer,
        area_treatment, area_buffer,
        height_treatment, height_buffer,
        fourCorners_treatment, fourCorners_buffer
    ))

# Commit changes and close connections
conn.commit()
cur.close()
conn.close()

Key Notes to Remember #

• Validate GeoJSON: Ensure your GeoJSON strings are valid before insertion. You can test validity with ST_IsValid(ST_GeomFromGeoJSON('your_geojson_string')) in SQL.
• Spatial Reference (SRID): If your GeoJSON uses a specific coordinate system (like WGS84, SRID 4326), you can set it explicitly with ST_SetSRID(ST_GeomFromGeoJSON(your_geojson), 4326) to avoid issues with spatial queries later.
• NULL Handling: As mentioned, ST_GeomFromGeoJSON(NULL) returns NULL, so you don't need any special logic for empty buffer fields—it just works.

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