QGIS and Spatialite Part 1: Scene of the Crime

I had mentioned in the last post about the switch to spatialite for the Intro to QGIS class. I had a lot of driving to do a few nights ago so I started sketching more of it out. The second thought was I’m going to use the same data I’m using for the QGIS Class. Which actually leaves me with a pile of data I’ve been playing with as of late….which coincidentally was my first though: I’VE GOT SOME DATA ALREADY DOWNLOADED THAT I’M NOT GOING TO USE IN THE CLASS.

So – I’m going to do a series of blog posts on Spatialite, QGIS, things in general while I muddle through this idea of a class/tutorial.

So just to start off the playing field as level as we can:

• Shapefiles:  You are at least going to have three files that will be .shp .shx and .dbf. Since this format floats back to the early 90’s (as far as I could tell – quick wikipedia search lead me down that road) it’s using dbf to store data. So you are limited to 10 characters for fields and you also have a bout a 2 GB limit. You find them everywhere and almost everything reads them.  If you were to say “I need one file format everything can read/write” – well – you have it.
• Spatialite: SQLite is a file database. Spatialite extends the core of SQLite to store geometry. You have one file. Usually that one file has a .sqlite or sometimes a .db extension. It’s new(ish) with a 2008 appearance. It’s been one of those things that has long been said “this will kill off the shapefile” and it hasn’t. Mostly because ESRI will read them and editing them is a whole different story. The big thing you need to be aware of is the latest new kid on the block is called Geopackage and it is built around spatialite. With Geopackage everything should be able to read and write to one common format. There isn’t much of a file limit with spatialite – so you can go way over the 2GB mark and not worry about it.

Chattanooga has an open data site. In the open data site we’ve got some released crime data that shows that people really need to be nicer to each other. I grabbed it. Exported it out in CSV Format and from there took it to QGIS.

I opened it in QGIS (using the add delimited text layer tool) to take a look at it and 155240 features later:

Nice long column names and it has a lot of information. I’m going to dump it to shapefile:

….and column truncation but the file size dropped to about 4.5Mb and display time is good. It takes about 2 to three seconds to refresh. I really want my data to remain intact though – I don’t like that my CodeDescription column renamed itself to CodeDescri.

So with QGIS I’m going to save my CSV out as Spatialite. In order to convert/project data in QGIS you right click on it and go to “Save as…”:

So the conversion itself isn’t blazing fast. I didn’t time it but it was close to 40 seconds and that is just long enough to worry you. The spatialite database checks in at 53Mb. A little more than double the original csv but not horrible. I’ve kept column names intact.

OK Randy – you’ve taken a CSV and thrown it into QGIS and then thrown it into something and I can’t read it in ArcGIS.

The only Screen Shot of data you will get will be from ArcMap (Arcview 10.3.1). Note – you can’t edit it but you can read it (a lot better than my last check with 10.2.2)

So what’s next? What’s so great about pushing your data out of shapefiles into Spatialite? WHY?

Those answers and more are coming up in Part II.