[OGDev] categories, locales and tags

[Kake L Pugh]

On Thu 29 Mar 2007, Tom Heath <tom.heath at gmail.com> wrote:
> Using the whole-hog approach of expressing "who tagged what with
> what and when" might be a bit tricky, because if you edit an entry
> that I placed in the Wolverton locale, who did the tagging? me?
> both of us? and when? I'm not sure if this degree of provenance data
> is held in the db or not.

You can get it out of the database, e.g.:

          node name          | version |       category       |   username   
-----------------------------+---------+----------------------+--------------
 Assa, WC2H 8LH              |       2 | Korean Food          | bob
 Assa, WC2H 8LH              |       3 | Korean Food          | Kake
 Assa, WC2H 8LH              |       1 | Restaurants          | Claudia
 Assa, WC2H 8LH              |       2 | Restaurants          | bob
 Assa, WC2H 8LH              |       3 | Restaurants          | Kake

This means that Claudia added Assa and tagged it as a restaurant, then Bob
added the Korean Food tag.  It also means that Bob agrees (or, at least,
doesn't disagree) with Claudia that it's a restaurant, and that Kake agrees
with both Bob and Claudia that it's a restaurant serving Korean Food.

(You can get the dates of the edits too, I just didn't bother in this extract.)

(I talked with zool about this many years ago - in a way, an edit that doesn't
remove a category or locale is a "vote" for that category/locale being
correct.  We were wondering if we could use this in some way to "define" the
extent of a locale - as in, if 20 people agree that a point is in Bermondsey,
then it probably is.  I don't think we have enough contributors for this to
work, but theoretically it's a nice idea.)

Here's the SQL, linewrapped for readability; the "substring" stuff was just
to make the output fit in 80 characters.

london=> select substring(node.name from 1 for 27) as "node name",
                content.version,
                substring(mc.metadata_value from 1 for 20) as "category",
                mn.metadata_value as "username"
         from content
         inner join node on (content.node_id=node.id)
         inner join metadata as mn on (content.node_id=mn.node_id
                                       and content.version=mn.version
                                       and mn.metadata_type='username')
         inner join metadata as mc on (content.node_id=mc.node_id
                                       and content.version=mc.version
                                       and mc.metadata_type='category' )
         order by node.name, mc.metadata_value, content.version;


Kake