43 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 ...
Matte's user avatar
  • 6,235
19 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....
John Truckenbrodt's user avatar
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 ...
alphabetasoup's 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 ...
Martin Hügi's user avatar
  • 3,612
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 ...
Jorge Toledo's user avatar
10 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 ...
geozelot's user avatar
  • 29.9k
10 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 ...
user30184's user avatar
  • 64.6k
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 ...
John Powell's user avatar
  • 13.6k
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 ...
Dr John A Stevenson's user avatar
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 ...
Matthias Kuhn's 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 ...
scai's user avatar
  • 6,080
8 votes

Data too huge to access: Bing Australia Building Footprints

Building :) on BERA's answer, you could use ogr2ogr to select just buildings within a 20km buffer of Melbourne using a sqlite sql query. I would still load all buildings to a geopackage first to take ...
user2856's user avatar
  • 65.3k
7 votes
Accepted

Join by attribute / spatialite with SQL / left outer join in QGIS

Use Spatialite Database! It is a lightweight file based spatial DB supported out of the box by QGIS. First set-up a spatialite DB following theses instructions Push your two tables to this ...
MarHoff's user avatar
  • 1,250
7 votes
Accepted

Creating SpatiaLite View in QGIS?

The "read_only" table obviously needs some input, as I could not find anything in the cookbook, after some search I found this insightful discussion with mr. furieri himself: https://groups.google....
AndreasK's user avatar
  • 1,235
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 ...
user30184's user avatar
  • 64.6k
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 ...
Steven Kay's user avatar
  • 20.3k
7 votes
Accepted

Cannot select / move any labels with label movement tools

QGiS will not let you manipulate data defined fields or manually move labels if you are applying a rotation to the canvas.
Laird Tam of Scootshire's user avatar
6 votes
Accepted

QGIS managing relationships

Besides creating the relations in spatialite (by adding reference constraints) you need to inform QGIS about that relations. If I recall well, that is done in the Projects properties, relations tab. ...
Alexandre Neto's user avatar
6 votes
Accepted

How to load a Spatialite layer into R?

You might consider using rgdal to read in the features from a Spatialite database based on the following example. From your terminal, check the layer names in your Spatialite database: >ogrinfo ...
dmci's user avatar
  • 4,872
6 votes
Accepted

How to create a SpatiaLite Layer in QGIS 3 with Python?

Just opened a PR to fix this issue in QGIS v3 (on Windows). Remember that this is a dev version. In the meantime, you could use this installer and adjust it by yourself (free software rocks!) after ...
Germán Carrillo's user avatar
6 votes
Accepted

Can a Geopackage file be created by Spatialite

Thanks to @user30184 and ultimately Sandro Furieri in this post: Creating a crystal-clear GPKG not containing any spatialite specific table is really simple. You simply have to avoid using ...
Andy Harfoot's user avatar
  • 3,367
6 votes
Accepted

Initialise a database with SpatiaLite 4.3

With current SpatiaLite versions the spatial metadata tables are created with SQL function InitSpatialMetadata(). The usage is documented in http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html ...
user30184's user avatar
  • 64.6k
6 votes
Accepted

Referencing a polygon layer in the field calculator of a point layer to make a virtual field

If you install the refFunctions plugin (from the menubar: Plugins > Manage and Install plugins) which adds extra functionality to the field calculator, you could use the expression: geomwithin( '...
Joseph's user avatar
  • 75.6k
6 votes
Accepted

Is there any way to differentiate and select polygons according to their shape on SpatiaLite/PostGIS?

Spatialite 5.0 has a function "Circularity" http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html. computes the Circularity Index from the given Geometry by applying the following ...
user30184's user avatar
  • 64.6k
6 votes
Accepted

Making DB Browser for SQLite run SpatiaLite queries

Ensure you're not running a sandboxed version (snap or flatpak), install it from your distribution repositories instead. Sandboxed apps can't see the real /usr/lib directory (e.g. when accessing /usr/...
user2856's user avatar
  • 65.3k
6 votes
Accepted

Difference between SQLite and SpatiaLite export from QGIS

The GDAL driver page gives rather good explanation https://gdal.org/drivers/vector/sqlite.html. SQLite database gives the platform and there are different ways to store geospatial data into SQLite ...
user30184's user avatar
  • 64.6k
6 votes

Data too huge to access: Bing Australia Building Footprints

Large geojson files can be extremly slow to open and display in QGIS. You can convert it to a geopackage using ogr2ogr which will open alot quicker. My environmental variables are broken so I specify ...
BERA's user avatar
  • 71.2k
5 votes
Accepted

Connecting to a spatialite DB, querying, writing the queried result to a spatialite table using PyQGIS

cur.execute(sql) rows = cur.fetchall() for row in rows: print row or for one record: row = cur.fetchone() print row
WKT's user avatar
  • 2,183
5 votes
Accepted

Write a layer in a specific table of a spatialite file

This is one example of missing functionality in QGIS API v2 that has been incorporated (by Even Rouault) into v3. Possible workarounds for QGIS v2: 1. I use this solution in QGIS v2.14.8 for a ...
Germán Carrillo's user avatar
5 votes
Accepted

Can true curves be copied into Spatialite from ArcGIS Desktop?

Polylines stored in a Spatialite database through ArcGIS Desktop do not appear store true curves. Any curves in the lines are densified into straight segments and vertices. To test this I created a ...
Midavalo's user avatar
  • 29.7k

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