Hello,<br><br>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...<br>
<br>It's a bit os strange behavior of UNION, or am I missing something?<br><br>Thanks Kevin for all the repplies,<br><br>Rodrigo Sperb<br><br><div class="gmail_quote">On Fri, Nov 13, 2009 at 2:54 PM, Rodrigo Sperb <span dir="ltr"><<a href="mailto:rodrigosperb@gmail.com">rodrigosperb@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Hello,<br><br>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:<br>
<br>this request:<br>select st_astext(dr_sum_edgedelay(st_geometryfromtext('LINESTRING(0 10, 50 25, 60 90)'),st_geometryfromtext('LINESTRING(0 10, 55 35, 100 100)'))) <br><br>comes out:<br>"LINESTRING(0 20,50 57.7272727272727,55 92.5,60 132.222222222222)"<br>
<br>and if I put 100 in the first geometry:<br><br>this request:<br>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)'))) <br>
<br>comes out:<br>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)'))) <br><br>Any clue why?<br><br>Rodrigo Sperb<br>
<br><br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
------------------------------<br>
<br>
Message: 16<br>
Date: Thu, 12 Nov 2009 07:06:18 -0800 (PST)<div class="im"><br>
From: rodrigosperb <<a href="mailto:rodrigosperb@gmail.com" target="_blank">rodrigosperb@gmail.com</a>><br>
Subject: Re: [postgis-users] Is that possible a function to behave<br>
differently inside and outside another main function code?<br>
To: <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
Message-ID: <<a href="mailto:26320274.post@talk.nabble.com" target="_blank">26320274.post@talk.nabble.com</a>><div class="im"><br>
Content-Type: text/plain; charset=us-ascii<br>
<br>
<br></div>
Hello Kevin,<br>
<br>
Yes, I know a DISTINCT FOLLOWING a UNION ALL would be the same as UNION, but<br>
I just wanted to try out in case of some sort of bug.<br>
<br>
Well, I'm quite sure that the input have at least 2 points. Because they are<br>
"bigger" functions (implemented as LINESTRING) that I cut a part (and my<br>
RAISE NOTICEs during the code seem to indicate that this cutting performs<br>
correctly), so they will have at least 2-points, start and end of the X<br>
interval in which I cut the function to give as input.<br>
<br>
I really cannot understand what goes wrong, I'm affraid.<div class="im"><br>
<br>
Rodrigo Sperb<br>
<br>
<br>
Kevin Neufeld wrote:<br>
><br></div>
> A DISTINCT and a UNION ALL will yield the same results as a straight up<br>
> UNION. I didn't realize that you need to have duplicates removed.<br>
><br>
> In that case, my guess is that the issue is with your input data.<br>
> You're selecting ST_X from $1, ST_X from $2 and ST_MaxX from $1. Have<br>
> you verified that this always yields at least two distinct X values with<br>
> your data? Try replacing the first SELECT clause with a simple "SELECT<br>
> xy.t" and add a "GROUP BY xy.t HAVING count(*) < 2" at the end to<br>
> identify all erroneous input data values.<br>
><br>
> Cheers,<br>
> Kevin<div class="im"><br>
><br>
> Rodrigo Sperb wrote:<br>
>> Hello,<br>
>><br>
>> Following what Kevin said about UNION ALL, I have tried to change the<br>
>> code (below) using UNION ALL and then SELECT DISTINCT (as I need the<br>
>> same X may be in both functions and I don't want a replicate. But I<br>
>> still ge the same error (eventual single-point Linestring that should<br>
>> never happen. Here is the code (so that I don't need to look-up the<br>
>> previous message:<br>
>><br>
>> ...header...<br>
>> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,<a href="http://xy.at" target="_blank">xy.at</a><br></div>
>> <<a href="http://xy.at/" target="_blank">http://xy.at/</a>>)))<div class="im"><br>
>><br>
>> FROM (SELECT q.t, dr_delay_value($1,q.t) +<br>
>> dr_delay_value($3,dr_delay_value($1,q.t)) AS at<br>
>><br>
>> --- dr_delay_value is a simple look-up function for a certain X<br>
>> value....<br>
>> FROM (SELECT st_X(st_PointN($1,n)) AS t<br>
>> FROM generate_series(1,st_NumPoints($1)) AS h(n)<br>
>> UNION<br>
>> SELECT st_X(st_PointN($2,n)) AS t<br>
>> FROM generate_series(1,st_NumPoints($2)) AS h(n)<br>
>> UNION<br>
>> SELECT st_Xmax($1) AS t<br>
>> ) AS q ORDER BY q.t) AS xy<br>
>> ...bottom...<br>
>><br>
>><br>
>> I then changed it to:<br>
>><br>
>> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,<a href="http://xy.at" target="_blank">xy.at</a><br></div>
>> <<a href="http://xy.at/" target="_blank">http://xy.at/</a>>)))<div><div></div><div class="h5"><br>
>><br>
>> FROM (SELECT DISTINCT q.t, dr_delay_value($1,q.t) +<br>
>> dr_delay_value($3,dr_delay_value($1,q.t)) AS at<br>
>><br>
>> --- dr_delay_value is a simple look-up function for a certain X<br>
>> value....<br>
>> FROM (SELECT st_X(st_PointN($1,n)) AS t<br>
>> FROM generate_series(1,st_NumPoints($1)) AS h(n)<br>
>> UNION ALL<br>
>> SELECT st_X(st_PointN($2,n)) AS t<br>
>> FROM generate_series(1,st_NumPoints($2)) AS h(n)<br>
>> UNION<br>
>> SELECT st_Xmax($1) AS t<br>
>> ) AS q ORDER BY q.t) AS xy<br>
>> ...bottom...<br>
>><br>
>><br>
>> And I know what went inside when I got the error was:<br>
>><br>
>> Inputs of function:<br>
>> $1 = LINESTRING(28800 28809.0366506299,28826.9908145614 28836.029580065)<br>
>> $2 = LINESTRING(28800 45.4281818181818,28826.9908145614 45.4299607582325)<br>
>> $3 = LINESTRING(0 43.53,52800 47.01,62700 74.87,86400 43.53) - edge<br>
>> delay function<br>
>><br>
>> Output:<br>
>> result =<br>
>> LINESTRING(28800 28854.4654280455) - thus is missing a pair<br>
>> X=28826.9908145614, Y = 28836.029580065 + Y of $3 for 28836.029580065<br>
>><br>
>> But here is what I find most intriguing: if I simulate the inputs<br>
>> above outside of the main function (in which this one that is<br>
>> returning an eventual error runs), it simply works.<br>
>><br>
>> SELECT<br>
>> st_AsText(dr_sum_arrivaltime_edgedelay(st_GeometryFromText('LINESTRING(28800<br>
>> 28809.0366506299,28826.9908145614 28836.029580065)'),<br>
>><br>
>> st_GeometryFromText('LINESTRING(28800<br>
>> 45.4281818181818,28826.9908145614 45.4299607582325)'),<br>
>> st_GeometryFromText('LINESTRING(0<br>
>> 43.53,52800 47.01,62700 74.87,86400 43.53)')))<br>
>><br>
>> = "LINESTRING(28800 28854.4654280455,28826.9908145614 28881.46013656)"<br>
>><br>
>> I hope anyone can give me a clue on that one. It's sort of really<br>
>> bothering already, as I can't imagine why that happens...<br>
>><br>
>> Best regards,<br>
>><br>
>> Rodrigo Sperb<br>
>><br>
>><br>
>><br>
>><br>
>><br>
>> ------------------------------<br>
>><br>
>> Message: 7<br>
>> Date: Sun, 08 Nov 2009 09:25:37 -0800<br>
>> From: Kevin Neufeld <<a href="mailto:kneufeld@refractions.net" target="_blank">kneufeld@refractions.net</a><br></div></div>
>> <mailto:<a href="mailto:kneufeld@refractions.net" target="_blank">kneufeld@refractions.net</a>>><div class="im"><br>
>> Subject: Re: [postgis-users] Is that possible a function to behave<br>
>> differently inside and outside another main function code?<br>
>> To: PostGIS Users Discussion<br>
>> <<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
>> <mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>><div class="im"><br>
>> Message-ID: <<a href="mailto:4AF6FF11.3080607@refractions.net" target="_blank">4AF6FF11.3080607@refractions.net</a><br></div>
>> <mailto:<a href="mailto:4AF6FF11.3080607@refractions.net" target="_blank">4AF6FF11.3080607@refractions.net</a>>><div class="im"><br>
>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>
>><br>
>> Are you sure you want to use "UNION" and not "UNION ALL"? The former<br>
>> will remove duplicates, the latter does not. It's conceivable<br>
>> that when<br>
>> UNIONed, the three SELECT st_X clauses will return a single value.<br>
>> Collected and put through ST_LineFromMultiPoint would probably<br>
>> result in<br>
>> a single point line (depending on which version of PostGIS you are<br>
>> using<br>
>> - the newer versions will ERROR with "geometry requires more<br>
>> points").<br>
>><br>
>> Hope that helps,<br>
>> Kevin<br>
>><br>
>> rodrigosperb wrote:<br>
>> > Hello,<br>
>> ><br>
>> > I have a bit of a problem that is sort of driving me crazy. I<br>
>> need to<br>
>> > perform an "addition of two (mathematical) functions". I<br>
>> represent them as<br>
>> > linestrings in my solution, and it is part of another bigger<br>
>> function. The<br>
>> > code is as follows:<br>
>> ><br>
>> > ...header...<br>
>> > SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,<a href="http://xy.at" target="_blank">xy.at</a><br></div>
>> <<a href="http://xy.at" target="_blank">http://xy.at</a>>)))<div><div></div><div class="h5"><br>
>> > FROM (SELECT q.t, dr_delay_value($1,q.t) +<br>
>> > dr_delay_value($3,dr_delay_value($1,q.t)) AS at<br>
>> > FROM (SELECT st_X(st_PointN($1,n)) AS t<br>
>> > FROM generate_series(1,st_NumPoints($1)) AS h(n)<br>
>> > UNION<br>
>> > SELECT st_X(st_PointN($2,n)) AS t<br>
>> > FROM generate_series(1,st_NumPoints($2)) AS h(n)<br>
>> > UNION<br>
>> > SELECT st_Xmax($1) AS t<br>
>> > ) AS q ORDER BY q.t) AS xy<br>
>> > ...bottom...<br>
>> > dr_delay_value() is simply a look-up function that takes the Y<br>
>> value for a<br>
>> > certain X.<br>
>> ><br>
>> > The thing is that eventually this fuction is failing on<br>
>> returning more<br>
>> > specifically a 2-points linestring (that sould) and returns only<br>
>> a single<br>
>> > point one. Now, I have prepared a "wrapper" PL/Pgsql function to<br>
>> keep track<br>
>> > of what is passed to that function (perhaps that was the reason<br>
>> for the<br>
>> > error. With that I'm pretty much sure that the arguments passed<br>
>> are fine,<br>
>> > and still get the same error... Strangely, with my wrapper<br>
>> function keeping<br>
>> > track of the arguments passed to the function I was able to try<br>
>> out to run<br>
>> > the same request (that inside of the bigger function fails)<br>
>> separately, and<br>
>> > guess what? is simply works!!<br>
>> ><br>
>> > I hope anyone may have a clue of what is going on. That's a very<br>
>> strange<br>
>> > behavior, I would say.<br>
>> ><br>
>> > Regards,<br>
>> ><br>
>> > Rodrigo Sperb<br>
>> ><br>
>><br>
>><br>
>> ------------------------------<br>
>><br>
>> Message: 8<br>
>> Date: Sun, 8 Nov 2009 09:26:41 -0800 (PST)<br>
>> From: rodrigosperb <<a href="mailto:rodrigosperb@gmail.com" target="_blank">rodrigosperb@gmail.com</a><br></div></div>
>> <mailto:<a href="mailto:rodrigosperb@gmail.com" target="_blank">rodrigosperb@gmail.com</a>>><div class="im"><br>
>> Subject: Re: [postgis-users] Is that possible a function to behave<br>
>> differently inside and outside another main function code?<br>
>> To: <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
>> <mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="im"><br>
>> Message-ID: <<a href="mailto:26255804.post@talk.nabble.com" target="_blank">26255804.post@talk.nabble.com</a><br></div>
>> <mailto:<a href="mailto:26255804.post@talk.nabble.com" target="_blank">26255804.post@talk.nabble.com</a>>><div><div></div><div class="h5"><br>
>> Content-Type: text/plain; charset=us-ascii<br>
>><br>
>><br>
>> Hi Kevin,<br>
>><br>
>> Thanks for your repply. I can't be sure whether the two functions<br>
>> have the<br>
>> same X value, and I don't want them twice, that's why I was using<br>
>> UNION,<br>
>> instead of UNION ALL (which is much faster even...).<br>
>><br>
>> But what you said make some sense. Do you think if use first a<br>
>> UNION ALL and<br>
>> then in the outer query (when I order by q.t) I use DISTINCT may<br>
>> work?<br>
>><br>
>> I think I will try it out.<br>
>><br>
>> Thanks again for the help.<br>
>><br>
>> Rodrigo Sperb<br>
>><br>
>><br>
>><br>
>> Kevin Neufeld wrote:<br>
>> ><br>
>> > Are you sure you want to use "UNION" and not "UNION ALL"? The<br>
>> former<br>
>> > will remove duplicates, the latter does not. It's conceivable<br>
>> that when<br>
>> > UNIONed, the three SELECT st_X clauses will return a single value.<br>
>> > Collected and put through ST_LineFromMultiPoint would probably<br>
>> result in<br>
>> > a single point line (depending on which version of PostGIS you<br>
>> are using<br>
>> > - the newer versions will ERROR with "geometry requires more<br>
>> points").<br>
>> ><br>
>> > Hope that helps,<br>
>> > Kevin<br>
>> ><br>
>> > rodrigosperb wrote:<br>
>> >> Hello,<br>
>> >><br>
>> >> I have a bit of a problem that is sort of driving me crazy. I<br>
>> need to<br>
>> >> perform an "addition of two (mathematical) functions". I<br>
>> represent them<br>
>> >> as<br>
>> >> linestrings in my solution, and it is part of another bigger<br>
>> function.<br>
>> >> The<br>
>> >> code is as follows:<br>
>> >><br>
>> >> ...header...<br>
>> >> SELECT st_LineFromMultiPoint(st_Collect(st_MakePoint(xy.t,<a href="http://xy.at" target="_blank">xy.at</a><br></div></div>
>> <<a href="http://xy.at" target="_blank">http://xy.at</a>>)))<div><div></div><div class="h5"><br>
>> >> FROM (SELECT q.t, dr_delay_value($1,q.t) +<br>
>> >> dr_delay_value($3,dr_delay_value($1,q.t)) AS at<br>
>> >> FROM (SELECT st_X(st_PointN($1,n)) AS t<br>
>> >> FROM generate_series(1,st_NumPoints($1)) AS h(n)<br>
>> >> UNION<br>
>> >> SELECT st_X(st_PointN($2,n)) AS t<br>
>> >> FROM generate_series(1,st_NumPoints($2)) AS h(n)<br>
>> >> UNION<br>
>> >> SELECT st_Xmax($1) AS t<br>
>> >> ) AS q ORDER BY q.t) AS xy<br>
>> >> ...bottom...<br>
>> >> dr_delay_value() is simply a look-up function that takes the Y<br>
>> value for<br>
>> >> a<br>
>> >> certain X.<br>
>> >><br>
>> >> The thing is that eventually this fuction is failing on<br>
>> returning more<br>
>> >> specifically a 2-points linestring (that sould) and returns<br>
>> only a single<br>
>> >> point one. Now, I have prepared a "wrapper" PL/Pgsql function<br>
>> to keep<br>
>> >> track<br>
>> >> of what is passed to that function (perhaps that was the reason<br>
>> for the<br>
>> >> error. With that I'm pretty much sure that the arguments passed<br>
>> are fine,<br>
>> >> and still get the same error... Strangely, with my wrapper<br>
>> function<br>
>> >> keeping<br>
>> >> track of the arguments passed to the function I was able to try<br>
>> out to<br>
>> >> run<br>
>> >> the same request (that inside of the bigger function fails)<br>
>> separately,<br>
>> >> and<br>
>> >> guess what? is simply works!!<br>
>> >><br>
>> >> I hope anyone may have a clue of what is going on. That's a<br>
>> very strange<br>
>> >> behavior, I would say.<br>
>> >><br>
>> >> Regards,<br>
>> >><br>
>> >> Rodrigo Sperb<br>
>> >><br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div></div>
>> <mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="im"><br>
>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>> ><br>
>> ><br>
>><br>
>> --<br>
>> View this message in context:<br>
>><br>
>> <a href="http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26255804.html" target="_blank">http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26255804.html</a><br>
>> Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
>><br>
>><br>
>><br>
>> ------------------------------<br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br></div>
>> <mailto:<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><div class="im"><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>><br>
>><br>
>> End of postgis-users Digest, Vol 87, Issue 9<br>
>> ********************************************<br>
>><br>
>><br></div>
>> ------------------------------------------------------------------------<div class="im"><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
<br>
--<br></div>
View this message in context: <a href="http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26320274.html" target="_blank">http://old.nabble.com/Is-that-possible-a-function-to-behave-differently-inside-and-outside-another-main-function-code--tp26251542p26320274.html</a><div class="im">
<br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
<br>
<br>
<br>
------------------------------<br>
<br></div>
Message: 17<br>
Date: Thu, 12 Nov 2009 19:57:55 +0000<br>
From: Ivan Caballero Cano <<a href="mailto:elmadrehado@hotmail.com" target="_blank">elmadrehado@hotmail.com</a>><br>
Subject: [postgis-users] How to Get a coordinates from a Geometry<br>
field?<div class="im"><br>
To: <<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br></div>
Message-ID: <SNT127-W29758BA34C6B271226E824BBA90@phx.gbl><br>
Content-Type: text/plain; charset="iso-8859-1"<br>
<br>
<br>
Good Day!<br>
<br>
Excuseme by my wrong English.<br>
<br>
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"<br>
and I want to get the coordinates by a SQL consult.<br>
<br>
Select *, coordinateY, CoordinateX from table where "somethin"<br>
<br>
Can you get some orientation about this??<br>
<br>
Thanks<br>
<br>
_________________________________________________________________<br>
Comparte tu vida en Perfil de Windows Live. Actual?zalo ya!<br>
<a href="http://www.actualizatuperfil.com.mx/" target="_blank">http://www.actualizatuperfil.com.mx/</a><br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://postgis.refractions.net/pipermail/postgis-users/attachments/20091112/003edf66/attachment-0001.html" target="_blank">http://postgis.refractions.net/pipermail/postgis-users/attachments/20091112/003edf66/attachment-0001.html</a>><div class="im">
<br>
<br>
------------------------------<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<br></div>
End of postgis-users Digest, Vol 87, Issue 13<br>
*********************************************<br>
</blockquote></div><br>
</blockquote></div><br>