39 votes
Accepted

Using GeoPackage instead of SpatiaLite, and vice-versa?

A small comparison between GeoPackage and SpatiaLite in QGIS. Right to the moment it is not really productive to use GeoPackage with QGIS. May change pretty fast though (see addtions!). Some ...
user avatar
  • 6,045
18 votes

How to use SpatiaLite functions in a Python script?

You could directly integrate your line into the database via a geometry column if you load spatialite as an extension of sqlite: import sqlite3 connR = sqlite3.connect(':memory:') connR....
user avatar
17 votes

How to add a geometry to an existing feature that was created with NULL geometry?

If you're after a GUI solution, you can use the "Add Part" tool (QGIS >=2.2 only). First, make your layer editable. Then in an Attribute Table select a single row with a null geometry that you want to ...
user avatar
  • 26.3k
17 votes
Accepted

Spatialite spatial queries do not function on GeoPackage

The issue is with the way I am using Spatialite. After talking with the company behind the web service that provides these datasets (since I initially assumed it may have been an export bug), they ...
user avatar
14 votes

How to convert a complex project of shapefiles to a SpatiaLite database?

It is definitely possible to pull all the layers into one database, although in a multiuser environment, this will be best if most users will only be reading (not editing) the data. The easiest way to ...
user avatar
13 votes
Accepted

Saving multiple layers from QGIS to SpatiaLite database?

You can create an empty SpatiaLite database using Layer > Create Layer > New SpatiaLite Layer. Once you've done so, it will appear in the DB Manager (an included plugin; enable from the plugin ...
user avatar
  • 909
12 votes

How to export shapefile to SpatiaLite

Yes it can be done with QGis. Using the QSpatiaLite Plugin "Manage your SpatiaLite databases within QGis" After installing the plugin you can run it. You will receive a series of messages/...
user avatar
  • 121
12 votes
Accepted

Moving SpatiaLite Table into PostgreSQL using ogr2ogr?

And, after a bit more reading the ogr2ogr help I found the answer more easily than I expected. ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb -sql "SELECT * FROM table" -dialect spatialite -nln ...
user avatar
12 votes
Accepted

Setting SRID in Spatialite table?

Every spatial table in a SpatiaLite database has a foreign key constraint so that the SRID has to match the value stored in a metadata table named geometry_columns. You can see the constraint with: ...
user avatar
11 votes
Accepted

Documenting inability to Start Editing SQLite geodatabase layers in ArcMap?

It's not strictly read-only access. You can load data via copy/paste and import into a SQLite or SpatiaLite database. Tables and feature classes can be appended to using append or Load Data. You can ...
user avatar
11 votes

Reading/writing local datastore for QGIS?

How about a local PostGIS instance? PostgeSQL can be downloaded from https://www.postgresql.org/download/ to which a PostGIS extension can be created to make it a spatial database. Also recommended ...
user avatar
  • 3,432
11 votes
Accepted

Does GeoPackage support simultaneous editing?

I have no technical background on how Geopackage works internally, so after searching for information on the simultaneous editing capabilities of Geopackage and barely finding anything conclusive, I ...
user avatar
10 votes
Accepted

How to define which columns in a Spatialite database are the latitude and longitude?

Add first a geometry column into your table with SQL SELECT AddGeometryColumn('your_table','geometry',4326,'POINT',2); Then populate the new geometry column by constructing point geometries from ...
user avatar
  • 52.5k
10 votes
Accepted

How to rename ogr2ogr tables on export to Spatialite?

From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.
user avatar
  • 39.9k
10 votes
Accepted

Dump a GeoJSON FeatureCollection from Spatialite query with Python

Okay, got it meanwhile. There are probably different ways to do this but this one works fine. Apart from access to your Spatialite database via Python's sqlite3 module and the Spatialite extension, ...
user avatar
  • 1,719
9 votes

The first "n" points near a polygon? (spatialite or postgis query)

