Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals).

I have anonymized all the names and other personalized information, and I would like to keep each lat/long pair together, however randomize them within the users, so that the locations stay accurate, but their association with a User is now random.

I assume this involves an UPDATE function, but I'm not sure how to perform it on the same table.

I am very new at postgres and would appreciate any help - thanks!

share|improve this question
    
If the rows cannot be identified to any real user (you have put random names there), why don't you keep the lat/longs as they are? –  ypercube Feb 16 '12 at 21:17
    
Good point, however the user IDs stay identical so if you have access to the original dataset, you can match to the fake set and find the user's location. –  adarsh Feb 17 '12 at 14:56
    
Ok, then Erwin's answer seems to work fine. No idea how efficient will be with a big table. –  ypercube Feb 17 '12 at 14:58
add comment

1 Answer 1

up vote 2 down vote accepted

so that the locations stay accurate, but their association with a User is now random.

If I understand you right, it could work like this:

UPDATE tbl
SET lat = t.lat
  , lon = t.lon
FROM (
   SELECT t2.id, t1.lat, t1.lon
   FROM  (
      SELECT lat, lon, row_number() OVER () AS rn
      FROM   tbl t1
      ) t1
   JOIN  (
      SELECT id, row_number() OVER (ORDER BY random()) AS rn
      FROM   tbl t2
      ) t2 USING (rn)
   ) t
WHERE  tbl.id = t.id;

lat / lon have moved to another row, randomly. Every connection to it's original row is gone, but lat & lon stay together.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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