7

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?

  • What's the purpose of having two geometries in a single table? Usually "aggregation" implies some sort of union operation. Personally, I'd use a geohash or some other string to form the basis of a GROUP BY. – Vince Mar 3 '15 at 11:45
  • @Vince: why not having to geo columns in one table? There are different ways to do the same for sure. But using a hash (isn't the geo value casted to varchar a hash already?) or a some other string would suffer performance. String comparisons should be slower than comparing numbers, at least that is my experience or am i wrong there? – Michael K. Mar 4 '15 at 7:42
  • In my experience, string indexes would execute much more quickly than a spatial index. I'd expect the difference to be more than one order of magnitude (perhaps as many as three). – Vince Mar 4 '15 at 11:35
  • @Vince: OK, string better than spatial sounds logical but that would be true also for number vs. string i think ;-) – Michael K. Mar 5 '15 at 9:21
  • I don't think you'd see a 2-3 order of magnitude difference between a geohash string and a long integer cell ID. At at some point it becomes a matter of resolution -- a geohash string might out-perform a gridX/gridY pair of long integers. – Vince Mar 5 '15 at 11:26
1

GROUP BY for geometry uses bounding box. I'm not absolutely sure about geography but I suspect so too. The bounding box is generally a little larger than the object. That said, I wouldn't be using geography or geometry as my primary grouping column. You are almost guaranteed to get the wrong answer given a large enough sampling of data.

I am a bit surprised though that you get different answer with GROUP BY v.s

 WHERE location_aggregated = ST_GeogFromText('SRID=4326;POINT(11.05 49.47)') 

Since group by uses the = operator to do grouping. Can you provide some points where they say they are equal but they are not?

  • Look at the WHERE clause example above, that point is an example for that issue. But there are more points involved but it doesn't look like it has to do with special points. I think all points are involved. Maybe it'S something with precision that creates differences in the values then... – Michael K. Mar 4 '15 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.