One nice way of doing this is using the new LATERAL JOIN syntax in Postgres in conjunction with the <-> operator. A lateral join essentially runs the right hand query for each row in the left hand ...
user avatar
  • 13.2k
9 votes
Accepted

Creating SpatiaLite view (for use in QGIS) from non-spatial SQLite database?

The following commands can be used to add a Spatialite view to non-spatial tables in a SQLite database. Add Spatialite capabilities to the database. SELECT load_extension('mod_spatialite'); SELECT ...
user avatar
9 votes
Accepted

Is it possible to run a PostGIS query on a SpatiaLite database?

You can use SpatiaLite tables as if they were PostGIS tables but without importing them physically by using the OGR Foreign Data Wrapper https://github.com/pramsey/pgsql-ogr-fdw. Once the virtual FDW ...
user avatar
  • 52.5k
8 votes
Accepted

Is there a good way to use Spatialite in QGIS?

I increasingly use Spatialite with QGIS effectively as a good, portable store for projects - and would say that the quick answer is 'yes, it's usable'. Store and access spatial and non-spatial ...
user avatar
  • 14.4k
8 votes
Accepted

Connecting QGIS, SpatiaLite and Python?

I have a doubt about your expectations If it's doing SQL query on a SQlite/Spatialite DB within QGIS Select the layer that use SQlite as a source, then do # Import QtSql function(s) from PyQt4....
user avatar
  • 28.7k
8 votes
Accepted

Pyspatialite Installation Error

Unfortunately fedora does not ship a pyspatialite package in the repositories. You can install it using pip but since pyspatialite is more than just some python files and contains compiled parts you ...
user avatar
8 votes

Getting banks layer from OpenStreetMap with QGIS?

Banks are tagged as amenity=bank so you need to look for the amenity key. This tag can appear on nodes and ways. This means that you will not only get buildings but also individual POIs. Keep in mind ...
user avatar
  • 5,684
8 votes
Accepted

CREATE TABLE AS with geometry in SpatiaLite

SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic ...
user avatar
  • 23.9k
7 votes

Saving multiple layers from QGIS to SpatiaLite database?

give the qspatialite plugin a try (https://plugins.qgis.org/plugins/QspatiaLite/) (Create empty db, now you can select all your layers and add at one go. see screenshot 1)
user avatar
  • 6,667
7 votes
Accepted

Insert Geopandas GeoDataFrame into Spatialite database

Okay, meanwhile I found out how this can be achieved. I don't know if this is the most efficient way but for my tables which are not huge it works fast enough. The idea is that geopandas stores ...
user avatar
  • 1,719
7 votes
Accepted

Merging overlapping polygons with SpatiaLite

Here is a step-wise process. Queries were made with Spatialite-gui and visualizations with OpenJUMP. Take some points into table "points" with an attribute "radius". Buffer points by taking the ...
user avatar
  • 52.5k
7 votes
Accepted

Keeping QGIS from writing all CRS to Spatialite database?

As @ianturton points out, a geopackage only contains the CRS info needed. You can confirm this using sqlite (Geopackages are sqlite databases) sqlite3 my.gpkg $sqlite > select count(*) from ...
user avatar
  • 19.8k
6 votes
Accepted

How to build a geographic database of GPS logs?

ptrv/gpx2spatialite does this remarkably well, saving timestamps for all points and deriving speed and length data for tracks. It also won't import duplicate tracks, so you can feed it a huge pile for ...
user avatar
  • 1,891
6 votes

Is it possible to embed QGIS layer styles in a Spatialite database?

now it'a available in Master and will be officially available in Qgis 2.4 http://pvanb.wordpress.com/2014/05/15/saving-layer-styles-to-your-spatialite-database/
user avatar
6 votes
Accepted

Does QGIS automatically use spatialite spatial indexes?

Yes. It does as it appears from looking at the source code for the Spatialite Data Provider. The QgsSpatiaLiteFeatureIterator class is the one that supplies the features to the map upon sending a ...
user avatar
  • 6,994

Only top scored, non community-wiki answers of a minimum length are eligible