Page 1 of 1 (7 posts)
Last update:
Sun Dec 22 06:00:27 2024
•
•
•
•
Today, I’ve finished my submission for the Hubway Data Visualization Challenge. All parts of the resulting dataviz were created using open source tools. My toolbox for this work contains: QGIS, Spatialite, Inkscape, Gimp and Open Office Calc. To see the complete submission and read more about it, check the project page.
Today, I’ve been experimenting with a new way to visualize origin-destination pairs (ODs). The following image shows my first results:
The ideas was to add a notion of direction as well as uncertainty. The “flower petals” have a pointed origin and grow wider towards the middle. (Looking at the final result, they should probably go much narrower towards the end again.) The area covered by the petals is a simple approximation of where I’d expect the bike routes without performing any routing.
To get there, I reprojected the connection lines to EPSG:3857 and calculated connection length and line orientation using QGIS Field Calculator $length operator and the bearing formula given in QGIS Wiki:
(atan((xat(-1)-xat(0))/(yat(-1)-yat(0)))) * 180/3.14159 + (180 *(((yat(-1)-yat(0)) < 0) + (((xat(-1)-xat(0)) < 0 AND (yat(-1) - yat(0)) >0)*2)))
For the style, I created a new “flower petal” SVG symbol in Inkscape and styled it with varying transparency values: Rare connections are more transparent than popular ones. This style is applied to the connection start points. Using the advanced options “size scale” and “rotation”, it is possible to rotate the petals into the right direction as well as scale them using the previously calculated values for connection length and orientation.
While the above example uses pretty wide petals this one is done with a much narrower petal. I think it’s more appropriate for the data at hand:
Most of the connections are clearly heading south east, across Charles River, except for that group of connections pointing the opposite direction, to Harvard Square.
Hubway is a bike sharing system in Boston and they are currently hosting a data visualization challenge. What a great chance to play with some real-world data!
To get started, I loaded both station Shapefile and trip CSV into a new Spatialite database. The GUI is really helpful here – everything is done in a few clicks. Afterwards, I decided to look into which station combinations are most popular. The following SQL script creates my connections table:
create table connections (
start_station_id INTEGER,
end_station_id INTEGER,
count INTEGER,
Geometry GEOMETRY);
insert into connections select
start_station_id,
end_station_id,
count(*) as count,
LineFromText('LINESTRING('||X(a.Geometry)||' '||Y(a.Geometry)||','
||X(b.Geometry)||' '||Y(b.Geometry)||')') as Geometry
from trips, stations a, stations b
where start_station_id = a.ID
and end_station_id = b.ID
and a.ID != b.ID
and a.ID is not NULL
and b.ID is not NULL
group by start_station_id, end_station_id;
(Note: This is for Spatialite 2.4, so there is no MakeLine() method. Use MakeLine if you are using 3.0.)
For a first impression, I decided to map popular connections with more than one hundred entries. Wider lines mean more entries. The points show the station locations and they are color coded by starting letter. (I’m not yet sure if they mean anything. They seem to form groups.)
Some of the stations don’t seem to have any strong connections at all. Others are rather busy. The city center and the dark blue axis pointing west seem most popular.
I’m really looking forward to what everyone else will be finding in this dataset.
For everyone working with spatial databases in QGIS there comes a time when “Add PostGIS/SpatiaLite Layer” and “RT Sql Layer” start to be annoying. You always have to retype or copy-paste your SQL queries into the user interface if you need to change the tiniest thing in the layer’s definition.
This is where “Fast SQL Layer” can be a real time saver. Fast SQL Layer is a new plugin for QGIS by Pablo T. Carreira. It basically adds an SQL console for loading layers from PostGIS/SpatiaLite into QGIS. And it even comes with syntax highlighting!
Fast SQL Layer comes with one dependency: Pygments, which is used for syntax highlighting.
On Ubuntu, all you have to do is install it with apt-get:
sudo apt-get install python-pygments
For Windows with OSGeo4W, @Mike_Toews posted this on gis.stackexchange:
I downloaded and extracted Pygments-1.4.tar.gz, then in an OSGeo4W shell within the Pygments-1.4 directory, type python setup.py build then python setup.py install
When you activate the plugin in plugin manager, a dock widget will appear which contains the console and some fields for specifying the database connection that should be used. Then, you can simply write your SQL query and load the results with one click.
Fast SQL plugin
In this example, I renamed “gid” to “id”, but you can actually edit the values in the drop down boxes to adjust the column names for id and geometry:
A second layer loaded using Fast SQL plugin
It certainly needs some polishing on the user interface side but I really like it.
This is how to create a Linestring connecting two coordinate pairs in SpatiaLite:
spatialite> SELECT AddGeometryColumn ('tablename','line_geom',4326,'LINESTRING',2);
spatialite> UPDATE tablename SET line_geom = LineFromText('LINESTRING('||startx||' '||starty||','||endx||' '||endy||')', 4326);
For a quick test, let’s calculate the length:
spatialite> SELECT GLength(line_geom) FROM tablename LIMIT 5;
For data collection, it is a common situation to work with a laptop or a phone offline in the field. Upon returning to the network, the changes need to be synchronized with the master data source, e.g. a PostGIS database. If several persons are working simultaneously on the same datasets, it is difficult to merge the edits by hand, even if people don’t change the same features.
Therefore, Mathias Walker implemented an offline plugin for QGIS. This plugin automates the synchronisation by copying the content of a datasource (usually PostGIS or WFS-T) to a spatialite database and storing the offline edits to dedicated tables. After being connected to the network again, it is possible to apply the offline edits to the master dataset.
To give the plugin a try, unpack the sources, apply the patch ‘qgissvn.diff’ to a current svn version of QGIS. Then copy the offlineediting folder to $PREFIX/src/plugins and recompile QGIS.
The usage of the plugin is straightforward:
Presumably, the offline editing plugin will be part of the next QGIS version (1.6)
Here are the slides from our presentation at FOSS4G 2010 in Barcelona:
