Hello, Now I think I could overcome it... It looks like the problem is on having the UNION embedded in the subquery to get the points of both geometries. If I do it geometry by geometry and then make the UNION, in my tests worked well...
It's a bit os strange behavior of UNION, or am I missing something? Thanks Kevin for all the repplies, Rodrigo Sperb On Fri, Nov 13, 2009 at 2:54 PM, Rodrigo Sperb <[email protected]>wrote: > Hello, > > I think I might have found out the error...Doing other stuff, but in which > I use the same principle of the mentioned function below (get x values point > by point of two geometries, make a union of them...) I noticed that again > eventually one point is missing. In this case I noticed that points are lost > somehow: > > this request: > select st_astext(dr_sum_edgedelay(st_geometryfromtext('LINESTRING(0 10, 50 > 25, 60 90)'),st_geometryfromtext('LINESTRING(0 10, 55 35, 100 100)'))) > > comes out: > "LINESTRING(0 20,50 57.7272727272727,55 92.5,60 132.222222222222)" > > and if I put 100 in the first geometry: > > this request: > select st_astext(dr_sum_edgedelay(st_geometryfromtext('LINESTRING(0 10, 50 > 25, 60 90, 100 140)'),st_geometryfromtext('LINESTRING(0 10, 55 35, 60 > 10)'))) > > comes out: > select st_astext(dr_sum_edgedelay(st_geometryfromtext('LINESTRING(0 10, 50 > 25, 60 90, 100 140)'),st_geometryfromtext('LINESTRING(0 10, 55 35, 60 > 10)'))) > > Any clue why? > > Rodrigo Sperb > > > ------------------------------ >> >> Message: 16 >> Date: Thu, 12 Nov 2009 07:06:18 -0800 (PST) >> >> From: rodrigosperb <[email protected]> >> Subject: Re: [postgis-users] Is that possible a function to behave >> differently inside and outside another main function code? >> To: [email protected] >> Message-ID: <[email protected]> >> >> Content-Type: text/plain; charset=us-ascii >> >> >> 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. >> >> >> >> ------------------------------ >> >> Message: 17 >> Date: Thu, 12 Nov 2009 19:57:55 +0000 >> From: Ivan Caballero Cano <[email protected]> >> Subject: [postgis-users] How to Get a coordinates from a Geometry >> field? >> >> To: <[email protected]> >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset="iso-8859-1" >> >> >> Good Day! >> >> Excuseme by my wrong English. >> >> I want to know how to get a coordinates of a spatial field postgis in a >> PostGreSQL table, in other words I have a table with a geometry field named >> "the_geom" >> and I want to get the coordinates by a SQL consult. >> >> Select *, coordinateY, CoordinateX from table where "somethin" >> >> Can you get some orientation about this?? >> >> Thanks >> >> _________________________________________________________________ >> Comparte tu vida en Perfil de Windows Live. Actual?zalo ya! >> http://www.actualizatuperfil.com.mx/ >> -------------- next part -------------- >> An HTML attachment was scrubbed... >> URL: < >> http://postgis.refractions.net/pipermail/postgis-users/attachments/20091112/003edf66/attachment-0001.html >> > >> >> >> ------------------------------ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> End of postgis-users Digest, Vol 87, Issue 13 >> ********************************************* >> > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
