Tag Archives: SpatiaLite

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 
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 build then python 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.

SpatiaLite Manger in QGIS 1.5

SpatiaLite Manager connected to example database (additional data from Natural Earth)

SpatiaLite Manager is a plugin for QGIS for managing spatial data in SpatiaLite databases. It’s based on the PostGIS Manager plugin that provides equal functionality for PostGIS.

In the database view, all tables are listed and it’s easy to distinguish between tables, views and spatial tables of different geometry types. On the right, different tabs show table meta data, table and a map preview for geometry tables.

Additionally, there is an SQL window that allows execution of statements from inside QGIS.

While there’s certainly room for additional functions, this is a great start and facilitates working with SpatiaLite databases considerably. Possible enhancements could be:

  • Functionality to create new layers from within SpatiaLite Manager (now only available through “Add new layer”).
  • SPIT-like importer for shapefiles

Looking for a complete list of functions supported by SpatiaLite 2.3.1? Here it is: SpatiaLite 2.3.1 – SQL functions reference list This is a great place to get a feeling for just how powerful SpatiaLite is.

One nice convenience function listed is MakePoint(). It’s a nice alternative to GeomFromText as described in SpatiaLite GeomFromText() for Points. The syntax is:
MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry

SpatiaLite supports a number of SQL functions that test spatial relationships both on MBRs and actual geometries: Equals, Disjoint, Touches, Within, Overlaps, Crosses, Intersects, Contains, and Relate. Of course you can also calculate distances between geometries. Furthermore, SpatiaLite supports the following spatial operators: Intersection, Difference, Union (called GUnion()), SymDifference, Buffer, and ConvexHull.

14NewLayerOriginally uploaded by

A new great feature for QGIS 1.5: You can create new layers in SpatiaLite!

Ooop, there’s not even an English Wikipedia article on SpatiaLite … Well, SpatiaLite is the spatial extension for SQLite, like PostGIS is the extension for PostgreSQL. SpatiaLite makes data exchange so much easier. Just copy the database file to some other location and use it there. The database can contain any number of spatial tables, all neatly packaged in one file. No more Shapefiles without .prj or .dbf file. *dream* :)

Also check out Tim’s other latest screenshots:

Will we really live to see the end of the Shapefile? One can always hope …

Assuming you already have a table containing x and y coordinates (WGS84) as integers:

spatialite> SELECT AddGeometryColumn('tablename','Geometry',4326,'POINT',2);
spatialite> UPDATE tablename SET Geometry = GeomFromText('POINT('||x||' '||y||')',4326);

SpatiaLite doesn’t need to be installed [1], just get SpatiaLite and unpack it in – for example – ~/apps (tested on Ubuntu 9.4):

~$ mkdir apps
~$ cd apps
~/apps$ wget
~/apps$ tar xvfz spatialite-tools-linux-x86-2.3.1.tar.gz

Now you should be able to run it. Let’s create a test database:

~/apps$ cd ~/temp
~/temp$ ~/apps/spatialite-tools-linux-x86-2.3.1/bin/spatialite testdb.sqlite

Then we have to initialize the new database with some metadata. We need a script called init_spatialite-2.3.sql (get it from It will create the tables “geometry_columns” and “spatial_ref_sys”.

spatialite> .read '~/init_spatialite-2.3.sql' ASCII

For testing purposes, we’ll now load a shapefile into our new database. I used a Teleatlas street graph (attribute encoding iso-8859-15) and created a “streets” table:

spatialite> .loadshp ~/maps/streetgraph streets iso-8859-15

Finally, we register the geometry columns … pretty similar to PostGIS:

spatialite> UPDATE streets SET Geometry = SetSrid(Geometry,4326);
spatialite> SELECT RecoverGeometryColumn('streets','Geometry',4326,'LINESTRING',2);

Done! That’s it. Now we can view our test data in QGIS. For a longer Tutorial check


%d bloggers like this: