table definition:
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?
Aucun commentaire:
Enregistrer un commentaire