Hello Kevin, Yes, I know a DISTINCT FOLLOWING a UNION ALL would be the same as UNION, but I just wanted to try out in case of some sort of bug.
Well, I'm quite sure that the input have at least 2 points. Because they are "bigger" functions (implemented as LINESTRING) that I cut a part (and my RAISE NOTICEs during the code seem to indicate that this cutting performs correctly), so they will have at least 2-points, start and end of the X interval in which I cut the function to give as input. I really cannot understand what goes wrong, I'm affraid. Rodrigo Sperb Kevin Neufeld wrote: > > A DISTINCT and a UNION ALL will yield the same results as a straight up > UNION. I didn't realize that you need to have duplicates removed. > > In that case, my guess is that the issue is with your input data. > You're selecting ST_X from $1, ST_X from $2 and ST_MaxX from $1. Have > you verified that this always yields at least two distinct X values with > your data? Try replacing the first SELECT clause with a simple "SELECT > xy.t" and add a "GROUP BY xy.t HAVING count(*) < 2" at the end to > identify all erroneous input data values. > > Cheers, > Kevin > > Rodrigo Sperb wrote: >> Hello, >> >> Following what Kevin said about UNION ALL, I have tried to change the >> code (below) using UNION ALL and then SELECT DISTINCT (as I need the >> same X may be in both functions and I don't want a replicate. But I >> still ge the same error (eventual single-point Linestring that should >> never happen. Here is the code (so that I don't need to look-up the >> previous message: >> >> ...header... >> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,xy.at >> <http://xy.at/>))) >> >> FROM (SELECT q.t, dr_delay_value($1,q.t) + >> dr_delay_value($3,dr_delay_value($1,q.t)) AS at >> >> --- dr_delay_value is a simple look-up function for a certain X >> value.... >> FROM (SELECT st_X(st_PointN($1,n)) AS t >> FROM generate_series(1,st_NumPoints($1)) AS h(n) >> UNION >> SELECT st_X(st_PointN($2,n)) AS t >> FROM generate_series(1,st_NumPoints($2)) AS h(n) >> UNION >> SELECT st_Xmax($1) AS t >> ) AS q ORDER BY q.t) AS xy >> ...bottom... >> >> >> I then changed it to: >> >> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,xy.at >> <http://xy.at/>))) >> >> FROM (SELECT DISTINCT q.t, dr_delay_value($1,q.t) + >> dr_delay_value($3,dr_delay_value($1,q.t)) AS at >> >> --- dr_delay_value is a simple look-up function for a certain X >> value.... >> FROM (SELECT st_X(st_PointN($1,n)) AS t >> FROM generate_series(1,st_NumPoints($1)) AS h(n) >> UNION ALL >> SELECT st_X(st_PointN($2,n)) AS t >> FROM generate_series(1,st_NumPoints($2)) AS h(n) >> UNION >> SELECT st_Xmax($1) AS t >> ) AS q ORDER BY q.t) AS xy >> ...bottom... >> >> >> And I know what went inside when I got the error was: >> >> Inputs of function: >> $1 = LINESTRING(28800 28809.0366506299,28826.9908145614 28836.029580065) >> $2 = LINESTRING(28800 45.4281818181818,28826.9908145614 45.4299607582325) >> $3 = LINESTRING(0 43.53,52800 47.01,62700 74.87,86400 43.53) - edge >> delay function >> >> Output: >> result = >> LINESTRING(28800 28854.4654280455) - thus is missing a pair >> X=28826.9908145614, Y = 28836.029580065 + Y of $3 for 28836.029580065 >> >> But here is what I find most intriguing: if I simulate the inputs >> above outside of the main function (in which this one that is >> returning an eventual error runs), it simply works. >> >> SELECT >> st_AsText(dr_sum_arrivaltime_edgedelay(st_GeometryFromText('LINESTRING(28800 >> 28809.0366506299,28826.9908145614 28836.029580065)'), >> >> st_GeometryFromText('LINESTRING(28800 >> 45.4281818181818,28826.9908145614 45.4299607582325)'), >> st_GeometryFromText('LINESTRING(0 >> 43.53,52800 47.01,62700 74.87,86400 43.53)'))) >> >> = "LINESTRING(28800 28854.4654280455,28826.9908145614 28881.46013656)" >> >> I hope anyone can give me a clue on that one. It's sort of really >> bothering already, as I can't imagine why that happens... >> >> Best regards, >> >> Rodrigo Sperb >> >> >> >> >> >> ------------------------------ >> >> Message: 7 >> Date: Sun, 08 Nov 2009 09:25:37 -0800 >> From: Kevin Neufeld <[email protected] >> <mailto:[email protected]>> >> Subject: Re: [postgis-users] Is that possible a function to behave >> differently inside and outside another main function code? >> To: PostGIS Users Discussion >> <[email protected] >> <mailto:[email protected]>> >> Message-ID: <[email protected] >> <mailto:[email protected]>> >> Content-Type: text/plain; charset=ISO-8859-1; format=flowed >> >> Are you sure you want to use "UNION" and not "UNION ALL"? The former >> will remove duplicates, the latter does not. It's conceivable >> that when >> UNIONed, the three SELECT st_X clauses will return a single value. >> Collected and put through ST_LineFromMultiPoint would probably >> result in >> a single point line (depending on which version of PostGIS you are >> using >> - the newer versions will ERROR with "geometry requires more >> points"). >> >> Hope that helps, >> Kevin >> >> rodrigosperb wrote: >> > Hello, >> > >> > I have a bit of a problem that is sort of driving me crazy. I >> need to >> > perform an "addition of two (mathematical) functions". I >> represent them as >> > linestrings in my solution, and it is part of another bigger >> function. The >> > code is as follows: >> > >> > ...header... >> > SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,xy.at >> <http://xy.at>))) >> > FROM (SELECT q.t, dr_delay_value($1,q.t) + >> > dr_delay_value($3,dr_delay_value($1,q.t)) AS at >> > FROM (SELECT st_X(st_PointN($1,n)) AS t >> > FROM generate_series(1,st_NumPoints($1)) AS h(n) >> > UNION >> > SELECT st_X(st_PointN($2,n)) AS t >> > FROM generate_series(1,st_NumPoints($2)) AS h(n) >> > UNION >> > SELECT st_Xmax($1) AS t >> > ) AS q ORDER BY q.t) AS xy >> > ...bottom... >> > dr_delay_value() is simply a look-up function that takes the Y >> value for a >> > certain X. >> > >> > The thing is that eventually this fuction is failing on >> returning more >> > specifically a 2-points linestring (that sould) and returns only >> a single >> > point one. Now, I have prepared a "wrapper" PL/Pgsql function to >> keep track >> > of what is passed to that function (perhaps that was the reason >> for the >> > error. With that I'm pretty much sure that the arguments passed >> are fine, >> > and still get the same error... Strangely, with my wrapper >> function keeping >> > track of the arguments passed to the function I was able to try >> out to run >> > the same request (that inside of the bigger function fails) >> separately, and >> > guess what? is simply works!! >> > >> > I hope anyone may have a clue of what is going on. That's a very >> strange >> > behavior, I would say. >> > >> > Regards, >> > >> > Rodrigo Sperb >> > >> >> >> ------------------------------ >> >> Message: 8 >> Date: Sun, 8 Nov 2009 09:26:41 -0800 (PST) >> From: rodrigosperb <[email protected] >> <mailto:[email protected]>> >> Subject: Re: [postgis-users] Is that possible a function to behave >> differently inside and outside another main function code? >> To: [email protected] >> <mailto:[email protected]> >> Message-ID: <[email protected] >> <mailto:[email protected]>> >> Content-Type: text/plain; charset=us-ascii >> >> >> Hi Kevin, >> >> Thanks for your repply. I can't be sure whether the two functions >> have the >> same X value, and I don't want them twice, that's why I was using >> UNION, >> instead of UNION ALL (which is much faster even...). >> >> But what you said make some sense. Do you think if use first a >> UNION ALL and >> then in the outer query (when I order by q.t) I use DISTINCT may >> work? >> >> I think I will try it out. >> >> Thanks again for the help. >> >> Rodrigo Sperb >> >> >> >> Kevin Neufeld wrote: >> > >> > Are you sure you want to use "UNION" and not "UNION ALL"? The >> former >> > will remove duplicates, the latter does not. It's conceivable >> that when >> > UNIONed, the three SELECT st_X clauses will return a single value. >> > Collected and put through ST_LineFromMultiPoint would probably >> result in >> > a single point line (depending on which version of PostGIS you >> are using >> > - the newer versions will ERROR with "geometry requires more >> points"). >> > >> > Hope that helps, >> > Kevin >> > >> > rodrigosperb wrote: >> >> Hello, >> >> >> >> I have a bit of a problem that is sort of driving me crazy. I >> need to >> >> perform an "addition of two (mathematical) functions". I >> represent them >> >> as >> >> linestrings in my solution, and it is part of another bigger >> function. >> >> The >> >> code is as follows: >> >> >> >> ...header... >> >> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,xy.at >> <http://xy.at>))) >> >> FROM (SELECT q.t, dr_delay_value($1,q.t) + >> >> dr_delay_value($3,dr_delay_value($1,q.t)) AS at >> >> FROM (SELECT st_X(st_PointN($1,n)) AS t >> >> FROM generate_series(1,st_NumPoints($1)) AS h(n) >> >> UNION >> >> SELECT st_X(st_PointN($2,n)) AS t >> >> FROM generate_series(1,st_NumPoints($2)) AS h(n) >> >> UNION >> >> SELECT st_Xmax($1) AS t >> >> ) AS q ORDER BY q.t) AS xy >> >> ...bottom... >> >> dr_delay_value() is simply a look-up function that takes the Y >> value for >> >> a >> >> certain X. >> >> >> >> The thing is that eventually this fuction is failing on >> returning more >> >> specifically a 2-points linestring (that sould) and returns >> only a single >> >> point one. Now, I have prepared a "wrapper" PL/Pgsql function >> to keep >> >> track >> >> of what is passed to that function (perhaps that was the reason >> for the >> >> error. With that I'm pretty much sure that the arguments passed >> are fine, >> >> and still get the same error... Strangely, with my wrapper >> function >> >> keeping >> >> track of the arguments passed to the function I was able to try >> out to >> >> run >> >> the same request (that inside of the bigger function fails) >> separately, >> >> and >> >> guess what? is simply works!! >> >> >> >> I hope anyone may have a clue of what is going on. That's a >> very strange >> >> behavior, I would say. >> >> >> >> Regards, >> >> >> >> Rodrigo Sperb >> >> >> > _______________________________________________ >> > postgis-users mailing list >> > [email protected] >> <mailto:[email protected]> >> > http://postgis.refractions.net/mailman/listinfo/postgis-users >> > >> > >> >> -- >> View this message in context: >> >> http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26255804.html >> Sent from the PostGIS - User mailing list archive at Nabble.com. >> >> >> >> ------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> <mailto:[email protected]> >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> End of postgis-users Digest, Vol 87, Issue 9 >> ******************************************** >> >> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- View this message in context: http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26320274.html Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
