sql - 由于同一表中另一列的几何形状，如何将值插入列

Question

我在下面发布了数据库表。两列geometryOfCellRepresentativeToTreatment和geometryOfCellRepresentativeToBuffer是几何类型。并且它们的值分别等于柱子的几何形状fourCornersRepresentativeToTreatmentAsGeoJSON_ 和fourCornersRepresentativeToBufferAsGeoJSON。如何将值作为前列的几何形状插入后列

表：

CREATE TABLE grid_cell_data (
           id SERIAL PRIMARY KEY,
           isTreatment boolean,
           isBuffer boolean,
           fourCornersRepresentativeToTreatmentAsGeoJSON text,
           fourCornersRepresentativeToBufferAsGeoJSON text,
           distanceFromCenterPointOfTreatmentToNearestEdge numeric,
           distanceFromCenterPointOfBufferToNearestEdge numeric,
           areasOfCoveragePerWindowForCellsRepresentativeToTreatment numeric,
           areasOfCoveragePerWindowForCellsRepresentativeToBuffer numeric,
           averageHeightsPerWindowRepresentativeToTreatment numeric,
           averageHeightsPerWindowRepresentativeToBuffer numeric,
           geometryOfCellRepresentativeToTreatment geometry,
           geometryOfCellRepresentativeToBuffer geometry)

data_to_be_inserted：

isTreatment = True//boolean
        isBuffer = False //boolean
        fourCornersRepresentativeToTreatmentAsGeoJSON_ = json.dumps(fourCornersOfKeyWindowAsGeoJSON[i])//string
        fourCornersRepresentativeToBufferAsGeoJSON_ = None//string
        distanceFromCenterPointOfTreatmentToNearestEdge_ = distancesFromCenterPointsToNearestEdge[i]
        distanceFromCenterPointOfBufferToNearestEdge_ = None
        areasOfCoveragePerWindowForCellsRepresentativeToTreatment_= areasOfCoveragePerWindow[i]
        areasOfCoveragePerWindowForCellsRepresentativeToBuffer_ = None
        averageHeightsPerWindowRepresentativeToTreatment_ = averageHeightsPerWindow[i]
        averageHeightsPerWindowRepresentativeToBuffer_ = None
        geometryOfCellRepresentativeToTreatment_ = //geometry of fourCornersRepresentativeToTreatmentAsGeoJSON_
        geometryOfCellRepresentativeToBuffer_ = //geometry of fourCornersRepresentativeToBufferAsGeoJSON_

图片

标签： sqlpostgresqlsql-updatepostgis

Answer 1

只需将geojson字符串设置为语句中的geometry列UPDATE（为了使其更明确，::请将字符串转换为geometry）：

UPDATE grid_cell_data SET
  geometryOfCellRepresentativeToTreatment = fourCornersRepresentativeToTreatmentAsGeoJSON::geometry,
  geometryOfCellRepresentativeToBuffer = fourCornersRepresentativeToBufferAsGeoJSON::geometry;

代码：

UPDATE grid_cell_data set 
    geometryOfCellRepresentativeToTreatment = ST_GeomFromGeoJSON(fourCornersRepresentativeToTreatmentAsGeoJSON)
 WHERE 
    fourCornersRepresentativeToTreatmentAsGeoJSON <> '' and fourCornersRepresentativeToTreatmentAsGeoJSON IS NOT NULL;

UPDATE grid_cell_data SET
    geometryOfCellRepresentativeToBuffer = ST_GeomFromGeoJSON(fourCornersRepresentativeToBufferAsGeoJSON)
WHERE 
    fourCornersRepresentativeToBufferAsGeoJSON <> '' and fourCornersRepresentativeToBufferAsGeoJSON IS NOT NULL;

链接到小提琴： 小提琴代码

注意：您在同一记录中存储了两次相同的几何图形，这并不是必需的。您应该这样存储几何图形，并且仅在需要时以您想要的格式将它们序列化，例如 WKT、KML、GeoJSON 等。