由于同一表中另一列的几何形状，如何将值插入列 [英] How to insert value to a column as a result of geometry of another column in the same table

问题描述

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

i have the database table posted below. the two columns geometryOfCellRepresentativeToTreatment and geometryOfCellRepresentativeToBuffer are of type geometry. and their value is equal the the geometry of the column fourCornersRepresentativeToTreatmentAsGeoJSON_ and fourCornersRepresentativeToBufferAsGeoJSON respectively. how can i insert the value into the latter columns as geometry of the former columns

表格:

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_

图片

推荐答案

只需将 geojson 字符串设置为 UPDATE 中的 geometry 列语句(为了更明确，将 :: 字符串转换为 geometry):

Just set the geojson strings to the geometry columns in an UPDATE statement (to make it more explicit, cast :: the strings to 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 等

Note: you are storing the same geometry twice in the same record, which is not really necessary. You should store geometries as such and only on demand serialize them in the format you want, e.g. WKT, KML, GeoJSON, etc.

这篇关于由于同一表中另一列的几何形状，如何将值插入列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！