[postgis-devel] PG7.4 ordering operator

[Paul Ramsey]

Whoa, slow down there.
So far, the only thing you have found is that in 7.4 "order by" does 
not work against geometries.
Well, that is fine.  An A->B ordering of geometries is meaningless.  
Geometries have no order, they cannot be laid out on a single axis in a 
clean A < B manner.
In fact, that is why we have a GiST rtree index and not a default btree 
index -- you cannot efficiently index geometry with a btree.
Also, in terms of an "equals" operator. From the point of view of the 
index, the only equality of interest is the bounding box equality. 
Perfect equality is beside the point from an indexing point of view.  
 From a users point of view, if they want a perfect equality test, they 
should use the GEOS equals() function, not a postgresql operator.
I do not think there are any issues here that actually need to be 
addressed.
- 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.
- 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