QGIS and Spatialite Part 1: Scene of the Crime

Dec 7, 2015 | ArcGIS Desktop, Chattanooga, GIS, QGIS

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:

crime_csv

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

crime_shape

….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…”:

spatialite

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.

add

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)

arcgis

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.

You may also like

Advertising and the Great Tweet

Business. It's complicated. One of the more complicated things I've done has been advertising. How do you advertise a small GIS Consulting Shop? Ads on "The Internet"? Magazine? Direct Mailings? Word of mouth? The answer is "word of mouth". That and Conferences. That...

FOSS4G Nashville Updates

wiki: https://wiki.osgeo.org/wiki/20221201-Nashville-foss4g OK - Updates. When this conference first started a few years back it was a wild hope that I could start a community building event here in the Southeastern US. Every year I was always amazed that people...

FOSS4G Nashville Nov 30-Dec 2nd 2022

FOSS4G Nashville Nov 30-Dec 2nd 2022

It's happening. The big plan for the FOSS4G style meetings I've held around here has been "grow this beyond me". At the TN GIS conference this year I was approached by Two people from Vanderbilt and this now gets a little bit bigger and better. So we're headed to...