CREATE TABLE search ( id bigserial NOT NULL, location geography(Point,4326) NOT NULL, location_aggregated geography(Point,4326) NOT NULL, created timestamp with time zone NOT NULL, CONSTRAINT search_pkey PRIMARY KEY (id) )
The table was filled with 1699012 rows containing random geography data around some points in a regional area for the month february. If that is important: i wrote a small java program to fill in data using jdbc and the postgis jdbc lib for the insert with org.postgis.PGgeometry class for the location. location_aggregated is the result of ST_SnapToGrid using the value of location and 0.005.
I'm using this statement:
SELECT (location_aggregated), count(*) FROM search where created between '2015-02-21 00:00:00' and '2015-02-28 23:59:59' group by (location_aggregated) having count(*) > 1 order by 2 desc;
The result is then 104 rows with all having count of 2. But there are much more results for the same geography point.
If i'm testing that with e.g.
ST_GeogFromText('SRID=4326;POINT(11.05 49.47)') i get 70 hits in the specified created range.
If i use
ST_AsText(location_aggregated) in the
GROUP BY instead, or just if i add
location_aggregated = ST_GeogFromText('SRID=4326;POINT(11.05 49.47)') to the
WHERE clause the result is very different. Using where it returns 1 row with the correct count of 70, using the function in
GROUP BY i get 39010 rows including the one with the count of 70 mentioned before.
This looks like the
GROUP BY on a geography (or just this special definition) doesn't read all rows to aggregate or the value just isn't read with the same precision for both ways of access. Checking with
ST_AsText and also converting to a varchar/string does return the same values for the 70 rows of the example point.
Is it a bug or do i have to use a function on the geography column for grouping?