[postgis-users] Is that possible a function to behave differently inside and outside another main function code?
Rodrigo Sperb
rodrigosperb at gmail.com
Fri Nov 13 06:22:26 PST 2009
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 <rodrigosperb at gmail.com>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 <rodrigosperb at gmail.com>
>> Subject: Re: [postgis-users] Is that possible a function to behave
>> differently inside and outside another main function code?
>> To: postgis-users at postgis.refractions.net
>> Message-ID: <26320274.post at talk.nabble.com>
>>
>> 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 <kneufeld at refractions.net
>> >> <mailto:kneufeld at refractions.net>>
>>
>> >> Subject: Re: [postgis-users] Is that possible a function to behave
>> >> differently inside and outside another main function code?
>> >> To: PostGIS Users Discussion
>> >> <postgis-users at postgis.refractions.net
>> >> <mailto:postgis-users at postgis.refractions.net>>
>>
>> >> Message-ID: <4AF6FF11.3080607 at refractions.net
>> >> <mailto:4AF6FF11.3080607 at refractions.net>>
>>
>> >> 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 <rodrigosperb at gmail.com
>> >> <mailto:rodrigosperb at gmail.com>>
>>
>> >> Subject: Re: [postgis-users] Is that possible a function to behave
>> >> differently inside and outside another main function code?
>> >> To: postgis-users at postgis.refractions.net
>> >> <mailto:postgis-users at postgis.refractions.net>
>>
>> >> Message-ID: <26255804.post at talk.nabble.com
>> >> <mailto:26255804.post at talk.nabble.com>>
>>
>> >> 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
>> >> > postgis-users at postgis.refractions.net
>> >> <mailto:postgis-users at postgis.refractions.net>
>>
>> >> > 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
>> >> postgis-users at postgis.refractions.net
>> >> <mailto:postgis-users at postgis.refractions.net>
>>
>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >>
>> >>
>> >> End of postgis-users Digest, Vol 87, Issue 9
>> >> ********************************************
>> >>
>> >>
>> >>
>> ------------------------------------------------------------------------
>>
>> >>
>> >> _______________________________________________
>> >> 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
>> >
>> >
>>
>> --
>> 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 <elmadrehado at hotmail.com>
>> Subject: [postgis-users] How to Get a coordinates from a Geometry
>> field?
>>
>> To: <postgis-users at postgis.refractions.net>
>> Message-ID: <SNT127-W29758BA34C6B271226E824BBA90 at phx.gbl>
>> 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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>> End of postgis-users Digest, Vol 87, Issue 13
>> *********************************************
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091113/dff3da29/attachment.html>
More information about the postgis-users
mailing list