How to insert value to a column as a result of geometry of another column in the same table

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

table:

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)

​x
 
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_

 
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_​

image

Answer

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 = fourCornersRepresentativeToTreatmentAsGeoJSON::geometry,  geometryOfCellRepresentativeToBuffer = fourCornersRepresentativeToBufferAsGeoJSON::geometry;​

code:

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;

 
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;​

link to fiddle: fiddle code

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.

Advertisement

Answer