Skip to main content

Questions tagged [spatialite]

SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.

Filter by
Sorted by
Tagged with
0 votes
1 answer
47 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 ...
Giene's user avatar
  • 711
4 votes
0 answers
38 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 ...
Tom Brennan's user avatar
  • 5,219
3 votes
1 answer
108 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 ...
Sch's user avatar
  • 41
1 vote
2 answers
85 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 ...
Giene's user avatar
  • 711
0 votes
0 answers
35 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 ...
Elham Peiravian's user avatar
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 ...
user240504's user avatar
0 votes
1 answer
33 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 ...
ThiPa's user avatar
  • 649
1 vote
1 answer
91 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"...
Dennis's user avatar
  • 35
0 votes
3 answers
135 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 ...
Steve Bennett's user avatar
0 votes
1 answer
55 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 ...
Vic's user avatar
  • 411
0 votes
0 answers
37 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 ...
Giene's user avatar
  • 711
2 votes
2 answers
97 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 ...
Giene's user avatar
  • 711
0 votes
1 answer
51 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&...
Giene's user avatar
  • 711
1 vote
1 answer
50 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 ...
Giene's user avatar
  • 711
0 votes
1 answer
45 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 ...
Joe G's user avatar
  • 191
2 votes
1 answer
62 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 ...
Giene's user avatar
  • 711
2 votes
2 answers
144 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 ...
Giene's user avatar
  • 711
0 votes
0 answers
66 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 ...
Giene's user avatar
  • 711
1 vote
0 answers
52 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 ...
Giene's user avatar
  • 711
1 vote
1 answer
49 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, ...
Giene's user avatar
  • 711
1 vote
1 answer
143 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 ...
Sch's user avatar
  • 41
4 votes
0 answers
63 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'. ...
Isaac Braham's user avatar
0 votes
0 answers
34 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 ...
Paal Pedersen's user avatar
0 votes
0 answers
76 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} ...
user232211's user avatar
2 votes
1 answer
195 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 ...
EranGeo's user avatar
  • 1,182
3 votes
1 answer
226 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 ...
M I P A's user avatar
  • 33
4 votes
2 answers
995 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 ...
J.Watt's user avatar
  • 51
0 votes
1 answer
485 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 ...
Paul Meems's user avatar
0 votes
0 answers
145 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 ...
Rojj's user avatar
  • 175
1 vote
1 answer
127 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")}&...
Walter's user avatar
  • 249
1 vote
1 answer
678 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 ...
CNIDog's user avatar
  • 479
4 votes
1 answer
333 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 ...
e-shirt's user avatar
  • 667
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 ...
Nick_Jo's user avatar
  • 158
2 votes
2 answers
478 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 ...
bertday's user avatar
  • 1,315
1 vote
1 answer
351 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 ...
bertday's user avatar
  • 1,315
1 vote
1 answer
186 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( ...
Jeff Sisson's user avatar
4 votes
0 answers
66 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 ...
fgregg's user avatar
  • 378
0 votes
0 answers
94 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....
tommaso ravanelli's user avatar
0 votes
1 answer
53 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 ...
Nick_Jo's user avatar
  • 158
-1 votes
1 answer
336 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 ...
Devils Dream's user avatar
  • 3,179
2 votes
1 answer
347 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 ...
Rojj's user avatar
  • 175
1 vote
1 answer
291 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 ...
Rojj's user avatar
  • 175
4 votes
2 answers
368 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 ...
Biaspoint's user avatar
  • 193
1 vote
1 answer
294 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 ...
Babel's user avatar
  • 72.3k
1 vote
1 answer
275 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;" ...
ThiPa's user avatar
  • 649
1 vote
0 answers
54 views

Level of SpatiaLite support on CentoOS 9, specifically spatialite_osm_raw

Running a script on CentOS9 (from within WSL2), I get the following error FileNotFoundError: [Errno 2] No such file or directory: 'spatialite_osm_raw' on Ubuntu, I was able to install the required ...
Biaspoint's user avatar
  • 193
1 vote
1 answer
341 views

Accessing SpatiaLite tables in custom QGIS Plugin

I have a SpatiaLite database that has relational tables to link geo entities that can be reflected in QGIS as layers. To link the relations I need some custom forms where I can select these entities ...
Pixelfahnder's user avatar
1 vote
1 answer
204 views

Spatialite Routing Point-to-Point

I tried to setup routing in Spatialite following this guide. The setup of routing data was alright. I'm trying to achieve route from point to point that are not lying directly on the network. Im able ...
Albine Pro's user avatar
3 votes
1 answer
50 views

How to map a single PostGIS function to multiple Spatialite functions with GeoAlchemy2

In PostGIS we have ST_Affine(geom, a, b, d, e, xoff, yoff) but in Spatialite this functionality is split across 2 functions: ATM_Transform( geom , affine_matrix ), and ATM_Create(a, b, d, e, xoff, ...
iwishiwasaneagle's user avatar
1 vote
0 answers
174 views

SpatiaLite ST_Transform does not return geometry in QGIS virtual layer

I am using the following query to SpatiaLite in a QGIS virtual layer but it is returning null geometries (looking to transform from ESPG:4326/WGS 84 to ESPG:27700/OSGB 1936): SELECT st_transform(p....
George of all trades's user avatar

1
2 3 4 5
15