Questions tagged [sqlite]
A self-contained SQL database engine.
324
questions
6
votes
1
answer
78
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 ...
- 2,754
0
votes
0
answers
15
views
QGIS: display RMaps SQLite data as layer
I am using QGIS 3.22.
What I would like to do is add a layer with data from a tiled map that I have available as RMaps SQLite file. So far, I did not find an option to do that. Is this possible in ...
- 111
0
votes
0
answers
13
views
Qualified KNN query on SpatiaLite
Is there a way to do a qualified KNN query on SpatiaLite?
I want to do a KNN search but specify some criteria on the table. For example, if I had a table of cities I want to return the K cities ...
0
votes
1
answer
25
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
25
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
115
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 ...
- 7,695
1
vote
1
answer
104
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....
- 77
2
votes
1
answer
63
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
0
votes
0
answers
35
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 ...
- 224
2
votes
1
answer
72
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
--...
- 7,695
1
vote
0
answers
29
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
90
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
40
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, ...
- 183
1
vote
0
answers
30
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 ...
- 183
0
votes
0
answers
46
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
0
votes
0
answers
97
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 ...
- 226
0
votes
0
answers
40
views
Calculate a centered Midpoint from a set of Lat and Longs in a given region
I am writing an Android app that will display placemarkers in regions you have visited over the world. In order to find where to place these placemarkers I am querying a Gadm36 (world) database for ...
0
votes
0
answers
53
views
How can I change the geometry of a Geopackage
I have a Geopackage that has a geometry field geom. I updated the field successfully with SQL: SET geometry=MakePoint(555985,5755607,25832)
But for some reason the geometry type is set to ...
2
votes
3
answers
58
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 ...
- 31
0
votes
0
answers
11
views
Joining two .sqlitedb maps into one using MOBAC or other software
I have two .sqlitedb maps converted from same source - for example Google Sattelite (one part complementing another).
How can I join them into one .sqlitedb map using MOBAC or other software?
2
votes
2
answers
83
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| ...
1
vote
1
answer
326
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,021
0
votes
1
answer
153
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')" "...
- 1
2
votes
1
answer
117
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,491
0
votes
0
answers
70
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
3
votes
0
answers
914
views
"Failed to process SRS definition" error when reprojecting vector layer via ogr2ogr
I'm using GDAL 3.3.1 with all the necessary variables already setup.
I'm reprojecting a vector layer using ogr2ogr ogr2ogr -t_srs EPSG:32651 test.shp new_layer.shp and an error says
ERROR 1: PROJ: ...
- 1,159
0
votes
1
answer
126
views
How to set up Python3 with SQLite 3 and SpatiaLite on MacOS
I've developed a converter for spatial data which currently runs on Windows and Linux in Python3. On Windows I need to exchange the given sqlite3.dll for the original one from the SQLite website, for ...
2
votes
1
answer
149
views
Spatialite does not use gpkg_spatial_ref_sys in GPKG mode
Spatialite and GPKG mode are enabled:
SELECT load_extension('mod_spatialite');
SELECT EnableGpkgMode();
Spatialite knows about gpkg_spatial_ref_sys (gpkgInsertEpsgSRID() uses it), and the table
is ...
- 131
4
votes
1
answer
89
views
Can "--config OGR_SQLITE_CACHE <size>" be set while using ogr2ogr?
I use ogr2ogr (GDAL 3.2.1) to export some Spatialites out of Postgresql.
As I was inspecting the exported data via the tools provided by Alessandro Furieri by using PRAGMA cache_size and PRAGMA ...
- 43
2
votes
1
answer
177
views
ogr2ogr missing geometry when selecting from one sqlite db into another
I have a more complicated dataset in real life but here I'll include a simplified case to illustrate what is happening. I'm almost certain it due to my lack of knowledge on how to use ogr2ogr properly ...
- 203
0
votes
0
answers
43
views
QGIS DB Manager - executing more than one statement at a time
Can I run more than one statement at a time on QGIS in the db manager?
I'm currently doing this using a geopackage and using basic sqlite queries.
1
vote
0
answers
25
views
Storing Line(2D) data in Navigation Data Standard format as geometry using SpatiaLite
Is it possible to store Line(2D) data in Navigation Data Standard format(with coordinate bit shift) as geometry into SQLite and also make use of SpatiaLite functions for querying?
I have to use ...
- 11
0
votes
0
answers
140
views
Error on creating a geometric column in SQLite
Many post has been written on this problem but I can't figure out what is my problem here. I want to create a geometric column in a SQLite database to store polygons (geojson format or WKT). I use ...
- 73
1
vote
0
answers
103
views
Point sampling using SpatiaLite
I have two tables in my SpatiaLite/SQLite3 database. One contains a number of points with no other data, and the other a number of polygons with associated columns of values.
ID
GEOMETRY
1
point ...
0
votes
1
answer
86
views
Vector tiles from SQLite database with Java
Currently, I'm serving vector tiles exported from QGIS shapefile with Export to PostgreSQL function.
Is there any simple Java library that could be used to serve these vector tiles from SQLite ...
- 133
1
vote
1
answer
103
views
Creating features from existing point layer in Standalone PyQGIS script
I am trying to add a feature into an existing layer in my SQLite database.
The script below is working:
layers = QgsProject.instance().mapLayersByName('msm_LoadPoint')
layer = QgsVectorLayer(layers[0]....
- 31
2
votes
1
answer
198
views
From SQLite db select one layer and create a shapefile
I have a SQLite database with multiple tables.
I just want at the end to create a shapefile with one of the layer/table (called msm_Catchment) that I have in my SQLite.
I want to run the Python script ...
- 31
2
votes
1
answer
164
views
Spatialite KNN - No results
SOLVED: f_table_name was not working as no spatialindex was created/defined using just the tutorial for VirtualKNN - Solution: Run the query
SELECT CREATESPATIALINDEX('tablename','geom_col')
Original ...
- 23
2
votes
1
answer
142
views
Load data from GPKG incrementally in R
I need to process a GPKG file in R, but it's too large (50GB) to load the entire thing into memory at once. Is there any way to incrementally load the results? Right now we are loading it with a ...
- 123
0
votes
1
answer
39
views
Is it possible to change distance in links table in AequilibraE plugin?
I'm in a project, which want to compute fastest path for container truck with some path are blocked. I am using QGIS with plugin AequilibraE (get data from OSM) to solve it, but I can't describe ...
1
vote
1
answer
73
views
gdal_translate SQLite driver has no raster capabilities
I am trying to convert a raster to SQLite using rasterlite2 and gdal a la:
gdal_translate -f SQLite raster.img raster.sqlite
but after running the command I get the following error:
ERROR 1: SQLite ...
- 11
2
votes
3
answers
701
views
No ST_* spatial functions are available in SQLite3
I'm struggling when using SQLite3 inside a docker container build on this (Ubuntu 20.04 based) GDAL image osgeo/gdal:ubuntu-small-3.2.1.
I have to install sqlite3 first by:
# apt-get install -q sqlite
...
- 6,748
3
votes
1
answer
281
views
Howto list EPSG codes as proj.4 strings from the new Proj 6 sqlite3 proj.db
I want to list all EPSG codes from the new PROJ.6 tool in form of proj.4 strings. Is there a tool / SQL script to replicate the former /usr/share/proj/epsg file from the SQLite3 proj.db?
- 3,463
3
votes
1
answer
172
views
Load layers from SQLite database in Model Designer
Regularly, I have to load layers from SQLite databases through Spatialite in Qgis 3.16 and perform some algorithms. Extension of these SQLite databases is not *.sqlite but another format (proprietary)....
- 344
2
votes
0
answers
153
views
Tilemill MBTiles SQLite database schema
I'm trying to resolve the inability to load a raster MBTiles map on a GPS that added support for the format.
Maps created using QGIS or CalTopo will work. The SQLite database only contains two tables-...
- 21
2
votes
0
answers
141
views
Combining Mapbox GL JS plugin with another SQLite-based plugin (Cordova)
I'm creating an App with Cordova. For this I want to use Mapbox GL JS for using offline maps and a SpatiaLite database for storing additional spatial data.
The mapbox-gl-cordova-offline plugin ...
- 53
1
vote
0
answers
148
views
Creating and writing GeoPackage Lookup Tables
I am writing Python using OGR to create and write a single vector layer in a GeoPackage. Some of the feature attributes need to be foreign keys to several lookup tables. My needs are completely ...
- 645
5
votes
2
answers
730
views
CREATE TABLE AS with geometry in SpatiaLite
I would like to create a new SpatiaLite table based on a command.
For example, I would like to transform a table to a different coordinate system:
SELECT Transform(Geometry, 3068) FROM points_berlin;
...
- 6,249
0
votes
1
answer
50
views
Compiling on CentOS fails due to ignored LIB flag
I need help with installing PostGIS.
In general: I try to install it on a cluster (CentOS) and do not have admin rights. There is a sqlite3 installation (connected to the error), but this seems ...
- 1
0
votes
1
answer
28
views
GRASS GIS 7.8 trigometric function of vector attribtue
I need to calculate the sin and cos of a vector attribute column, but these functions do not appear to be available. In Python I've tried using v.db.update:
gscript.run_command('v.db.update', map='...
