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

Question

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

score 0 · Answer 1 · edited Nov 12 '21 at 08:10

0

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.

edited Nov 12 '21 at 08:10

Amrmsmb

1
27
104
226

answered Nov 09 '21 at 14:31

Jim Jones

18,404
3
35
44

please, as shown in the image attached above, for some rows fourCornersRepresentativeToTreatmentAsGeoJSON has value and for other rows it has not. the same applies for fourCornersRepresentativeToBufferAsGeoJSON.therefore, when i use the update statement i receive invalid geometry.i think it is due to that some times fourCornersRepresentativeToTreatmentAsGeoJSON and fourCornersRepresentativeToBufferAsGeoJSON has value and some times not.would you please tell me how the update statement can be altered to suit this condition?? – Amrmsmb Nov 11 '21 at 11:06
in such case, there should be no geometry related. – Amrmsmb Nov 11 '21 at 11:11
yes, in other words, if fourCornersRepresentativeToTreatmentAsGeoJSON is empty then geometryOfCellRepresentativeToTreatment is empty as well, and the same applies of fourCornersRepresentativeToBufferAsGeoJSON and geometryOfCellRepresentativeToBuffer – Amrmsmb Nov 11 '21 at 11:26
i would like to keep you informed that the code you provided works good and logical. but it did not give me the desired result untill i added fourCornersRepresentativeToBufferAsGeoJSON <> '' to the where clause.so the where clause became `WHERE fourCornersRepresentativeToBufferAsGeoJSON <> '' and fourCornersRepresentativeToBufferAsGeoJSON IS NOT NULL;` i just want to tell you that.thanks alot...may i update your answer?or you want to do it? – Amrmsmb Nov 12 '21 at 05:52
would you please assist me to find a solution to this question:https://stackoverflow.com/questions/69939684/how-to-use-st-asmvtgeom-and-st-asmvt – Amrmsmb Nov 12 '21 at 08:09

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

1 Answers1

Linked