R: [postgis-users] ST_<function> vs <function>
P.Rizzi Ag.Mobilità Ambiente
paolo.rizzi at ama-mi.it
Tue Sep 11 07:06:19 PDT 2007
Regina,
thank you very much for your answer!!!
I keep it at hand for the next PostGIS upgrade I'll have to do!!!
For the moment it seems I can assume that using the old form:
<non-ST_> function(geom1,geom2) AND geom1 && geom2
should work the very same with both old and new PostGIS installations.
When old non-ST_ functions will be removed from PostGIS, one will have
to query the DB for the PostGIS version and use the appropriate SQL syntax,
or ask the user which syntax to use. A little unhandy, but not a real problem at all.
For now the old ST_ functions are only deprecated, so they should be OK
to use in new versions, but who knows, maybe a bug will be fixed only in
new ST_ functions, so it's better to stop using older ones as soon as possible...
I'll probably have a new server installed soon, so I'll be able to do some
tests for old VS new PostGIS with tables of some 200.000 polygons
and see what performances are.
Bye
Paolo Rizzi
> -----Messaggio originale-----
> Da: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net]Per
> conto di Obe,
> Regina
> Inviato: martedì 11 settembre 2007 15.42
> A: PostGIS Users Discussion
> Oggetto: RE: [postgis-users] ST_<function> vs <function>
>
>
> Paolo,
>
> The old functions have not been changed to use && so if you
> use the old names, then you need to put in the &&. I'm
> assuming there is no plan in changing this behavior. I think
> prior to 1.2.2 (e.g. 1.2.1 had some ST_ in there) and the &&
> auto-magic was not completely in place so those st_ were just
> aliases for the non-STs so be careful with pre 1.3 versions.
>
> That's a very interesting question whether or not there is a
> performance penalty. I wasn't sure if PostgreSQL planner
> would be smart enough to realize
> that the 2 && calls are redundant and if there would be a
> planning penalty. In theory if PostgreSQL used all powers of
> logic - it could put all these conditions in a matrix groups
> in sets and realize that (a AND a AND a..to infinity) is simply a.
>
> It appears that it is smart enough to do that when looking at
> the generated plans
>
> In the 3 cases I tried - CASE 1: ST_Within(geom1, geom2),
> CASE 2: ST_Within(geom1,geom2) AND geom1 && geom2
> or in CASE 3: If you forgot you already did an && and did it
> yet again ST_Within(geom1,geom2) AND geom1 && geom2 AND
> geom1 && geom2
>
> The planner reduced both to
> geom1 && geom2 AND _ST_WITHIN(geom1, geom2)
>
>
> Now doing trial runs - is kind of indeterminate - if I run
> Within(geom1,geom2)
> ST_Within(geom1,geom2)
> geom1 && geom2 AND ST_Within(geom1,geom2)
>
> They all give slightly different answers on start-up but in
> the end presumably after the plan is cached -- they all have
> exactly the same timing. So I'm guessing there may be a
> planning performance penalty to being redundant, but it seems
> fairly negligable.
>
> Thanks,
> Regina
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of P.Rizzi Ag.Mobilità Ambiente
> Sent: Tuesday, September 11, 2007 5:57 AM
> To: PostGIS Users Discussion
> Subject: R: [postgis-users] ST_<function> vs <function>
>
> > >>> Most if not all of the ST relation functions have
> > automatic index use
> > >>> enabled in them.
>
> So, using the new ST_ functions, one won't have to use &&
> anymore, that's great!!!
> But what about the old non-ST_ functions, are they mapped to
> the new ones or not???
> That is, if I use the old non-ST_ functions will I have to
> use an explicit && or not???
> And the other way around, if I use the new ST_ functions
> _and_ an explicit &&,
> will it be transparently optimized or will I incur a
> performance penalty???
>
> Just to know how to write SQL that works well with both old
> and new PostGIS.
>
> Bye
> Paolo Rizzi
>
>
> > -----Messaggio originale-----
> > Da: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net]Per conto di
> > Barend Köbben
> > Inviato: martedì 11 settembre 2007 8.26
> > A: PostGIS Users Discussion
> > Oggetto: Re: [postgis-users] ST_<function> vs <function>
> >
> >
> > The SQL-MM specification has ST_ as a prefix to all spatial
> > funcionality
> > (types, functions), and the newer PostGIS functions will
> > follow that spec...
> >
> >
> > On 11-09-2007 02:42, "Stephen Woodbridge"
> > <woodbri at swoodbridge.com> wrote:
> >
> > > This is all I could find:
> > >
> > > Log:
> > > Added versions of functions with standard ST (Spatial Type)
> > prefixes to
> > > any functions that were lacking them. Updated the
> > regression tests to
> > > include the new functions.
> > >
> > >
> > > Richard Greenwood wrote:
> > >> I'm embarrassed to ask, but what does "ST" stand for?
> > >>
> > >> Rich
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> On 9/10/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
> > >>>
> > >>>
> > >>> Steve,
> > >>>
> > >>> Just to add to what Mark said. There is a little bit
> > more going on than
> > >>> simply a renaming.
> > >>>
> > >>> Most if not all of the ST relation functions have
> > automatic index use
> > >>> enabled in them.
> > >>>
> > >>> For example
> > >>>
> > >>> the old within is really closer to the hidden _ST_Within
> > and the new
> > >>> ST_Within function is equivalent to writing the old
> > >>>
> > >>> geom1 && geom2 AND Within(geom1, geom2)
> > >>>
> > >>> Same goes for ST_Contains, ST_Overlap etc.
> > >>>
> > >>> Hope that helps,
> > >>> Regina
> > >>>
> > >>> ________________________________
> > >>> From: postgis-users-bounces at postgis.refractions.net on
> > >>> behalf of Mark Cave-Ayland
> > >>> Sent: Sun 9/9/2007 4:30 PM
> > >>> To: PostGIS Users Discussion
> > >>> Subject: Re: [postgis-users] ST_<function> vs <function>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> On Sun, 2007-09-09 at 16:03 -0400, Stephen Woodbridge wrote:
> > >>>> Hi all,
> > >>>>
> > >>>> I seem to have missed reading some vital post or something.
> > >>>>
> > >>>> What is the difference between ST_<function>() and
> <function>()?
> > >>>> Is this just a renaming for namespace issues?
> > >>>> Are the <function>() names deprecated?
> > >>>> Are there other benefits to this renaming?
> > >>>>
> > >>>> Please explain or point me to the relevant docs.
> > >>>>
> > >>>> Thanks,
> > >>>> -Steve
> > >>>
> > >>> Hi Stephen,
> > >>>
> > >>> I don't remember there being much discussion about this, but the
> > >>> relevant commit can be found here:
> > >>>
> http://postgis.refractions.net/pipermail/postgis-commits/2007-
> June/000074..ht
> >>> ml.
> >>> In short, the old function names are deprecated, and new
> applications should
> >>> start to use the ST_ prefix functions instead.
> >>>
> >>>
> >>> ATB,
> >>>
> >>> Mark.
> >>>
> >>> --
> >>> ILande - Open Source Consultancy
> >>> http://www.ilande.co.uk
> >>>
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>>
> >>>
> >>>
> >>> ________________________________
> >>>
> >>>
> >>>
> >>>
> >>> The substance of this message, including any attachments, may be
> >>> confidential, legally privileged and/or exempt from
> disclosure pursuant to
> >>> Massachusetts law. It is intended solely for the
> addressee. If you received
> >>> this in error, please contact the sender and delete the
> material from any
> >>> computer.
> >>> _______________________________________________
> >>> 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
>
> _______________________________________________
> 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
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
> _______________________________________________
> 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