[postgis-users] ST_<function> vs <function>

Tue Sep 11 06:42:14 PDT 2007

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.