Screenshot of database http://wp.me/aIW9p-i0
For table “households”, I want to create two new columns: 1) “Geom”, data type – geometry; 2) “WKT”, data type ‐ text;
I am intending to do the following tasks: 1. For each household, I have the location, indicated by x_coord and y_coord, respectively in the table. Then I have to create a geometric object – point, based on the x_coord and y_coord for each household. Insert these geometric data to the column “Geom” accordingly.
- Now, after the first step, I have a geometric object for each household. Translate the geometric point data to the well‐known‐text,showing the longitude and latitude of the location of each house.
Insert these values to the second column “WKT”, accordingly
For the Step 1 I wrote the following function but I am getting the error: Query returned successfully with no result in 54 ms.
CREATE OR REPLACE FUNCTION my_geom() RETURNS SETOF clean.households AS $BODY$ DECLARE x clean.households%ROWTYPE; pst geometry;
BEGIN
FOR x IN SELECT * FROM clean.households LOOP pst := ST_GeomFromText('POINT(' || x_coord || ' ' || y_coord || ')');
UPDATE clean.households set geom = pst; RETURN NEXT x;
END LOOP;
RETURN; END $BODY$ LANGUAGE 'plpgsql' ;