Questions tagged [sqlite]
A self-contained SQL database engine.
261
questions
2
votes
1answer
37 views
SQLite error - no such function: InitSpatialMetadata
I am porting a QGIS plugin to QGIS3.
I get a problem when I try to create a new SpatiaLite DB, in particular when I initialize spatial metadata (GEOMETRY_COLUMNS and SPATIAL_REF_SYS).
My code is:
...
3
votes
2answers
42 views
ogr2ogr selecting features by location
I'm trying select features from one shapefile that contains features from another shapefile. However when I run the command I get an error " no such column geometry" Both shapefiles are working ...
3
votes
1answer
59 views
QGIS 3 shows and saves raw default-values from spatialite-tables
QGIS shows default values from sqlite tables as string in the edit-form when i try to create a new row. After save, the raw default values are stored in the table. How can i edit in QGIS LTR 3.4.7 a ...
0
votes
1answer
40 views
Creating geodatabase using QGIS [closed]
I am using QGIS and am looking to get started on creating a geodatabase. I have point, line and polygon data. I want to arrange my database in categories that can store various point, line and polygon....
1
vote
0answers
30 views
Spatialite queries not loading entirely on canvas
I connected a SQLite database to my QGIS project. I wrote a query in the DB manager whose result contains 261 elements.
Then, I tried to load the result on the canvas (from the load button at the ...
0
votes
1answer
34 views
Return all results from join of non-spatial CSV to spatial data using ogr2ogr and SQLite dialect
I am attempting to carry out a simple join of a non-spatial CSV to a GeoJSON data source using ogr2ogr an its built-in SQL capabilities. I would like to use the SQLite dialect to carry out the join. ...
4
votes
1answer
64 views
Join non-spatial CSV to GeoJSON data source using ogr2ogr and SQLite dialect
I am attempting to do a simple join of a non-spatial CSV file to a GeoJSON data source using the built-in SQL capabilities in ogr2ogr. I have successfully managed to do this using the OGR SQL dialect ...
0
votes
0answers
11 views
Retrieving data from an SQLite database where each point lies withing a sea-area
I have lat/long data stored in a simple SQLite database, which works well for my previous analysis (global analysis and more concentrated area analysis where I define a square grid to narrow it down). ...
4
votes
1answer
59 views
QGIS: Creating custom CRS without using the GUI
What would be the appropriate way to create a custom CRS without using the GUI of QGIS on Windows?
I am trying to create a custom CRS but my GUI seems to be broken. I cannot use the normal way ("...
2
votes
1answer
31 views
How do I read a layer from a .gpkg file whilst selecting on an attribute?
I have a GeoPackage (.gpkg) database with only one layer:
ogrListLayers("data/LM_data/zipped/ay_riks.gpkg")
[1] "ay_riks"
attr(,"driver")
[1] "GPKG"
attr(,"nlayers")
[1] 1
There are too many objects ...
0
votes
0answers
23 views
ogr2ogr - ST_DUMP(geometry)
I have been trying to run:
ogr2ogr -f "Mapinfo File" out.tab in.shp -dialect SQLite -sql "SELECT ST_DUMP(geometry),* FROM in"
but there is always an error like this:
no such function: ST_DUMP
...
0
votes
0answers
18 views
Copy features from a SQLite multipolygon to SQLite polygon layer
I need to copy features from a multipolygon layer that contains multipart features into a polygon layer in SQLite in QGIS. I know I can run the "Multiparts to Singleparts" algorithm and simply copy ...
8
votes
1answer
135 views
Spatialite spatial queries do not function on GeoPackage
I have downloaded some data (as GeoPackages) from this excellent website: https://data.mfe.govt.nz/layer/52757-petroleum-wells/ and https://data.mfe.govt.nz/layer/52675-location-and-extent-of-nzs-...
1
vote
0answers
58 views
Decimal places in SQLite
I'm working with a locally stored SQLite database and have an area field that auto populates via a virtual field using $area. Works fine but there seems to be no way to set the precision in a decimal ...
3
votes
1answer
183 views
How do I use pre-existing Lat/Long data in a SQLite table with QGIS?
I have an application that stores its data in a SQLite database. I can't modify the application or database to add geometry extensions, but it does already have latitude and longitude columns for each ...
9
votes
2answers
234 views
Using RECURSIVE in Virtual Layer
Preface
For a while, I was wondering about applying the looping/iterable
functions in QGIS's Virtual Layer. There were several cases when the
application of a loop would be beneficial and ...
1
vote
1answer
72 views
Why does the AddGeometryColumn function not exist in RSQLite?
I am trying to add the geometry to a new table with the following statement:
library(RSQLite)
# db.sqlite was created with spatialite-gui
con <- dbConnect(RSQLite::SQLite(), dbname = "C:/GIS/db....
0
votes
0answers
35 views
Calculating shortest route in Geopackage/SQLite
Are there any graph analysis tools written for a SQLite database? What I'm looking for is something akin to pgRouting but for SQLite. I can't find anything with a significant user base, which is ...
0
votes
0answers
18 views
Error when running v.net.distance
I'm running grass76 on CentOS 7. When I have a network map (generated using v.net) with layer 1 having the arcs and layer 2 and 3 having the sets of nodes that I need to connect. Details are as ...
0
votes
0answers
54 views
ogr2ogr sqlite dialect can't find functions
my question might be related to this Dissolve using ogr2ogr gives 'no such function: ST_union'
What I am trying is st_unioning my landparcel polygons based on its group.
The code is very ...
1
vote
1answer
39 views
Transforming a GeoPackage table's geometry column into GeoJSON?
I have a GeoPackage that is downloaded to client devices for a mobile app. I need the clients to query the GeoPackage to retrieve a GeoJSON representation of the geom column. I'm familiar with ...
1
vote
1answer
62 views
Error when importing shapefile as vector layer into GRASS GIS
I'm tried to importing an Esri shapefile into GRASS GIS using both v.in.ogr and v.in.import however methods fail with the following error message:
WARNING: Table <roadsSriLanka> linked to ...
0
votes
0answers
80 views
Running SpatiaLite K-Nearest Neighbors search (VirtualKNN) in Python?
I was using python to run the spalite KNN search (pleasse see code below), but the python terminated without showing anything (no errors). It seems that this query makes python crash.
Can anybody ...
1
vote
0answers
34 views
Removing layer from database in PyQGIS?
I've got a PyQGIS 3 project up and running with a .sqlite database, with which I'm adding layers to with a QgsVectorFileWriter, without too many issues so far.
However I've noticed that once a ...
4
votes
0answers
98 views
PyQgis 3 - Add New Layer to Spatialite Database
For the most part, working with QGIS layers has been a pleasure, but I've run into some difficulties recently when trying to create a new vector layer in the SQLite database I've been using.
I've ...
0
votes
1answer
41 views
Saving default layer name to spatialite/geopackage table?
I'm a huge fan of being able to save default symbology to spatialite/geopackage tables, however, I don't like that when I import a table as a layer it takes on the name "database name table_name".
...
2
votes
0answers
78 views
Why ST_Distance give something different to 0?
I have this SQL:
select
ST_Distance(GeomFromText('POINT(-0.338767 39.55425)', 4326), geom) dis1,
ST_Distance(GeomFromText('POINT(-0.338767 39.55425)', 4326), geom, 1) dis2,
AsEWKT(geom) ...
2
votes
1answer
53 views
GDAL SQLite geometry error
I am having the following issue writing points to a SQLite file using ogr with SPATIALITE enabed:
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
...
3
votes
0answers
61 views
Processing a Sqlite table in standalone QGIS application
I have been trying to use a single table of a Spatialite db in a standalone Python application. I have run it through the QGIS interface to find how it used the input pathway, which was: 'dbname=\'C:/...
0
votes
0answers
96 views
Loading spatialite tables into GeoPandas GeoDataFrame
Using information from here, I am trying to load a result of a SQL Python selection into a Geopandas geodataframe. I know pyspatialite is not longer maintained, so have tried using the dbapi2 module ...
1
vote
1answer
52 views
Determining whether the geometry field in a SQLite table has z coordinates using Python
In the Shapely package, there is a function that allows you to determine whether coordinates include a Z value. It is object.has_z.
Is there a way that a similar command can be carried on the ...
0
votes
1answer
345 views
Error: Geometry constraint geom-type or SRID not allowed. How to ensure the correct geom-type?
Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:
engine.execute("SELECT AddGeometryColumn('your_table'...
0
votes
0answers
133 views
Loading and displaying NDS (Navigation Data Standard // SQLlite)?
On QGIS 3.4 (Windows), I would like to load and display NDS (Navigation Data Standard) data (attributes and geometry). I succeed in loading NDS data sample from openlanemodel.
Via the QGIS DB manager,...
1
vote
1answer
181 views
Difference between MapBox mbtiles and offline mbtiles for MapBox Android SDK
I know what have some difference between MapBox mbtiles and offline mbtiles for MapBox Android SDK.
In offline mbtiles for SDK besides tiles contains:
Style JSON.
All fonts and icons associated ...
2
votes
1answer
83 views
Valid datetime in GeoPackage
I am adding spatial objects to a GeoPackage database. To register a new spatial table I use a formulation like:
INSERT INTO gpkg_contents
(table_name, data_type, identifier, description, ...
1
vote
0answers
74 views
GRASS: import PostGIS vector layer into SQLite database
I am working with vector layers (points) composed of more than 100 000 geometries. These layers are originally in a PostGis database and I import them with v.in.ogr, using a formulation like:
v.in....
0
votes
1answer
57 views
Importing NAS Data into SQLite database? [closed]
I'm trying to import German NAS-Data into a SQLite- Spatialite-database. For this I use the following command:
ogr2ogr -f SQLITE --config OGR_SQLITE_CACHE 8192 -gt 65536 -skipfailures -s_srs "+proj=...
1
vote
1answer
21 views
Python SQLite JSONSTRINGLIST
I have converted an XML (NAS) - File into a SQLite-Database using ogr2org - which worked quite well. Now I'd like to read the data using Python (3.72) - which works quite good as well. But there is a ...
2
votes
0answers
91 views
How to delete openmaptiles (OSM Mbtiles) layers
I am struggling with converting PBF to Postgresql Table (importing only certain layers) or editing them, for the volume of the data. (world scale of PBF is about 80Gbs)
meanwhile, I have found that ...
2
votes
1answer
127 views
Add FID to attribute table using ogrinfo
I want to add the fid to the attribute table using ogrinfo in OSGeo4W Shell.
Here is my code:
ogrinfo layer.shp -sql "ALTER TABLE layer ADD COLUMN ID integer"
ogrinfo layer.shp -dialect SQLite -...
1
vote
0answers
68 views
Using QgsVectorFileWriter to write several layers on same Spatialite file?
Does anybody knows how to write several QGIS layers on a same spatialite file using QgsVectorFileWriter or another way to add several same layers type to a Spatiallite specific table using ...
0
votes
0answers
32 views
Delete rows or tables from mbtiles
I have downloaded mbtiles from openmaptiles.org and I am trying to remove layers that I do not need using DB Browser for SQLite.
What I am trying to get rid of is the ocean layer and I do not know ...
0
votes
2answers
114 views
SQLite spatial function
I have a SQLite database, with point and linestring geometries. Between the points(blue) and the linestring(green) is a gap, which I want to close with another line(red). The line should have angle of ...
0
votes
0answers
37 views
Get a column from database and populate the shapefile attributes?
I would like to know how to get copy a specific columns from the database and add that column in a shapefile attribute table?
I am open to other better ideas.
3
votes
0answers
202 views
Loading editable SQLite layer in QGIS?
I converted a shapefile to a SpatiaLite database so I could work with attributes with a length longer than 254 characters, and also not see some columns and change their order.
In QGIS 2.18.16 I have ...
0
votes
1answer
117 views
Can a Geopackage style be deleted through SQL window in DB Manager?
I am trying to clean up a Geopackage and as part of this I would like to delete some styles from the "layer_styles" table. I have tried doing this with the below query in the SQL window of the DB ...
2
votes
1answer
441 views
How to open and add SQLite connection to browser with PyQgis
I have a code to open the layers of a SQLite database. When executing this code, the layers are loaded correctly (1 & 2). However i also need to connect to the database and show the connection in ...
0
votes
1answer
335 views
Can multiple MapBox vector tile mbtiles be merged just using SQLite?
Is it possible to combine two vector mbtiles files using just SQLite?
I noticed this question Joining several raster .mbtiles?
but it does not seem to work with vector files.
I also came across ...
-2
votes
1answer
409 views
Difference between SQLite and SpatiaLite? [closed]
I wrote telegram bot for my uni project that receives from user spatial and non-spatial data (coordinates and user identity) and puts them in an SQLite database. Then when I extract this database and ...
1
vote
1answer
333 views
Adding SQLite layers to ArcGIS Pro throws error “Exception from HRESULT: 0x80041538”
I have a SQLite database which I have just created in QGIS and I want to open it in ArcGIS Pro.
In Catalog I have added the database to the project but when I try to add layers to the map I get the ...
