5

I am looking for a super simple and very pragmatic approach to k-means clustering of questions using PostgreSQL database alone.

While I am fully aware that this method may not yield meaningful results if my assumptions are off, it may be a good and first attempt at categorizing my data.

Imagine a small online forum, where users are free to ask brief questions about ANY topic they want but without the need to categorize them, and other users need to be notified about a new question if it matches the topics of interest to which they have previously subscribed.

My plan is to first break down each incoming question into lexemes using to_tsvector but in all honesty I am a bit lost about what to do afterwards.

Even assuming I have correctly identified the k categories to which the question may be matched, how would I go about deciding whether a questions should fall into one (or more) category?

Improve this question

2 Answers 2

Reset to default
2

you could use the ranking returned from the text search as a cut-off to decide if the question matches a topic. For each category you could hold a document with relevant terms and search the query text against these terms.

Btw, there's a nice extension called madlib (it's more of a utility package) - it contains many useful features/algorithms including topic analysis & clustering. Take a look at madlib documentation

Improve this answer
2
  • Thanks, I looked into madlib before. The part where you suggest to hold a document with relevant terms is exactly what I am struggling with. I believe it should not be a static document but rather should be updated as new questions (with new lexemes) come in. I've upvoted the answer but still keeping it open.
    – Andy
    Commented Nov 20, 2016 at 4:25
  • Hi, you can use the ts_stat command to scan your questions table which have been assigned to a specific tag and look for common lexeme.
    – cohenjo
    Commented Nov 20, 2016 at 8:36
0

Basket-Of-Words approach:

  1. Take the unique words and their number in the dataset and look through the top X words. Search for the most important words that might mark a cluster.
  2. Put the chosen words into columns with their number per question as values in each row.
  3. Cluster on all word columns that you chose. In order to cluster, have a look at the Sisense Tech Talk Multi-dimensional Clustering Using K-Means in Postgres SQL.

It is using PostgreSQL database alone, and though the code is not short, it is simple and pragmatic in that there is an example that you simply and pragmatically can adapt to your needs. The example leads to such a cluster result, I guess that is what you are looking for:

enter image description here

  1. Use the assigned cluster_id to check the working of the chosen words. Improve iteratively. That would be the pragmatic manual approach.

Alternatively, at a given set of important words and their numbers, you might also do TF-IDF in SQL by step-wise calculating:

  • Term frequency
  • Inverse document frequency
  • Term frequency–Inverse document frequency
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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