1

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

0

"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...

  • i'd be happy to help you further, but I need some more information. please update your question to include the clean.households table schema, the updated function, and the full syntax error message you are getting? – Nathan Gerhart Jul 3 '13 at 18:34
  • @TechSingh, I've revised my answer. – Nathan Gerhart Jul 3 '13 at 22:41
  • UPDATE clean.households set geom=ST_GeomFromText('POINT(' || a.x_coord || ' ' || a.y_coord || ')',2285) where hhid ='a.hhid'; UPDATE clean.households set wkt = ST_AsText(ST_Transform(ST_GeomFromText('POINT(' || a.x_coord || ' ' || a.y_coord || ')',2285),4269))where hhid='a.hhid'; – Gurmehar Singh Jul 5 '13 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.