Hello,

I am using MonetDB in a university project dealing with analysis of twitter data.

"stuff" contains some pre-selected user ids.

"tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....)

What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff".

I am using the following query:

SELECT

userid,

ROUND(latitude,0) AS lat,

ROUND(longitude,0) AS long

FROM

tweets1207

INNER JOIN

stuff USING (userid)

WHERE

latitude <> 0 AND

longitude <> 0

GROUP BY

userid, lat, long

ORDER BY

userid, lat, long;

The result set however still contains rows with duplicate values for "lat" and "long", e.g.

userid lat long

-----------------------------

19703 41 -74

If I remove the INNER JOIN part from the query, the grouping works as expected.

To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...)

Can anybody point me in the right direction? Am I doing something wrong here?

Best regards and thanks in advance,

Jonas