[postgis-users] how to define an alias for && operator?

[strk at refractions.net]

On Fri, Nov 04, 2005 at 11:32:00AM -0500, Jean-Pierre Quirion wrote:
> Hi strk,
> Surprise:

This is interesting, thanks for pointing out!
I'll make some tests on older postgresql versions.
--strk;

> 
> explain select count(*) from spot where geometry_overlap(setsrid('BOX3D(7 51,8 52)'::BOX3D,4326),
> _geom);
> "Aggregate  (cost=22308.88..22308.88 rows=1 width=0)"
> "  ->  Seq Scan on spot  (cost=0.00..21615.08 rows=277522 width=0)"
> "        Filter: geometry_overlap('01030...'::geometry, _geom)"
> 
> 
> explain select count(*) from spot where ampamp(setsrid('BOX3D(7 51,8 52)'::BOX3D,4326), _geom);
> "Aggregate  (cost=6069.65..6069.65 rows=1 width=0)"
> "  ->  Index Scan using spot_geom_gist on spot  (cost=0.00..6065.64 rows=1605 width=0)"
> "        Index Cond: ('0103000020E61...'::geometry && _geom)"
> 
> 
> And it is indeed faster and the results are equal, as far as we have tested it.
> 
> Regards
> Jean-Pierre
> 
> 
> 
> --- strk at refractions.net a écrit :
> 
> > I would be surprised if you get that function to use an index.
> > Did you verify it ?
> > --strk;
> > 
> > On Fri, Nov 04, 2005 at 11:03:05AM -0500, Jean-Pierre Quirion wrote:
> > > 
> > > --- Mark Cave-Ayland <m.cave-ayland at webbased.co.uk> a écrit :
> > > 
> > > > 
> > > > > -----Original Message-----
> > > > > From: postgis-users-bounces at postgis.refractions.net 
> > > > > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > > > > Behalf Of Jean-Pierre Quirion
> > > > > Sent: 04 November 2005 15:14
> > > > > To: PostGIS Users Discussion
> > > > > Subject: RE: [postgis-users] how to define an alias for && operator?
> > > > 
> > > > (cut)
> > > > 
> > > > > The "OPERATOR 3  <<< RECHECK," line has been added. And this 
> > > > > does not work. Apparently you can not reuse a 
> > > > > strategy_number, is this right (3 in this case). [My 
> > > > > assumptions are based on 
> > > > > http://www.postgresql.org/docs/8.0/interactive/sql-createopclass.html]
> > > > > Our problem is not the operator (it works), but the 
> > > > > association with an index aka operator class. If we used a 
> > > > > new strategy number like 13, we would have to define a new 
> > > > > strategy in "lwgeom_gist.c" . I am looking for an easier solution.
> > > > > 
> > > > > Jean-Pierre
> > > > 
> > > > 
> > > > Hi Jean-Pierre,
> > > > 
> > > > The operator classes are defined in terms of strategy number so you will be
> > > > unable to do this. The actual modifications in lwgeom_gist.c shouldn't be
> > > > that bad though - you could probably get away with just defining a new
> > > > strategy around line 78, e.g.
> > > > 
> > > > #define RTNewOverlapStrategyNumber		13
> > > > 
> > > > And then simply replace the two occurences of "case
> > > > RTOverlapStrategyNumber:" with
> > > > 
> > > > case RTOverlapStrategyNumber:
> > > > case RTNewOverlapStrategyNumber:
> > > > 
> > > > You should then be able to add your operator by including the following line
> > > > in your operator class:
> > > > 
> > > > "OPERATOR 13  <<< RECHECK"
> > > > 
> > > > Kind regards,
> > > > 
> > > > Mark.
> > > 
> > > Mark,
> > > Thanks for the hint. If we modified lwgeom_gist.c or any other file, updates of PostGis would
> > be
> > > less straight forward.
> > > 
> > > We found - some minutes ago- the following solution for our original problem (avoiding && in a
> > > query).
> > > You probably know that geometry_overlap() does not use the indexes but "&&" does. So we just
> > > define a new function called "ampamp":
> > > 
> > > CREATE function ampamp(geometry,geometry) returns boolean as 'select $1 && $2' language sql;
> > > 
> > > The function ampamp() is as serviceable as geometry_overlap() but uses the same indexes as
> > "&&"
> > > does. 
> > > 
> > > Do you see any inconveniences? What is the reason that postgis does not include indexed
> > versions
> > > of the functions? Maybe nobodies uses the function instead of the operators.
> > > 
> > > Kind Regards
> > > Jean-Pierre
> > > 
> 
> 
> 
> 	
> 
> 	
> 		
> __________________________________________________________
> Lèche-vitrine ou lèche-écran ?
> magasinage.yahoo.ca
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users