5

How can I create PDFs out of QGIS using the Print Composer Templates that include a lot of text (from a text-template) and within the text several information comes out of my spatiallite-database. A map is also part of it, but I am only having problems with the spatialite-text-inputs.

Workflow:

  • User Selects a Polygon (This is the only layer that is loaded)
  • Users presses a „Print this for me“ Plugin Button (Using the Polygon-Name I get out all the information I need from the tables in my Spatialite DB)
  • User gets a wonderful PDF

The table that includes the polygon is different from the tables (yes, there are several) that have all the other information, this is due to data-entry reasons.

I already have a solution using QWebView(), but my solution is not very good for composing the map itself and if a user wants to change my templates, it is a bit complicated. Here my solution:

import sys
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtWebKit import *
import sqlite3

# create web view
webview = QWebView()

connection = sqlite3.connect('D:\allthedata.sqlite')
cursor = connection.cursor()
cursor.execute('SELECT name FROM information WHERE polygon_nr = 2015')
for row in cursor.fetchall():
    name = row[0]
# I use 15 inputs, but to simplify this, I am only using „name“ here

# get template and push into webview
template_txt_temp = open("c:/template1.txt") #I have several templates
template_txt = template_txt_temp.read()
html = template_txt.replace('$name$', name)

# the template ist just some html, but can have several pages;  html →
# <p>Hello, this is just a dummy text, I want to show you, where I
# want so insert the $name$ from the database</p>

print html
print type(html)

webview.setHtml(html)

printer = QPrinter()
printer.setPageSize(QPrinter.A4)
printer.setOutputFormat(QPrinter.PdfFormat)
printer.setOutputFileName(r'C:\report.pdf')
webview.print_(printer)

And out of it I get a nice PDF. So the essential question: How do I get the "name" information out of my database and into the Print Composer? (And is my Question even understandable?) Maybe this picture helps:

enter image description here

1

I would suggest you perform a join of the relevant non spatial tables with your spatial layer. Do this by opening the properties for your spatial table. You then open up the Join tab and add the relevant joins.

enter image description here

If your tables do not share a common key you could create a supporting table which you then use for each of your map plates. This table is then linked to relevant objects in the other tables. You could name it tbl_mapcoverage and your attributes could be something like this:

  • id - uniquely identifies your plate)
  • geom - a polygon which sets the focus area for your map. It does not have to be visible.
  • ref_breadandbutterprices - reference to table with non-spatial information
  • ref_conveniencestore - reference to table with geom containing referencing one or more shops.

I am assuming tha tyou are aware of how the QGIS atlas functionality]2 works and that you are using it to complete this task.

If you want to include a one to many relation you could pull this data in by using a python script and generate a html-table with your data using a function.

  • This is not an option, the data is not necessarily related with my geometries. – AndreasK Mar 8 '17 at 21:00
  • As long as the common denominator is your map plate you could make a "supporting" table which binds your other data together with this one. I will update my answer with this option. – ragnvald Mar 8 '17 at 22:03
  • "not necessarily related" means "there is not necessarily a common denominator", therefore I asked how I can get data from a database into the print composer (and the question has nothing to do with atlas generation). table joins do not do the job, when there is nothing to join. – AndreasK Mar 24 '17 at 8:11
  • Guess you will have to resort to preprocessing your data using python, and then pick the data up in the print composer printing the variable or file previously written to. – ragnvald Mar 25 '17 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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