Questions tagged [spatialite]
SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.
709 questions
0
votes
0
answers
32
views
QGIS- Do Triggers Bypass Default Values in SpatiaLite?
I'm working with SpatiaLite and using triggers to update or insert data into other tables.
Is it expected behavior, or could it be a bug, that when I insert a new feature using a trigger, such as:
...
- 815
1
vote
1
answer
75
views
Better control cascading triggers in SQLite [closed]
I'm working with two interdependent SpatiaLite layers.
When I delete entities from the first layer (ESPECES_PT), I also delete corresponding entries from the second layer (ESPECES_ATTRIBUTS). However, ...
- 815
0
votes
0
answers
40
views
Where does output from QGIS Processing "SpatiaLite execute SQL (registered DB)" go?
I can run SQLite queries on a SQLite database in the Processing module named "SpatiaLite execute SQL (registered DB)".
This SQL runs with no error messages:
CREATE table Tur_og_Deltur_2016 ...
- 313
0
votes
1
answer
52
views
QGIS - Issue with Geometry Disappearing After Merging Attributes in QGIS with SpatiaLite Trigger
I’m working with SpatiaLite, and I have a layer that contains a field named pk, which requires unique values.
However, when using QGIS, there is a tool that allows users to merge attributes from ...
- 815
0
votes
0
answers
42
views
SpatiaLite views in QGIS are not being mapped correctly
I've created a view in SpatiaLite, the unique id is made with
cast(row_number() over() as integer) as id
the geometry is made with
CastToMultiPolygon(st_union(geom)) as geom (it's a merging operation)
...
- 1
0
votes
0
answers
11
views
Numerous errors migrating SpataLite database with GeoDjango
On a Windows machine, I am trying to run GeoDjango's makemigrations, but receiving numerous errors from a models.py file created from GeoDjango's inspectdb. The error block looks like:
...
- 713
0
votes
1
answer
35
views
GeoDjango fails to load SpatiaLite, but not Jupyter?
I am trying to use SpatiaLite for a GeoDjango application on a Windows 10 machine using Anaconda, but, when I run python manage.py dbshell, I get an error "CommandError: You appear not to have ...
- 713
0
votes
0
answers
26
views
.mode column results in strange output with spatialite
I'm currently experimenting with SpatiaLite because I plan to eventually use it to programmatically generate a geodatabase from an API I have subscribed to. I already have sqlite3 available as a ...
- 3,574
0
votes
1
answer
59
views
QGIS - SpatiaLite - SQLite string manipulations (position/replace)
I have two layers :
Layer 1: ESPECES_HABITATS_ATTRIBUTS, with fields 'espece' (containing
unique values) and 'distance' (an integer).
Layer 2: ESPECES_HABITATS_POLY, with fields 'ESPECE' (a ...
- 815
4
votes
0
answers
44
views
Closing connection to SpatiaLite DB in QGIS
I have a SpatiaLite DB that has been used to generate some layers in QGIS (via a PyQGIS script, using the native:extractbyextent processing tool).
Once these layers have been generated, they are saved ...
- 5,479
3
votes
1
answer
113
views
SpatiaLite Equals function vs the standard ‘=‘ operator
When using SQLite with SpatiaLite, what is the difference between ‘=‘ and Equals?
As far as I understand from the documentation:
Equals(geometry1,geometry2)
may return TRUE when
geometry1 = geometry2 ...
- 41
1
vote
2
answers
107
views
Synchronizing polygon changes between layers using QGIS
I'm working with two distinct SpatiaLite layers, each with specific fields (they are not duplicates of each other).
While working on HABITATS_POLY, I aim to update ZONES_HUMIDES_POLY based on field ...
- 815
0
votes
0
answers
49
views
Finding closest road segment to GPS points in SpatiaLite
I am using the following query in SpatiaLite to find the closest road segment to a given GPS location:
SELECT id, Distance(segment, GeomFromText('POINT(-79.406166 43.666075)'),1) AS Distance
FROM rs
...
2
votes
0
answers
58
views
Calculating risk factors for waste management system using SQL database
I am working on a waste management system where I need to prioritize actions based on risk factors associated with different types of waste, including industrial and sewage waste. The system utilizes ...
- 21
0
votes
1
answer
39
views
MbrIntersects returns all features in layer
I want to add features to a gpkg using python, but before I add them, I want to test if they intersect with the gpkg_layer.
For performance, I want to first filter the gpkg_layer using the bounding ...
- 659
1
vote
1
answer
112
views
Error when adding new feature
I wrote a python script for QGIS to create an empty SpatiaLite layer, which the user later can fill with features:
import sqlite3
DBdir = r"Test.sqlite"
SPATIALITE = r"path/to/dll"...
- 55
-1
votes
3
answers
505
views
What is the status of SpatiaLite in 2024?
I'm trying to figure out the feasibility of using SpatiaLite in a project.
The official website seems to be pretty old and without obvious instructions for how to install it - although the latest ...
- 5,710
0
votes
0
answers
73
views
Adding columns to existing SpatiaLite layer in QGIS
I have created a SpatiaLite point layer in QGIS with each point having attribute NAME. I have since realised it would be useful to have a text field for NAME of notes/description. I have looked at ...
- 451
0
votes
0
answers
44
views
QGIS - Unable to save after mergin attribute - (SpatiaLite - Triggers)
I'm working with SpatiaLite layers, and I'm currently encountering a problem when I use the tool: « Merge attributes of selected entities ».
I use several triggers that are not the cause of the ...
- 815
2
votes
2
answers
105
views
QGIS - Trigger's execution too slow (QGIS freeze)
I am currently working on a SpatiaLite layer that is updated after insertions and modifications through triggers.
When I copy-paste features on the "HABITATS_POLY" layer, it triggers updates ...
- 815
0
votes
1
answer
61
views
QGIS - SpatiaLite triggers doesn't update properly each field
See edit at the end of the post
I am working in two layers:
HABITATS_POLY (important fields: "EUNIS_1" , "EUNIS_2" , "EUNIS_3" , "EUNIS_4" ; "EUNIS22_1&...
- 815
1
vote
1
answer
63
views
QGIS - How to update several rows with triggers SQLite
I currently have two SpatiaLite layers:
HABITATS_POLY: Key fields include EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4, EUNIS22_1, EUNIS22_2, EUNIS22_3, EUNIS22_4.
GESTION_CODES: Key fields include EUNIS and ...
- 815
0
votes
1
answer
53
views
Spatial Join with no Related Primary Key
The example I am showing is a VERY simplified version of my dataset which actually represents 100's of points. I have point A representing a building (centroid of the building polygon), but no other ...
- 363
2
votes
1
answer
73
views
Getting the result automatically when only one match with Value relation in QGIS
I have a project that comprises three crucial layers:
A SpatiaLite layer named 'HABITATS_POLY'
A layer without geometry named 'CORRESPONDANCES'
A database named "BD_CORINE"
When a code is ...
- 815
3
votes
2
answers
220
views
QGIS load dynamic dropdown list from the content of another field, using separator
I would like to dynamically populate a dropdown list based on the content of another field. While one approach involves using relations and querying a database, it can be time-consuming. Currently, my ...
- 815
0
votes
0
answers
73
views
QGIS how to improve triggers on SQL - SpatiaLite
I'm looking for a way to improve the way my triggers are working on a SpatiaLite layer.
Currently, it takes 45 seconds to work on 1000 entities. Is there a way to improve the performance?
My triggers ...
- 815
1
vote
0
answers
62
views
Refresh attribute table after SQL trigger
I'm utilizing a trigger on a SpatialLite layer in QGIS.
This trigger is designed to update a field when its content matches with another in the database.
Here's the trigger's code:
CREATE TRIGGER ...
- 815
1
vote
1
answer
66
views
QGIS - SpatialLite - Trigger for code correspondences - Manage multiple results
I have a database containing correspondences between codes (e.g., A = 1 = 11 = H = M, etc.). I've made the first 100 rows of this data available for download.
BDD Extract
I would like a feature where, ...
- 815
1
vote
1
answer
243
views
Rebuild spatial index after massive changes
I have a question about spatial indexes.
I'm using SQLite with VirtualSpatialIndex.
I have a main table that contains millions of polygons.
Let's say every 4 months I have to get a completely new ...
- 41
4
votes
0
answers
77
views
In QGIS, can the 'spatialite execute sql' algorithm accept '.sql' text files?
I'm working in Model Designer on a process which includes running an SQL on a SpatiaLite database. I have the connection as the input, but I want the SQL to come from a text file 'xxxx.sql'. ...
- 61
0
votes
0
answers
143
views
SpatiaLite / SQLite -- error: tracks.geom violates geometry constraint [geom-type or srid not allowed]
I am working with an Elixir struct that looks like
%Geo.MultiLineStringZ{
coordinates: [
[
{18.68056, 54.695338, 15.6},
{18.68061, 54.695331, 15.6},
{18.68069, 54.695339, 15.6}
...
2
votes
1
answer
279
views
Is there a SpatiaLite equivalent to PostGIS' ST_DumpPoints? [duplicate]
I've been using PostGIS' ST_DumpPoints to 'explode' multipolygon layer into points with its corresponding polygon part, ring and index information. As QGIS uses a SpatiaLite engine against its Virtual ...
- 1,182
3
votes
1
answer
335
views
Inserting XY POINT (EPSG 4326) fails in sqlite3 shell
I'm working for the first time with spatial databases, using SpatiaLite. I'm working with Kotlin (Xerial's driver for JDBC) and it seems to work just fine. But I'm wondering why I am getting errors ...
- 33
4
votes
2
answers
1k
views
Data too huge to access: Bing Australia Building Footprints
I am relatively inexperienced with QGIS. I seeking to access building footprints in a 20km radius from the centre of Melbourne city, Australia.
The dataset I am hoping to use is Bing's 2020 Australia ...
- 51
0
votes
1
answer
659
views
Convert multipolygon to polygon with holes using ST_MakePolygon and ogr2ogr
I have a shapefile that is exported by Surfer. This shapefile is invalid and has 3 polygons. 1 large polygon and two smaller ones. The smaller ones lay on top of the large one, instead of in the large ...
- 323
0
votes
0
answers
169
views
Spatial index creation: MySQL vs Spatialite
I'm just starting to learn MySQL and I have a script that loads a large CSV file (4 million rows) into a table and then adds a spatial index to the GeometryLatLon column. The spatial index creation ...
- 175
1
vote
1
answer
203
views
AddGeometryColumn not creating column on new Spatialite database
I'm creating a new Spatialite database using c# and .net through the following code:
using var connection = new SqliteConnection($"Data Source={Path.Combine(outputDirectory, "data.db")}&...
- 249
1
vote
1
answer
894
views
Convert SQLite3 Geometry BLOB to WKB for PostgreSQL
I have used iterdump() to create an output file from a SQLite database that includes SpatiaLite data. I need to convert the hex data to a geometry in PostgreSQL.
I have found here a summary of how the ...
- 479
4
votes
1
answer
416
views
Difference between SQLite and SpatiaLite export from QGIS
When exoprting data in QGIS, I can choose between SpatiaLite and SQLite files. Both files get the file extension .sqlite.
What is the difference between these export formats?
Is it a different data ...
- 727
3
votes
1
answer
74
views
Retrieving maximum date from SpatiaLite DB in QGIS as a filter [closed]
I have a SpatiaLite DB layer with geometry that displays correctly and has lots of associated attributes. The layer feature count is 40943.
One of these attributes is a "Date", and I would ...
- 158
2
votes
2
answers
670
views
Can't query GeoPackage geometry via SQLite client
I have a GeoPackage and I want to select the geometry as text from the SQLite command-line client. Here's what I've done:
Run sqlite3
Load SpatiaLite with .load /path/to/mod_spatialite.dylib (I'm on ...
- 1,327
1
vote
1
answer
444
views
ST_Intersects support in GeoPackage
I generated a new GeoPackage with GDAL:
ogr2ogr -f GPKG my.gpkg my.geojson
I can open the GPKG in QGIS and all of the data is there, but when I open a SQLite client and run a spatial query, for ...
- 1,327
1
vote
1
answer
222
views
How to illustrate geodesic line reflecting bearing between two points + third projected point
I'm using Spatialite + ST_Project to extrapolate a line with an implied bearing between two points to some distance beyond those two points. Here's an example sql snippet:
SELECT AsGeoJSON(ST_Project(
...
- 111
5
votes
1
answer
94
views
Approximate ST_GeneratePoints in SpatiaLite
Is there an efficient way to approximate ST_GeneratePoints in SpatiaLite?
Here's one approach I can think of: get the bounding box of a geometry, then generate random points within the box using the ...
- 390
0
votes
0
answers
104
views
Layers with same coordinates not lining up - EPSG:4326 - WGS 84 - for Trentino in Italy
It's been a while that I've been noticing that the layers don't line up with open street maps, although I've always been setting the same coordinate system.
The coordinate system is EPSG:4326 - WGS 84....
0
votes
1
answer
68
views
SpatiaLite AddGeometry not accepting a variable [closed]
I have a program that asks users to browse for a .csv of X and Y points and then prompts the user via a sg.popup to enter a SRID (SRS) captured as string variable 'EPSG_Code'.
Once entered I'd like to ...
- 158
-1
votes
1
answer
365
views
Best and Fastest way to extract Polygon attributes of a Point [closed]
This is a comparative question, I don't know whether it is allowed to ask this type of Question here or not.
Problem: I have a shapefile of almost 1 million polygons with 20-25 attribute fields. I ...
- 3,217
2
votes
1
answer
416
views
SpatiaLite update/transform Geometry column CRS
How do I update the CRS for a table geometry column?
I have created my db and table with
spatialite_tool -i -shp London_Ward_CityMerged -d london_boundaries.sqlite -t Barnet -g geometry -c utf-8 -s ...
- 175
1
vote
1
answer
385
views
Spatial join ST_Within
I have two tables
Barnet
0 pk_uid INTEGER 0 NULL 1
1 name TEXT 1 NULL 0
2 gss_code TEXT 1 NULL 0
3 district TEXT 1 NULL 0
4 lagsscode TEXT 1 ...
- 175
4
votes
2
answers
445
views
Add image/photo as Blob to a geopandas dataframe and then save to a geopackage *.gpkg
Trying to switch from doing this through spatialite, and fully containing it within geopandas.
I have a file with the image's file locations in it, and loop through them all. Open each image and add ...
- 213