[postgis-users] IF, ELSE, FOR and WHILE in postgis

[P Kishor]

2009/10/12 Daniel Grum <daniel.grum at unibw.de>:
> P Kishor schrieb:
>>
>> 2009/10/12 Daniel Grum <daniel.grum at unibw.de>:
>>
>>>
>>> Hi, I have a probleme with PostgreSQL and PostGIS.
>>>
>>> I need loops for the programming of a PostGIS SQL-script but PostgreSQL
>>>  makes truble.
>>>
>>> I think I know how I have to use loops in SQL.-->I need loops to update
>>> my
>>> table line by line!
>>>
>>> ELSE PostgreSQl said: "as an expression used underinquiry proved more
>>> than
>>> one line"
>>>
>>> The SQL-Skript:
>>> UPDATE einheiten_und_bewegungen
>>> SET the_geom = (SELECT
>>>
>>> ST_Line_SubString(ST_LineMerge(line.the_geom),(1-((Length(line.the_geom)-1000)
>>>          /Length(line.the_geom))),1)
>>>      FROM public.einheiten_und_bewegungen line
>>>      WHERE length(line.the_geom)>1000
>>>      ORDER BY line.gid);
>>>
>>> In the table einheiten_und_bewegungen are more than one line!
>>>
>>
>> I am not sure what you are trying to do, but you are mixing metaphors
>> above. An UPDATE statement is made of
>>
>> UPDATE <table>
>> SET <column_1> = ?, <column_2> = ?, .. <column_n> = ?
>> WHERE <condition>
>>
>> If you update expression is a SELECT statement (a sub-select) then it
>> has to return one and only one value. So, the following
>>
>> SELECT
>> ST_Line_SubString(ST_LineMerge(line.the_geom),(1-((Length(line.the_geom)-1000)
>>          /Length(line.the_geom))),1)
>>      FROM public.einheiten_und_bewegungen line
>>      WHERE length(line.the_geom)>1000
>>      ORDER BY line.gid
>>
>> won't work because it is likely to return more than one values. Think
>> of it this way... you can SET the_geom equal to only one geometry, no?
>> So, your sub-select has to return only one geometry.
>>
>> Perhaps if you explained more clearly what you wanted to do, you would
>> get better help.
>>
>>
>>
>>
>>
>>
>>
>>>
>>> If there are some ideas please help me!
>>>
>>> --daniel
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>>
>>
>>
>
> I know that an UPDATE updates the info of one column-->that is the problem.
>
> I only want to save the new information line by line. the geom and the
> distance of one linestring while be changed round by round and this changed
> information should be saved in the table round by round.
>
> So I need a possiblity to save the changed information of the first
> linestring in the first line and in the next lines the informations of the
> changed other linestrings(line by line).
>
> Do you understand what I mean.
>


Not sure if I do. Would the following work for you?

UPDATE einheiten_und_bewegungen
SET the_geom = ST_Line_SubString(ST_LineMerge(line.the_geom),(1-((Length(line.the_geom)-1000)
/Length(line.the_geom))),1)
WHERE length(line.the_geom)>1000;

Note: make sure the parentheses match above


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States