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

[TECHER Jean David]

Hi

You could use operator || too.

Then you can concat your points as geometry:

update FOO set shape = GeometryFromText('LINESTRING('||startx||'
'starty||','||stopx||' '||stopy||' )',YOUR_SRID)

where YOUR_SRID = -1 or something else

----- Original Message -----
From: <strk at refractions.net>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Monday, September 27, 2004 9:58 AM
Subject: Re: [postgis-users] Intro and question about Update statements
andGeometry columns


> 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;
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users