[GIS] How to a HTML table be stored within a database field as a BLOB, text or other value

qgisspatialitesqlite

My Spatialite database has several thousand spatial polygon records In QGIS, each with a couple dozen field values. I also have many thousands more of additional tabular data relevant to those polygons, all stored within Spatialite, that the polygons need, a one to many relationship (a table as a value)

I'd like to create a QGIS ACTION of a single Field value that pops up a whole table stored within a single value in the spatial record. My initial strategy is to use a BLOB or Text to store HTML coded tables I create, so that the QGIS ACTION would open it up in a web browser. Opening up an equivalent table in Excel or some other common application would be satisfactory.

My worries are that I can't store html as a BLOB since its not binary. (but maybe that's not relevant). Also, I figure I can't store the html table as text since it would contain more characters of data than spatialite or shapefile can handle.

I can create an html coded page for each field value without a problem, but…

  1. How would I store the html page as a single field value in Spatialite or Shapefile?

  2. Or, what would be a good alternative to doing that, such as using another format besides html or BLOB data.

  3. Would it be possible to have a SQL query stored as a field value, and assign an Action that pops open the Results?

I'm understanding I could use PostGIS to create a database relationship if all else fails. It may be a day before I get back with any feedback. Thanks in advance for everyone's suggestions.

Best Answer

First part, you can store your HTML in a text field. SQLite doesn't have rigid field types like other database engines, so you can just define your field as text and move on. If you do decide to go with PostgreSQL, you can also define your field as text and store as much HTML in there as you need.

To display your HTML you can try using a map tip. Check out this blogpost on the feature. They define the HTML statically, but you might be able to choose your HTML field and have it work off that. Then again, it might not work, I've never tried it.

http://nathanw.net/2012/08/05/html-map-tips-in-qgis/

Related Question