Imagine you have Tableau dashboards that require the use of row-level security on data coming from PostgreSQL, but the connection to that database is established through the same technical user for everyone. How can you achieve that? Is it even possible? Short answer: it’s possible – and it’s not even that complicated, provided you’re not afraid of some PL/pgSQL scripting, PostgreSQL session IDs, and clicking on that mysterious “Initial SQL” button in Tableau Desktop. For full disclosure I’d like to mention that I found the original idea for this approach on my colleague Bryant Howell’s excellent blog, where he outlines the process for Microsoft SQL Server. That said, the approach I’m showing here is not a mere translation from SQL Server to PostgreSQL, but I also added a few more features.Continue reading →
In the last quarter of 2016 the German marketing team came up with a great way to follow the immense success of last year’s Tableau Stadium Tour: the Tableau Cinema Tour! After visiting ten cities all over Germany, Austria, and Switzerland, we are now considering rolling it out all over Europe. Stay tuned for that! Since we often got requests for the data used in the main demo, I decided to produce this write-up of how to extract the data from the Internet Movie Database (IMDb). Unfortunately copyright reasons make it impossible for us to just provide you the ready-made data. That said, with this walk-through everybody should be able to get the data!
Wow, another year has passed and so much has happened in the meantime!
During my job at the Institute for Transport Research at the German Aerospace Center (DLR) in Berlin I not only worked on the theoretical underpinnings and actual development and implementation of micro-scale traffic models but was obviously also involved in publicizing the results of said models and also other research work. I did this mostly with R, Shiny, PostgreSQL/PostGIS, QGIS and the occasional line of Python code sprinkled in-between. They’re all great. I love them with all my heart and enjoy every second I’m working with one of them. But I found it increasingly hard to visualize data easily and quickly while still being pretty. Sure R and
ggplot allow for camera-ready plots, Shiny and Leaflet make it increasingly easy to put together interactive plots and maps. But sometimes fiddling with their settings and writing the necessary code is just not practical to get to the point quickly. Also, during the fascinating stage of exploratory data analysis (kind of the first date with your new data in the data analysis process…) I felt focusing too much on the code and other technical aspects which distracted me from what I was originally doing: exploring my data to get a better understanding. Going back to the dating analogy it’s like over-thinking what to order and what small-talk topic to bring up next and thereby losing the interest of your possible future partner instead of being focused exclusively on him/her. Not a recipe for success… Continue reading →
Today I stumbled across something I wouldn’t exactly consider a bug, but at least some rather unintuitive and annoying behavior in QGIS when performing table joins.
I did something very mundane: joining a Postgres table of spatial data to another Postgres table of attribute data. The normal way to do this (for me) is as follows:
- Open the spatial table using
Layer > Add Layer > Add PostGIS Layers...
- Open the attribute table the same way (1 & 2 can be loaded in one go)
- Join the tables in the spatial table’s
For that last step I decided to join the two tables (
plr is the spatial table here, while
mss has the attributes) using the field
plr_id, which exists in both tables and only once on each side (hence a plain vanilla 1:1 join).
That works perfectly fine, except that somehow the order of the joined fields appears to get messed up:
Some research revealed that this seems to be a problem caused by identical field names in the two joined tables other than the join field itself. In my case the aforementioned
plr_id was used to join the two tables, but in addition both tables also had a field
gid, as can be seen in the following screenshot on the left:
Removing this field
gid from the attribute table
mss was no problem, since the 1:1 relation to the spatial data uses the key
plr_id anyways. As can be seen in the screenshot above on the right, the new table
mss2 is identical to
mss, only without the field
gid. And lo-and-behold – joining this attribute table to the spatial table
plr in QGIS works flawlessly now:
This problem had already been identified in QGIS 2.0 in late 2013, and has been marked as fixed in the meantime. Removing fields with identical names in the two tables is one – admittedly rather radical way – to
solve circumvent the issue. Another, more intuitive way would be to choose a meaningful table prefix in the
Add vector join dialog which can be seen in the first image above. As you can see I checked the
Custom field name prefix checkbox but left the field empty. I prefer this, since it keeps my field names nice and tidy, but in cases where homonymous fields exist in the two tables you will run into trouble – hence entering a prefix here would be a nice and easy fix for this issue.
Everything described above was performed on
QGIS 2.8.1-Wien (64bit) on a Windows 7 machine and
PostgreSQL 9.1.16 on a
64bit Ubuntu 4.6.3 server (
I have always been a great fan and avid user of databases. They’re just so versatile, efficient, easy to use, … I found this to be true for all kinds of data, small and large, high-dimensional and low-dimensional, spatial, temporal, you name it. It was only very recently that my data seemed to have outgrown my PostgreSQL database. Not so much in size, but rather in performance.