Questions tagged [sqlite]
A self-contained SQL database engine.
347
questions
-1
votes
0
answers
38
views
Optimizing SQLite3 database search using a search envelope (minx/miny/maxx/maxy)
I've created an SQLite3 database with the following table:
table = """ CREATE TABLE bounds (
value varchar(255) not null,
minx float,
...
0
votes
1
answer
30
views
Buffer lines using ogr2ogr
I'm trying to create a contour around polygons. I already managed to convert the polygons to lines and now I am trying to use SQLite and the ogr2ogr command to give them thickness. From online ...
0
votes
0
answers
39
views
QGIS forms and connected SQLite tables
I have a problem regarding QGIS forms for connected SQLite tables. The description is rather long, but needed in order to explain the issue.
While there are many tables, it is the connection between ...
- 11
3
votes
0
answers
61
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 ...
- 31
0
votes
0
answers
41
views
Geopackage no such table: main.gpkg_ogr_contents when deleting table contents
I want to delete contents in several tables in geopackage using the following command
DELETE FROM main.tablexyz
I’m able to delete some of my tables using this command but run into this error in a ...
- 3
4
votes
2
answers
513
views
Unesting JSON array in SQLite [closed]
I'm using DB Manager on QGIS to write a view. I want to "translate" unnest() SQL function, but I don't find the same in SQLite.
SQL
SELECT unnest(ARRAY['A','B','C']);
unnest
A
B
C
The ...
- 1,566
3
votes
1
answer
178
views
Creating relationships at the database level using QGIS and Geopackages
I've been trying to work out a way to create relationships at the database level of geopackages.
Simple scenario for example - Table 'clearing' is related to table 'sourcefile'. The solution is to ...
- 3,365
1
vote
1
answer
60
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
4
votes
1
answer
214
views
SQL functions supported by ogr2ogr
I have a GeoJSON, test.geojson:
{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { }, "geometry": { ...
- 1,308
1
vote
1
answer
59
views
ogr2ogr SQLite and json_each, filtering using WHERE does not work
using ogr2ogr I want to process a geojson file that has a JSON field (see minimal file below).
I tried the following command to extract the description corresponding to group a from the JSON field:
...
1
vote
1
answer
289
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 ...
- 469
3
votes
1
answer
136
views
ogr2ogr: Make polygon from points
I have a set of points in a csv file like this (ie. a square):
-77.63333333;-11.75
-77.63333333;-12.25
-77.3;-12.25
-77.3;-11.75
-77.63333333;-11.75
-77.63333333;-11.75
After getting rid of the semi-...
- 2,122
3
votes
1
answer
123
views
How can I calculate the nearest 3D distance (x, y, z) between points in Qgis?
I'm new to Qgis.
I have two point groups. One is the points representing the road with an interval of 1 m, and another is the forest area.
Each point has x, y, and z values.
I want to calculate the ...
- 33
4
votes
1
answer
194
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 ...
- 659
2
votes
1
answer
39
views
SQLite collaboratibe editing in Mergin-Maps
I've QGIS project using SpatiaLite database. I can see SQLite as a valid data provider in Mergin-Maps user documentation, but it is not listed as collaborative editing, only GeoPackage is. As SQLite ...
- 23
2
votes
1
answer
201
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,255
0
votes
0
answers
120
views
SQLite database not updating
I have a SpatiaLite database that I need to update from a shapefile.
I have successfully created a dictionary querying the shapefile and I've printed on my console statements like:
UPDATE POLE SET ...
- 513
1
vote
0
answers
194
views
Export raster to SQliteDB format in QGIS
Does anyone know how I can export a raster to .SQliteDB format in QGIS?
I created georeferences of satellite images with the GIS program Global Mapper for Locus Map, but now I want to understand how ...
- 11
1
vote
1
answer
627
views
SQLite in QGIS project
I have exported file with no geometry, values from attribute tables, to SQLite. I manage to open it in SQLite.
But when I want to import to another QGIS project or even the same project I get an ...
- 611
2
votes
1
answer
153
views
QGIS: SQLite DB with point data in lat, long columns (no geometry columns)
I have a SQLite database that I cannot modify (it is a local copy of a database sync'd from elsewhere).
I would like to view vector (point) data from a table in this database in QGIS. The table ...
- 93
1
vote
0
answers
96
views
GeoPackage (GPKG) with custom style and spatial area
With Qgis 3, with the tool available in "Processing" "Layer packaging", package layers within a GeoPackage database by saving the style. But unless I am wrong, it does not allow to ...
- 869
1
vote
1
answer
30
views
GDAL missing VFK driver
I've installed gdal v3.6.0 with conda v4.12.0, but I'm missing VFK vector driver. How can I add it?
When I run a siple script:
from osgeo import gdal
vfk_file = '727008.vfk'
ds = gdal.Open(vfk_file, ...
- 13
7
votes
3
answers
1k
views
Using input parameter as part of SQL query in QGIS model
Sample data for testing available here (the model described below is embedded in the QGIS project file)
In the context of a QGIS model, I am struggling to pass input parameters as part of a SQL query ...
- 452
2
votes
1
answer
195
views
Converting a shapefile to .sqlite using Python
I am trying to convert a shapefile to SQLite file in QGIS 3.26 using Python.
Here is my code:
def convert_to_sqlite(self):
input_shp = QgsVectorLayer("C:\shapeFile\BMR_Zones_Corrected_519.shp&...
- 303
6
votes
3
answers
322
views
Adding non-spatial SQLite query to QGIS
How do I load a query of a non-spatial table to be used as a join with a spatial layer? The DB Manager doesn't allow me to load as a layer and I don't want to load the whole table.
- 315
0
votes
0
answers
97
views
Add Query Feature Class to ArcMap using ArcPy
Is there any way to add a feature class from a .sqlite database to ArcMap using ArcPy (Python)?
If I drag and drop the feature class from the .sqlite database, ArcMap creates a Query Feature Class, ...
0
votes
1
answer
38
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 ...
- 116
1
vote
1
answer
190
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 ...
- 155
1
vote
1
answer
155
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 ...
- 155
1
vote
0
answers
214
views
Creating table with latitude and longitude with LeafLet, ASP.NET and SQLite?
I am trying to get the latitude and longitude and create a table from it, and show in a map, using Leaflet, however I didn't find anything that could help me in my project, the files that I have made ...
- 111
6
votes
1
answer
136
views
Compound primary keys prevents QGIS updates
I have a problem with a selected table in a geopackage database.
QGIS is preventing updates only, from happening on a particular table, in this database. QGIS is preventing editing of values, via the ...
- 3,365
0
votes
1
answer
156
views
QGIS: update query in db-manager fails
I've created a layer with photos using the tools in QGIS. This yields a table, where each line contains a filepath to a photo. So far so good. But the paths are all absolute. I'd like to share this ...
- 51
1
vote
1
answer
71
views
SpatiaLite visibility problem in QGIS
I have a .sqlite dataset, and I want to open it using QGIS, check the data, and plot some color maps. It's a 1 km by 1 km gridded data. When I import the data into QGIS using "add SpatiaLite ...
- 93
3
votes
1
answer
317
views
GeoPackage, SQLite: features count but don't exist (QGIS)
GeoPackage is my favorite geo-format but I also have some strange behaviors. One of them: I delete all records/features within a table using QGIS -> Delete Features or SQL statement but there are ...
- 8,196
1
vote
1
answer
286
views
Spatialite: insert points, do knn search. how to?
So I want to add 20 points, run Spatialite knn and find 10 points. I created a simple test in colaboratory:
import os
from datetime import datetime
import json
from pprint import pprint
from playhouse....
- 35
2
votes
1
answer
194
views
ogr2ogr PostGIS to SpatiaLite: convert hstore to json
I used this command to export PostGIS tables to SpatiaLite with ogr2ogr
ogr2ogr -f SQLite -progress -dsco SPATIALITE=yes ./mapfile/psql_tags.sqlite PG:"host=localhost port=5432 dbname=gis user=...
- 187
1
vote
1
answer
531
views
Can't create Auxiliary Storage layer in QGIS in order to move labels
When I try to move a label for a point in a layer called 'Places', I am prompted to choose a primary key, but when I select the fid column, I get the error 'Unable to execute 'CREATE TABLE IF NOT ...
- 243
2
votes
1
answer
132
views
Virtual-Layer/SQL-View: SQL to get list of all values in a group (SpatiaLite/GeoPackage) [closed]
I have a GeoPackage-Table and it's looking like that:
id data
-- ----
1 A
1 A
1 B
2 A
2 B
3 B
3 B
I need a virtual layer or a SQL-View that should look like that:
id data
--...
- 8,196
1
vote
0
answers
82
views
Extract concontours from S57 features stored in SQLite3 file
I have a table where one of the columns is a set of S57 objects given as a data buffer (FEATURE field):
OBJ_ID CHART_FID FEATURE
0 441153270841366 102714 [, M, _, C, O, V, R, *, ,...
- 69
3
votes
2
answers
436
views
Deleting overlaping vertex between line-features if not start/end vertex
I have a problem. I have a road-network dataset that has topological intersection where it's not supposed to be (see picture below). To solve this, what I want to do is to erase overlapping vertexes ...
0
votes
0
answers
47
views
Assigning point attributes values to respective start and end attributes of polyline using QGIS or Modellbilder
I have created a line layer manually connecting centroids of my sub catchments, then I have created a point layer (point2) of this line layer using ‘extract specific vertices’ function from QGIS, ...
- 305
1
vote
0
answers
51
views
Join sub-catchment centroid using line and import attributes of it using QGIS Model builder
Input Data:
I have a sub catchment polygon shapefile, which contain following details
Name
Description
TG_ID
Name of the Sub-catchment
Hoehe
Average height
Slope
Average slope
UTM_x_m
centroid x ...
- 305
0
votes
0
answers
92
views
Updating SQLite database upon changes in Attribute properties
I want to write a script to update the a property of an attribute in the SQLite database automatically.
What I could think of was creating a Python code that accepts 3 inputs, namely the id of the ...
- 1
1
vote
1
answer
337
views
Unable to open database file error when generating mbtiles
I am creating a docker image to run few tippecanoe to generate mbtiles pointing to a specific folder where all the .json are.
It seems to work fine when running the script in my local but when I ...
- 286
2
votes
3
answers
187
views
Placing point with specific attribute on top within overlapping points in QGIS
I have a layer with multiple points which have overlapping geometry. Each point is a control at a specific position with each individual attributes. They all have attribute "X" in common ...
- 91
2
votes
2
answers
240
views
ORDER BY with GROUP_CONCAT DISTINCT in QGIS3
There are two areas S1 and S2. Every area was visited two times to collect bumblebees. I want all species that has been found on each area as a comma separated string in alphabetical order.
area| ...
6
votes
2
answers
1k
views
Making DB Browser for SQLite run SpatiaLite queries
I've started using DB Browser for SQLite and I liked its interface. However, even though I can open SpatiaLite databases in there, I can't use any spatial function while running queries from there.
My ...
- 1,211
2
votes
1
answer
551
views
SQLite/SpatiaLite error: no such function: ImportGeoJSON
I'm inside a bash script trying to import a GeoJSON file into a SQLite DB.
I tried both ...
SPATIALITE_SECURITY=relaxed sqlite3 'db.sqlite' "SELECT load_extension('mod_spatialite')" "...
- 21
2
votes
1
answer
227
views
Improve SQLite spatial query speeds for multilayer gpkg and Python
I have a GeoPackage with two polygon layers (WGS84). The first layer is an Sentinel 2 tile grid with about 800 polygons. The other layer is about 300 000 small polygons (AOIs). Each layer has an rtree ...
- 1,600
0
votes
0
answers
193
views
Are Mobile Geodatabases Readable and Editable using ArcGIS Field Maps
The 100.12 release notes for Android state it can Read and edit standalone mobile geodatabases
We were able to sideload them to a directory within our mobile app and read and edit it in our ArcGIS ...
- 407