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_
image
Advertisement
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;
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;
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.