Getting the highest (or lowest) value from a database column is a bit tricky if you cannot use GROUP BY, because it requires you to aggregate across all columns that you want in the result. Say you have a table with employees, having the columns name, salary, and department, and you want to know the highest-paid employee per department. Then GROUP BY is not an option because you would need to also aggregate by name to have the name in the output, which doesn’t make sense.
I used to tackle this kind of query with unwieldy subqueries, but I just came across PostgreSQL’s DISTINCT ON clause, which makes them a lot more compact and readable. Using that, you can simply do
SELECT DISTINCT ON (department)
name, salary, department
ORDER BY department, salary DESC;
So we’ll only get one entry per department, and ORDER BY salary DESC makes sure it is the one with the highest salary. The only bummer is that it is a PostgreSQL-specific function, so it won’t work on other DBMSs.
In this great article, Lisa Charlotte Rost gives you a crash course to the use of color in data visualisation (and mapping, for that matter). It covers some theory, lots of useful links to classics such as ColorBrewer and less-known tools such as this awesome R library that provides color schemes based on Wes Anderson movies (yes, seriously).
I came across some really neat new tools this week. Since I don’t have any time to test them out right now (or in the foreseeable future…), I’ll at least post them here so I don’t forget to check them out later:
I’m currently conducting a little online experiment about different visualizations of uncertainty. If you have a few minutes to spare, it would be great if you could participate and help me spread the word about it. Here’s the link: carsten.io/uncertainty/
This slide deck from FOSS4G 2016 originally only got my attention because of the truly awesome title, but it turns out Erik Escoffier has some tricks up his sleeve that I didn’t know. Worth a look if you’re frequently writing code that does geo stuff.
I just learned about Open Reblock from my students. Very cool project:
Reblocking is the process of physically transforming an informal settlement to provide an access path to all its structures. This project analyzes the spatial structure of informal city blocks, and uses an algorithm to suggest reblocking solutions that provide access to all structures within the block in a minimally disruptive way.
Abstract: As location-aware applications and location-based services continue to increase in popularity, data sources describing a range of dynamic processes occurring in near real-time over multiple spatial and temporal scales are becoming the norm. At the same time, existing frame- works useful for understanding these dynamic spatio-temporal data, such as time geography, are unable to scale to the high volume, velocity, and variety of these emerging data sources. In this paper, we introduce a com- putational framework that turns time geography into a scalable analysis tool that can handle large and rapidly changing datasets. The Hierar- chical Prism Tree (HPT) is a dynamic data structure for fast queries on spatio-temporal objects based on time geographic principles and theories, which takes advantage of recent advances in moving object databases and computer graphics. We demonstrate the utility of our proposed HPT us- ing two common time geography tasks (finding similar trajectories and mapping potential space-time interactions), taking advantage of open data on space-time vehicle emissions from the EnviroCar platform.
I knew that Uber drivers do not make a terrible lot of money, but this breakdown shows how bad the situation really is for them. If you factor in all costs, drivers are getting ripped off to the point where they are actually losing money when they drive for Uber. So much for the ethics of the self-proclaimed sharing economy.
I just hope the old school taxi cab companies will eventually get their act together and make it as easy to order a cab as Uber does.
Our proposal for the next (6th!) Linked Science workshop at ISWC has been accepted. Our theme this year is “Supporting collaboration and learning in e-research infrastructures” to help diminish the knowledge gap between technologists and those less tech-savvy. Here’s the gist of it, head over to linkedscience.org for the full announcement: