Questions tagged [sqlite]

A self-contained SQL database engine.

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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....
user avatar
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=...
user avatar
  • 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 ...
user avatar
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 --...
user avatar
  • 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, *, ,...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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?
user avatar
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| ...
user avatar
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 ...
user avatar
  • 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')" "...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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.
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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]....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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?
user avatar
  • 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)....
user avatar
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-...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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; ...
user avatar
  • 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 ...
user avatar
  • 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='...
user avatar

1
2 3 4 5
7