Questions tagged [spatialite]
SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.
707
questions
0
votes
1
answer
27
views
ST_Intersection in SpatiaLite does not return correct intersections
I was playing around with the ST_Intersection function in SpatiaLite version 5.0.1 and discovered some weird behaviour or in effect some inconsistencies in the determination of intersecting points.
...
- 23
0
votes
0
answers
35
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
9
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
27
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
21
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,554
0
votes
1
answer
53
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 ...
- 773
4
votes
0
answers
40
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,419
3
votes
1
answer
112
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
88
views
QGIS - Synchronize polygons changes between layers
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 ...
- 773
0
votes
0
answers
41
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
53
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
34
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 ...
- 649
1
vote
1
answer
102
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
0
votes
3
answers
269
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,702
0
votes
1
answer
63
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 ...
- 441
0
votes
0
answers
39
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 ...
- 773
2
votes
2
answers
101
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 ...
- 773
0
votes
1
answer
56
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&...
- 773
1
vote
1
answer
56
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 ...
- 773
0
votes
1
answer
51
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 ...
- 343
2
votes
1
answer
69
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 ...
- 773
3
votes
2
answers
181
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 ...
- 773
0
votes
0
answers
68
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 ...
- 773
1
vote
0
answers
60
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 ...
- 773
1
vote
1
answer
56
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, ...
- 773
1
vote
1
answer
202
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
68
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
41
views
OGR error creating feature -N: sqlite3_step() failed: UNIQUE constraint failed: layer.rowid (n)
Im in QGIS writing a plugin to syncronize 2 spatialite databases. I perfom a test to delete a feature in database A, then I click my syncronizer. And then I get this error in the message pane, not any ...
- 121
0
votes
0
answers
121
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
230
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
280
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
567
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
156
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
159
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
790
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
371
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
65
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
578
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
393
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
198
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
82
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
97
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
62
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
347
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,187
2
votes
1
answer
382
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
345
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
415
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 ...
- 203
1
vote
1
answer
319
views
Get extent of the current map canvas in a virtual layer query in QGIS
Is it possible to get the extent of the current map canvas in a virtual layer query? Something similar to the variable @map_extent in QGIS expressions that can be used to create a polygon covering the ...
- 73.8k
1
vote
1
answer
301
views
Correct way to use DROP TABLE on SpatiaLite in PyQGIS
I try to delete a table in an SpatiaLite database using PyQGIS. The table is deleted, but still shown in the browser panel.
I'm using the following code:
query = "DROP TABLE table_name;"
...
- 649