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)
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_
CodePudding user response:
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;
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.