[postgis-users] Is that possible a function to behave differently inside and outside another main function code?
Kevin Neufeld
kneufeld at refractions.net
Wed Nov 11 23:19:48 PST 2009
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
>
More information about the postgis-users
mailing list