Insert geometric data to a column from coordinate values in double precision

Question

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.

1. 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' ;

Answer 1

"Query returned successfully..." isn't an error. It is returned to indicate that you successfully created the function my_geom().

As written, your function does not work, because there are no variables named x_coord or y_coord; they need to be x.x_coord and x.y_coord instead. Once you get the names correct, the function runs, but doesn't do what you expect. It will UPDATE all rows in the table over and over with the geometry for the current row in the loop. When the LOOP finishes, all rows in the table will have the geometry that describes the last row in table. The function returns the contents of the table before the function ran.

I'd suggest using plain SQL for this task, and I'd suggest using ST_MakePoint instead:

-- Step 1: populate the geom column using the x and y coordinates
UPDATE clean.households SET geom = ST_MakePoint(x_coord, y_coord);
-- Step 2: populate the wkt column using the geometry
UPDATE clean.households SET wkt = ST_AsText(geom);

Also, the link doesn't show the "Geom" and "WKT" columns in your table. You'll need to add those columns before you can insert data into them...