[postgis-users] RE: Compare data in one table

[Broun Uganda]

That was spot on, didn't need to change a thing
One part is still confusing me though, I need to show the offended line but when i try i get an error that i have to GROUP BY or use Aggregate function
So i decided to write like this but the min thing is an error that wont always be correct.

SELECT link_name,  from_node, b.link_diameter, offended_link, a.in_diameter  FROM
  (SELECT  to_node, min(link_name) AS offended_link, max(diameter) AS in_diameter FROM links GROUP BY to_node) AS a,
  (SELECT link_name, from_node, diameter AS link_diameter FROM links) AS b
WHERE a.to_node = b.from_node AND b.link_diameter < a.in_diameter;

The target here is: Link 10 from node7 is of size12 while the previous link 9 has size13 (Link 9 is one of the links that have node7 as their to_node).

Broun Uganda


From: tekuganda at hotmail.com
To: postgis-users at postgis.refractions.net
Subject: Compare data in one table
Date: Sat, 30 Aug 2008 00:23:33 +0300








I am trying to develop a trigger function that searches for lines with particular properties. I have two or more lines joining at a point but only one line exiting from that point. I want to check that the size of the outgoing (exiting) line is always not smaller than incoming lines.
Take such data as example:
line_name from_point to_point size
line1        p1              p2          2
line2        p10             p2         3
line3        p2            p7            1
line4        p7            p8            3

Lines Line1 and Line2 join at point p2 (to_point), therefore line3 should have size at least equal to 3.
Tried to use this select statement but doesnt work, could anyone give me an idea
Select link_name, diameter, from_node, to_node from links as ends where ends.diameter< links.diameter;
or even
SELECT   from_node from links as dstream  intersect SELECT   to_node from links as previo ;

Thank you

Broun Uganda

Discover the new Windows Vista Learn more!

_________________________________________________________________
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080830/67e9d3f9/attachment.html>