on December 27, 2022

How to visualise generic geometry column from SpatiaLite database in QGIS

Hiking & Activities

SpatiaLite Geometry in QGIS: Making it Play Nice

Spatial databases? Yeah, they can be a bit of a headache, especially when you’re trying to wrangle data from different sources. SpatiaLite is this cool, lightweight spatial database – think of it as the SQLite of the spatial world – and QGIS is the trusty open-source GIS we all know and love. But sometimes, getting those two to play nice, especially with those tricky “generic geometry columns,” can feel like herding cats.

So, what’s the deal with these “generic geometry columns” anyway? Well, in SpatiaLite, a normal geometry column is pretty straightforward: it knows exactly what kind of shapes it’s holding – points, lines, polygons, the usual suspects. But a generic geometry column? That’s the wild card. It can hold anything. Which is great for flexibility, until QGIS gets confused.

I remember one project where I was dealing with a dataset of building footprints. Some were polygons, sure, but others were just points representing single buildings. The generic geometry column seemed perfect at first, until I tried visualizing it in QGIS. Let’s just say it wasn’t pretty.

Here’s how to avoid that same frustration.

First things first, let’s get QGIS talking to your SpatiaLite database. It’s pretty simple:

  • Fire up QGIS. Obvious, right?
  • Go to “Layer,” then “Add Layer,” and pick “Add SpatiaLite Layer.”
  • Now, hit “New” to set up a connection, or just pick one you’ve already got.
  • Point it to your .sqlite or .db file – that’s your SpatiaLite database.
  • Click “Connect”. Boom.

    • Now you should see a list of tables. Find the one with your geometry column – the one causing all the trouble. Take note of the geometry column’s name; you’ll need it in a sec.

    Click “Add,” and QGIS will try to load it. If you’re lucky, it’ll work perfectly. But if you’re here, chances are it didn’t. Don’t worry, it happens! This is where we get our hands dirty.

    The problem is QGIS doesn’t always know what to do with a generic geometry column. It’s like giving a kid a box of random LEGOs and expecting them to build a specific model. You gotta give it some instructions.

    Here’s the fix:

  • Right-click on your layer in the “Layers” panel.
  • Choose “Properties.”
  • Head over to the “Source” tab.
  • Make sure the right geometry column is selected.
  • Okay, this is the important bit. Look at the “Geometry Type.” See how it probably says “Unknown” or something equally unhelpful? This is where you might need to experiment. Try setting it to “Point,” “Line,” “Polygon,” and see what works. If most of your data is one type, that’s your best bet.
  • Set the SRID. This is the Spatial Reference System ID, and it tells QGIS where on Earth your data is located. If you don’t know it, dig through the database metadata or project docs. Getting this wrong will put your data in the middle of the ocean, trust me.
  • Hit “Apply” and “OK.” Hopefully, things are looking better!

    • But what if you have a mix of geometry types? Points and polygons in the same column? That’s a whole different ballgame.

    Here’s a couple of tricks:

    • Create Views: Think of these as filtered versions of your table. You can use some SQL magic to create separate views for each geometry type. Something like this:

      sql

      You may also like

      Field Gear Repair: Your Ultimate Guide to Fixing Tears On The Go

      Outdoor Knife Sharpening: Your Ultimate Guide to a Razor-Sharp Edge

      Don’t Get Lost: How to Care for Your Compass & Test its Accuracy