[postgis-users] Intro and question about Update statements and Geometry columns

[strk at refractions.net]

On Sun, Sep 26, 2004 at 11:44:15PM -0700, Liam Graham wrote:
> Hi, everyone,
> 
> I've been learning Postgis in my spare time (I don't currently work in the GIS field), and just
> joined the list today, after reading through a lot of the archives recently.  I have a rather
> basic question that I haven't been able to find an answer for in the documentation or in the
> archives:  how can I populate a Geometry column based on the values in other non-Geometry columns
> in the table?  For example, if I have a simple table with starting and stopping x and y
> coordinates defining lines, and I add a Geometry column, how can I populate the new column based
> on the existing columns?
> 
> So with a table named FOO and structured something like this:
> 
>  startx             | integer                 | not null
>  stopx              | integer                 | not null
>  starty             | integer                 | not null
>  stopy              | integer                 | not null
>  shape             | geometry                | 
> 
> I tried this, which I thought would work:
> update foo set shape = GeometryFromText('LINESTRING(startx starty, stopx stopy)', -1);
> 
> But I get an error:  "ERROR:  couldnt parse object in GEOMETRY"
> 
> I'm sure my mistake is something simple ... and I should also mention that I'm a novice at
> Postgres as well as Postgis (have used Oracle and Sybase much more).
>  
> Thanks for any help you can provide.  And if the answer is out there somewhere in the docs or
> archives, I apologize: I did search, but couldn't find it.  
> 
> Sincerely,
> Liam

Liam, unfortunately geometry contruction functions are now currently
present in postgis. What you might do is continue with your contruction
by text approach (which is pretty uncomfortable and error-prone).
The problem in your query is that startx, starty and other identifiers
are put inside a literal string, you should change the query to something
like:

update foo set shape = GeometryFromText(textcat('LINESTRING(',textcat(startx::text, textcat(starty::text, textcat(stopx::text, textcat(stopy::text, ')''')))))));

Pretty ugly uh ?
--strk;