[postgis-users] Loop or Cursor

[Bob Pawley]

Leo

Following is the update that I wish to deploy and I have used it as an AFTER INSERT on the parent table. When the object (process_id) has one attrribute (fluid_id) this works well. 

With two fluid_id attributes the update lands both attributes on the same geometry.

I don't understand what needs to be done to loop through the fluid_ids using recursive.

 update p_id.image
 set the_geom = 
 (select st_translate(graphics.utilities_dgm.the_geom,
 st_x(st_centroid(graphics.spatial_ref.line_the_geom)) - 
 st_x(st_centroid(graphics.utilities_dgm.the_geom)),
 st_y(p_id.p_id.three) + graphics.ip_matrix.point_offset -
 st_y(st_centroid(graphics.utilities_dgm.the_geom)))
 from graphics.utilities_dgm, p_id.processes, p_id.p_id, graphics.spatial_ref, p_id.image, processes_count, graphics.ip_matrix
 where graphics.utilities_dgm.utilities_description = 'Text Arrow' 
 and cast (graphics.utilities_dgm.orientation as integer)  = graphics.ip_matrix.text_arrow_orientation
 and graphics.ip_matrix.ip_number = p_id.p_id.number_of_ips
 and p_id.image.p_id_id = p_id.p_id.p_id_id
 and graphics.spatial_ref.description =  p_id.image.pid_outline
 and p_id.image.description = 'P&ID Outline'
 and p_id.p_id.p_id_id = processes_count.p_id_id
 and graphics.spatial_ref.orientation  = graphics.ip_matrix.text_arrow_orientation
 and graphics.ip_matrix.ip_number = p_id.processes.ip_number 
 and p_id.p_id.p_id_id = processes_count.p_id_id
 and graphics.ip_matrix.spatial_ref = p_id.p_id.proc_graphic_position)
 from p_id.processes,  processes_count
 where p_id.processes.p_id_id = processes_count.p_id_id
 and p_id.processes.fluid_id = p_id.image.fluid_id
 and p_id.image.description = 'ip_op_Arrow_name';

The system I have deployed to establish the position of the process objects is based on a number or processes count using IF statements. I am sure there is a less onerous method but, for the time being it is working without using an AFTER UPDATE. That is, unless AFTER UPDATE is needed to accomplish the recursive operation.

Bob


  ----- Original Message ----- 
  From: Paragon Corporation 
  To: 'PostGIS Users Discussion' 
  Sent: Tuesday, July 21, 2009 11:38 PM
  Subject: Re: [postgis-users] Loop or Cursor


  Bob,

  For this kind of thing -- we usually use a database trigger on the AFTER INSERT row event of the table in question.  You would also probably need an AFTER UPDATE trigger as well since as a child object is being updated,
  you may want to update the parent if the update condition affects the structure/position of the parent.  So in essence you would have  a RECURSIVE trigger that keeps on recursing until the final has no parents.  Though you'd need to be careful to make sure you don't have cyclical redudancies that could trigger an infinite recursion.

  Perhaps if you provided the update statement you are trying to run we could be of more help.

  Leo



------------------------------------------------------------------------------
  From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bob Pawley
  Sent: Tuesday, July 21, 2009 11:19 AM
  To: PostGIS Users Discussion
  Subject: Re: [postgis-users] Loop or Cursor


  Hi Leo

  I'm developing a geometric system as opposed to geographic (basically an engineering drawing).

  I have a fixed field, representing a peice of paper, within which the user can add one to four objects.

  These object are placed within the field according to the total number of objects.

  The first object 'A' is placed in the center and then the user adds lines (one to eight at the moment) as attributes to the object.

  When the user adds a second object, I want to update the attributes of object A to satisfy the new placement of object A.

  When the user places a third object then both, object A and B, need to be updated.

  At the moment, my updates haven't worked as I get  either a null or multiple return.

  Any suggestions for updating the lines for each object would be greatly appreciated.

  Thanks

  Bob


    ----- Original Message ----- 
    From: Paragon Corporation 
    To: 'PostGIS Users Discussion' 
    Sent: Monday, July 20, 2009 7:45 PM
    Subject: Re: [postgis-users] Loop or Cursor


    What kind of operation?  Even an update statement might suffice.  There are many ways to cleverly control the flow of an update that do not necessitate doing a loop or cursor.

    Also keep in mind even within a function used in a  SELECT statement of a query, PostgreSQL allows you to run updates and do all sorts of crazy things like launch perl scripts and bash scripts.  So for example you can mail to your whole membership simple by doing this

    SELECT member_name, send_mail(member_id) As did_it_send
    FROM membership
    WHERE gross_income > 1000000 and active = true


    It is very convenient for making rule processors.

    Leo




----------------------------------------------------------------------------
    From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bob Pawley
    Sent: Monday, July 20, 2009 1:43 PM
    To: PostGIS Users Discussion
    Subject: [postgis-users] Loop or Cursor


    Hi

    I need to access a number of rows, one at a time to perform an operation on each.

    Under what conditions would I consider using a loop command, rather than (or with) a cursor?

    Bob


----------------------------------------------------------------------------


    _______________________________________________
    postgis-users mailing list
    postgis-users at postgis.refractions.net
    http://postgis.refractions.net/mailman/listinfo/postgis-users



------------------------------------------------------------------------------


  _______________________________________________
  postgis-users mailing list
  postgis-users at postgis.refractions.net
  http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090723/03663cf0/attachment.html>