Spatialite and ArcGIS and QGIS

I know this is old news as I just upgraded to ArcGIS 10.2. Whaaaaa?? BUT ALL YOU EVER TALK ABOUT IS QGIS?!?!!?? It’s true – I do use ArcGIS – It helps me more annoying. I know – I didn’t think it was possibly either.

Over the last year we (NRGS) have been taking a different approach to life and business. Granted I’ve been pushing in that direction but this year it started to actually gain some traction. You really need a mix of tools in your GIS Bag of tricks – most of the mix is Free and Open Source (and you might find yourself so compelled you might just work with the other stuff one day). After all your data is the most important piece to your GIS puzzle right?

So Spatialite – that little used (or widely used) database format (technically sqlite with spatial added in) that keeps threatening to do something at some point…. Well – It did something. ArcGIS 10.2 (upgrade ran smooth by the way – probably the smoothest ever) came with this little hidden gem that gives you support for spatialite. You can’t edit – but you can read and import data into it – which is a good thing (“WELL HELL IF I CAN’T EDIT WHAT GOOD IS IT????!!1!” – Just hold on).

I’ve got a project I’ve been working on and I dumped it from a coverage File Based Geodatabase back to shapefiles. I committed myself to doing all of my editing and data prep in QGIS. So far ArcGIS has come into play twice – topology and printing. Had I not been frazzled I would have finished up topology in QGIS. I’ve not been happy about the move to shapefiles because stupid things can happen upon occasion. So in my post Christmas humbug I started playing around.

I did this in the ArcGIS 10.2 python window:

#declare the variable
sqlite_database = 'C:/data/rjhale_arcgistest.sqlite'
#create the spatialite database, “SPATIALITE”)

I did try this with “ST_GEOMETRY” and couldn’t get it to work. I most likely was doing something wrong – but – for the purposes of this exercise “SPATIALITE” worked.

I then imported a feature class. I jumped over to QGIS and began editing away. So this jump did three things of importance for you the casual reader of this blog.

  1. I just created an open format and transferred data out of my geodatabase into spatialite.
  2. I just avoided the whole shapefile issue and getting truncated field names (i.e. 10 characters for a field name)

For fun I did the exact opposite and was able to view the data (QGIS to ArcGIS). For ArcGIS editing purposes I exported it out back into a File Based Geodatabase (or SDE).


Just to really mix it up I downloaded the spatialite gui and created a database and imported a shapefile and passed it around. For the record I passed data between two different software programs and two operating systems. Given an upcoming project we will be passing data between macs, linux, and windows (7&8) and this might be the best way to go about it.

…AND I know I’m oversimplifying this a bit. I have no doubt there’s going to be some “hiccup” along the way of moving data and there’s probably something I’ve missed. QGIS still flails a bit with spatialite (IMO) and I’m pretty sure creating spatialite databases in arcgis has some problem somewhere. On Ubuntu (xubuntu) 12.04 LTS, creating a spatialite database from QGIS/Spatialite GUI is a 10 minute exercise in pain because of libraries or something. From the command line it takes 2 seconds to make one. So it’s not ever going to be super easy – but if it was everyone would be in GIS.

The challenge – Think differently. If you’re a small organization and you are a bit strapped for resources you now have (assuming you have ArcGIS 10.2 – if not there are other things that can be done) that would allow you to expand your data into an open format beyond shapefiles – example:

  • Your water utility needs to edit some data. Dump what you have into a spatiaite database and load QGIS onto their machines and let them edit. It keeps them out of your database and gives them the power to fix things. They bring the data back to you.
  • You have some field crews going out for something and you can’t afford another copy of ArcGIS – dump your data into spatialite and send them out with QGIS and let them bring the edits back to you.
  • Are you getting an intern to help you convert some old maps into digital and all they have is a mac…well – Spatialite and QGIS. At worst shapefiles but if you have existing data in SDE or some database…….

The bad – you have to write some documentation and maybe have a brief tutorial on what buttons to push. If you need to do more complex editing you always have “home base” covered with ArcGIS (I could argue you can do complicated editing in QGIS also). Let your inner control freak out for a bit and write some documentation.

Open Source software always have a problem with being mystical and magical. Documentation isn’t sexy as we’ve found with so many things this year – it’s riddled with acronyms and things you aren’t used to seeing. We’ve (NRGS) are trying to demystify that a bit. Open Source GIS can be your ally when you need it. ArcGIS is always there if you have it and it’s comfortable because it hasn’t changed greatly in 10 years.

…and Of course – shapefiles always work, but this opens your world up just a bit more if your gis is a monoculture.

Think Differently.


    • says

      I’m wondering the same thing. You have two options when you make spatialite – “ST_Geometry” and “Spatialite”. Supposedly ST_Geometry lets you edit. I couldn’t get it to work. Except ST_Geometry doesn’t let QGIS edit (I think). I hope they open it up. It could make for some interesting times.