Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#292 closed defect (fixed)

Can not order by geography

Reported by: robe Owned by: pramsey
Priority: medium Milestone: PostGIS 1.5.0
Component: postgis Version: master
Keywords: Cc:


Not sure to consider this a bug or not, except that I can order by a geometry column and I can't by geography.

get error ERROR: could not identify an ordering operator for type geography.

Change History (5)

comment:1 Changed 11 years ago by robe

For completeness these are the situations where this shows its ugly head

-- the obvious order by
SELECT g.gid, g.the_geog
ORDER BY g.the_geog;
-- the group by
SELECT g.gid, g.the_geog, SUM(sales)
FROM g INNER JOIN finl ON g.gid = finl.gid
GROUP BY g.gid, g.the_geog;
-- the distinct
SELECT DISTINCT g.gid, g.the_geog
-- the equality which questionably
--  well has to be allowed but prone to abuse
-- get error: ERROR:  operator does not exist: geography = geography
--  No operator matches the given name and argument type(s). 
-- You might need to add explicit type casts.
SELECT g.gid,g.the_geog
FROM g WHERE g.the_geog = ST_GeographyFromText('POINT(1 2)');

comment:2 Changed 11 years ago by pramsey

Create a test table

create table g as select  
  lon * 100 + lat as gid, 
  geography(st_setsrid(st_buffer(st_makepoint(lon, lat),1.0),4326)) as the_geog
from (
  select lon, 
  generate_series(-80,80, 5) as lat 
from (
  select generate_series(-175, 175, 5) as lon) as sq1) as sq2;
alter table g add primary key ( gid );
create index g_geomidx on g using gist ( the_geog );

comment:3 Changed 11 years ago by pramsey

Any thoughts on the meaning of the >, < and = operators? I've committed a stub implementation that works against the geocentric box centers for now (r4779)

comment:4 Changed 11 years ago by pramsey

Resolution: fixed
Status: newclosed

I've committed a simple version that uses box centers. If we ever create a hhkey generator or something like that, we can do something more elegant.

comment:5 Changed 11 years ago by pramsey

The commit went into trunk at r4814

Note: See TracTickets for help on using tickets.