Hacker News Reader

Comment by chaps | Hacker News Reader

Extracting deeply useful information from tens/hundreds/thousands of thousands of scanned PDFs in a way that dumps its information, regardless of structure (eg, tables, text), into a relational database that's (mostly) trivially queryable and repeatable. Preferably open source. This is such a hard problem right now.

View on HN

I'm doing this for a customer right now!

Using Textract from Amazon to do the OCR. I dump the raw json in a SQLite database.

Information extraction is trickier. I extract useful things like lines and pages, locations of lines, set up various columns and now can search the SQLite db either with python code or SQL queries.

There's of course cost for running textract OCR on AWS. There are open source solutions like Tesseract

Oh yes, I'm very familiar with these. All these do though is extract information, but don't immediately make them useful. So there's a massive gulf of a middle-step that's not yet done. Textract gets close...ish to that, but it's prohibitively expensive.

Even with Amazon Textract, the middle step to curate extracted information into some form of meaning is still missing. Didn't realize this is still an unsolved problem.

Very much so. Here's an example document I work with that has information that's difficult to extract from: https://s3.documentcloud.org/documents/6929951/CRID-1061543....

Lots of missing context from these sheets that has to be interpreted (ie, how do you taxonomize each field of information?). Then asking questions on top of these documents is a step on top: "is the allegation about sexual violence?", "What is the name and rank of the person being accused?", "Is anything anomalous in the review process?", "Has this person's rank changed in the past 5 years?" etc etc.

Now expand this problem to hundreds of thousands of different types of document.

If you haven't yet, you should look at the full text search capabilities of SQLite and postgres. Could simplify your search part of the workflow a bit

When you say "regardless of structure" -- if it's a relational database, that inherently implies a set structure. Or did you mean the information is consistent enough to be represented in one relational structure, but is presented in the PDFs in different formats?

Honestly, I don't know what it would look like, but being able to query would be deeply important. But what I can say is that a lot of the PDFs I work with are auto-generated as PDF forms using queries, after the information was likely inserted into a relational database during some esoteric transcription step.

Meaning, the information that formed the PDFs very likely come from a relational database and an inversion back to its original relational form is probably the convenient form. Whether that means it turns to 40 tables, that's fine, so long as it's relational and a query can be written.

If querying is the main goal, ingesting the pdf directly into something like elasticsearch or splunk would be much simpler. This of course doesn't meet the checkbox of being open source though.

This is a very... engineer... approach. Not something that I would want to ever do with the intent of exploratory analysis and eventual sharing.

> Extracting deeply useful information

This right there is the difficult part - what do you mean exactly? I cannot come up with anything better than search, as in like Google search. And they did it for books already, it's seriously good.

A big problem that happens with FOIA requests is that you're often sent data in the form of a spreadsheet that was converted to a PDF. And then scanned. For thousands of pages. Solve that generally so that you can insert all of that data into a postgres database, with sensible indexes.

Check this project from AllenAI called PdfFigures: they also extract tables

https://github.com/allenai/pdffigures2

That sounds doable, but... why opensource? Does this mean that people / companies are not prepared to pay for the product or service?

I have around 2 million pages from FOIA requests that need information systematically extracted and I'm not alone in this problem. The costs for the systematization of many pages will be prohibitive.

The public good of having a resource like this available to the public for free is beyond unimaginable as far as I'm concerned.

How do you currently extract info from the FOIA request pages? What kind of info do you look for? Just thinking how you could standardise this

Mostly tesseract, or uploading it through documentcloud and running manual searches. I do a combination of data analysis and spend many hours reading through documents. Sometimes I use unix tools like grep/awk/etc, sometimes I use SQL. If the PDF isn't scanned, I use tabula for csv extraction, but if it's scanned it becomes a silly ordeal.

Mind you, I'm not exactly looking for advice here. It's a supremely difficult problem and gut-ideas more often than not don't pan out.

Reply on news.ycombinator.com