• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
North River Geographic Systems Inc

North River Geographic Systems Inc

Geospatial Problem Solving

  • Home
  • About NRGS
  • Training
  • Blog
  • Resources
    • QGIS Tutorials and Hacks
    • GDAL Tutorials and Hacks
    • PostGIS Tutorials and Hacks
    • GRASS Tutorials and Hacks
    • OSM Tutorials and Hacks
  • Services
    • Support and Services
    • Tennessee NG911 Address Server
    • Forestry Database Services
    • Conservation GIS
  • Portfolio
  • Show Search
Hide Search

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:

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.

Primary Sidebar

Calendar

July 2022
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031
« Jun    

What we talk about

addresses ArcGIS ASPRS Athens Atlanta Beer Cartography Chattanooga Community Conference Data Do Some Good Education ESRI Forestry FOSS4G foss4gna Fulcrum gaurisa ga urisa Gdal Georgia GeoServer GIS GISP Google GRASS Lidar MAGS open source OpenStreetMap osgeo OSM postgis Python QGIS raleigh Rambling Small Business Tennessee TNGIC Topology Training URISA usvi

North River Geographic Systems Inc

  • Home
  • About NRGS
  • Training
  • Blog
  • Resources
  • Services
  • Portfolio