Since I’ve got the tools to create GIFs, I needed a way to find these clips in videos. In a movie for instance, Office Space, I remember a scene where Peter was talking to his neighbour about getting out of working the weekend. The phrase “I’m doing the drywall at the new McDonald’s” was somewhere in there. I don’t know the exact line, which that could very well be it, but how would you search for it if you didn’t.<\/p>\n\n\n\n
Semantic search is a great solution for this. It tries to get the meaning of sentences instead of a strict keyword search. This way, if you get the gist of the sentence you’re looking for in a scene, you can find it.<\/p>\n\n\n\n
So we need the text from the videos in order to search them. You could get fancy and use something like Whisper in order to generate the script from the video. But, why generate it when it exists already.<\/p>\n\n\n\n
That’s where the subtitles come in. There’s plenty of them on the internet. They may not match your video exactly because they were probably meant for a torrent, but they can get you within seconds of your own library media.<\/p>\n\n\n\n
I won’t go over every piece of code because it’s currently changing as I improve this personal project, but I’ll go over some of the necessities. The first is vectorization.<\/p>\n\n\n\n
I use sentence_transformers to handle the vectorizing. You simply use the model name you wish to use and encode a vector from a string. What’s important is to match the size of the vector with your database table definition. Here’s a quick example of using the sentence_transformers:<\/p>\n\n\n\n
from sentence_transformers import SentenceTransformer\n\nclass SubtitleParser:\n def __init__(self):\n # Load the pre-trained model\n self.model = SentenceTransformer('all-mpnet-base-v2')\n\n def vectorize_text(self, text):\n # Generate a vector for the subtitle text\n vector = self.model.encode(text)\n return vector<\/code><\/pre>\n\n\n\nThis example creates a vector with a size of 768, so we need a table that can hold it. Here’s an example of a PostgreSQL script to create such table:<\/p>\n\n\n\n
-- Table to store subtitle data with vectorization\nCREATE TABLE subtitles (\n id SERIAL PRIMARY KEY,\n episode_id INT, -- Nullable because movies won't have episodes\n media_id INT, -- Nullable because TV shows have episodes\n start_time TEXT NOT NULL,\n end_time TEXT NOT NULL,\n text TEXT NOT NULL,\n vector vector(768), -- Vectorized representation of the subtitle text\n FOREIGN KEY (episode_id) REFERENCES episodes(id) ON DELETE CASCADE,\n FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE\n);<\/code><\/pre>\n\n\n\nYou can find many resources on how to insert data into tables so I’ll skip that. Querying however is slightly different than simple select statements. Check it out:<\/p>\n\n\n\n
def search_subtitles(self, query_vector, top_n=10):\n """Perform a vector similarity search on the subtitles using pg_vector."""\n cursor = self.conn.cursor()\n\n # Convert the query vector to a proper string representation with brackets\n query_vector_str = "[" + ",".join(map(str, query_vector)) + "]"\n\n # SQL query to find the closest matches\n cursor.execute(f"""\n SELECT subtitles.text, media.title, subtitles.start_time, subtitles.end_time, media.media_type, \n 1 - (subtitles.vector <=> '{query_vector_str}'::vector) AS similarity_score\n FROM subtitles\n JOIN media ON media.id = subtitles.media_id\n ORDER BY subtitles.vector <=> '{query_vector_str}'::vector\n LIMIT %s;\n """, (top_n,))\n\n # Fetch results and create a DataFrame\n results = cursor.fetchall()\n df = pd.DataFrame(results, columns=['Subtitle', 'Media Title', 'Start Time', 'End Time', 'Media Type', 'Similarity Score'])\n\n cursor.close()\n return df\n<\/code><\/pre>\n\n\n\nThis will take your query vector (the text you want to search with, create a vector of it before comparison) with each vector in the subtitles table.<\/p>\n\n\n\n
Now we can put it all together with streamlit. Streamlit is a great framework for creating prototypes. Here’s a quick example of using it to fetch the subtitles:<\/p>\n\n\n\n
# User input to search for subtitles\nquery = st.text_input("Enter your query:")\n\nif st.button("Search") and query:\n # Query vector for searching\n model = SentenceTransformer('all-mpnet-base-v2')\n query_vector = model.encode(query)\n\n # Perform search on pg_vector with vector similarity\n query_results = db_manager.search_subtitles(query_vector)\n\n # Display search results in a DataFrame\n if not query_results.empty:\n st.write(query_results)\n else:\n st.write("No results found.")<\/code><\/pre>\n\n\n\nI will share my personal project on this shortly, but in the mean time I hope this helps some people get started with semantic search, a very powerful tool that can be used with Retrieval Augmented Generation, which I play more with in the future.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Since I’ve got the tools to create GIFs, I needed a way to find these clips in videos. In a movie for instance, Office Space, I remember a scene where Peter was talking to his neighbour about getting out of working the weekend. The phrase “I’m doing the drywall at the new McDonald’s” was somewhere […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[14,13,12,11,15],"class_list":["post-83","post","type-post","status-publish","format-standard","hentry","category-semantic-search","tag-postgresql","tag-python","tag-semantic-search","tag-streamlit","tag-vector"],"_links":{"self":[{"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/posts\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=83"}],"version-history":[{"count":2,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions"}],"predecessor-version":[{"id":85,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions\/85"}],"wp:attachment":[{"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kylehayhurst.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}