[postgis-devel] PG7.4 ordering operator

[strk]

pramsey wrote:
> Whoa, slow down there.

Sorry.. I'm just too ahead of you guys in terms of timezone ;)

> So far, the only thing you have found is that in 7.4 "order by" does 
> not work against geometries.

Also select DISTINCT and GROUP BY and (worst then everything else)
SELECT the_geom FROM table1 UNION SELECT the_geom FROM table2

> - Ordering might be nice to not have a failure, but frankly the 
> ordering would be meaningless, so might as well let it fail.

> - Equality operators should be left alone, they are doing when they 
> should do in an index context.

GiST uses <<, &<, &&, &>, >>.

Operators <, =, > are only used by DISTINCT, GROUP BY and ORDER BY in 
postgresql prior to version 7.4, where operators named exactly like
that where the default operators used for these funcionalities.

If we drop their definition in pg<7.4 everything will work
as in current 7.4 environment (no relational unions, no group by,
no order by, no distinct). 

On the other hand, if we want to provide some sort of support for
those SQL keywords, we should decide a semantic and document it.

Current implementation have geometry_{lt,gt,eq} where equality is
tested for LLB point.

I've modified postgis_ops.c sligthly changing
these function semantic and adding geometry_{le,ge} for <= and >=
operators. The new semantic is still NOT exact and still QUICK, but
equality is obtained when operators bounding boxes are approximately
the same (floating-point based approximation).

Also I added a geometry_cmp support function needed to define a default
btree operator class which would make PG7.4 functionality in sync with
older.

I finally modified postgis_sql_common.sql.in to contain new <= and >=
operators definitions fixing COMMUTATOR and adding NEGATOR specifications
*and* postgis_sql_74_end.sql.in to define  a default btree operator class.

Please let me know if I broke anything ;)

--strk;

> - Equals() provides a "true" test of equality, if such a thing is 
> needed.
> I am open to being proved otherwise mistaken.
>
> Paul
> 
> On Wednesday, November 19, 2003, at 06:05 AM, strk wrote:
> 
> > Ops.. I forgot to tell the info source:
> > http://www.postgresql.org/docs/7.4/interactive/xindex.html
> >
> > Another thing: in order to make a default btree operator class
> > a geometry_compare function will be also needed.
> >
> > --strk;
> >
> > strk wrote:
> >>
> >> Found the problem:
> >>> When there is no default [B-tree] operator class for a data type,
> >>> you will get errors like "could not identify an ordering operator"
> >>> if you try to use these SQL features [DISTINCT, GROUP BY, ORDER BY]
> >>> with the data type.
> >>> [...]
> >>> Note:  In PostgreSQL versions before 7.4, sorting and grouping 
> >>> operations
> >>> would implicitly use operators named =, <, and >. The new behavior of
> >>> relying on default operator classes avoids having to make any 
> >>> assumption
> >>> about the behavior of operators with particular names.
> >>
> >> So we need to define a default b-tree operatoc class in
> >> postgis_sql_74_end.sql.in
> >>
> >> --strk;
> >>
> >> strk wrote:
> >>> Testing postgis support in PG7.4 (2003-11-11)
> >>> I've encountered to this problem:
> >>>
> >>>         ERROR:  could not identify an ordering operator for type 
> >>> geometry
> >>>         HINT:  Use an explicit ordering operator or modify the query.
> >>>
> >>> Whenever I issue one of these commands:
> >>>
> >>> 	gis=# select the_geom from table1 UNION select the_geom from table2;
> >>> 	gis=# select DISTINCT the_geom from table1;
> >>> 	gis=# select the_geom from table1 ORDER BY the_geom;
> >>>
> >>> Operators '<', '>', '=' are available:
> >>>
> >>> 	 oprname | leftoperand | rightoperand
> >>> 	---------+-------------+--------------
> >>> 	 <<      | geometry    | geometry
> >>> 	 &<      | geometry    | geometry
> >>> 	 &&      | geometry    | geometry
> >>> 	 &>      | geometry    | geometry
> >>> 	 >>      | geometry    | geometry
> >>> 	 ~=      | geometry    | geometry
> >>> 	 @       | geometry    | geometry
> >>> 	 ~       | geometry    | geometry
> >>> 	 =       | geometry    | geometry
> >>> 	 <       | geometry    | geometry
> >>> 	 >       | geometry    | geometry
> >>> 	(11 rows)
> >>>
> >>> Previous PG versions does not show this problem.
> >>> Any hint on what might be missing ?
> >>>
> >>> --strk;
> >>>
> >>> _______________________________________________
> >>> postgis-devel mailing list
> >>> postgis-devel at postgis.refractions.net
> >>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
> >>
> >> _______________________________________________
> >> postgis-devel mailing list
> >> postgis-devel at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-devel
> >
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> >
>       Paul Ramsey
>       Refractions Research
>       Email: pramsey at refractions.net
>       Phone: (250) 885-0632
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel