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

[Jean-Pierre Quirion]

Hi strk,
Surprise:

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