Questions tagged [spatialite]
SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.
679
questions
2
votes
1
answer
56
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 ...
- 1,132
3
votes
0
answers
48
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
4
votes
2
answers
869
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 ...
- 51
0
votes
1
answer
70
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 ...
- 323
0
votes
0
answers
74
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 ...
- 155
1
vote
1
answer
57
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
1
vote
1
answer
282
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
4
votes
1
answer
192
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
3
votes
1
answer
54
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 ...
- 116
0
votes
0
answers
62
views
Loading SpatiaLite layers into PyQGIS [closed]
I am trying to figure out why PyQGIS won't load SpatiaLite layers.
According to the cookbook: Pages
the following snippet should gather the requested layer:
home = os.getcwd()
uri = QgsDataSourceUri()
...
2
votes
1
answer
197
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
1
vote
1
answer
201
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 ...
- 1,255
1
vote
1
answer
90
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(
...
- 111
4
votes
0
answers
55
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 ...
- 378
0
votes
0
answers
81
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....
0
votes
1
answer
37
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
votes
1
answer
221
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 ...
- 3,141
1
vote
1
answer
185
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
149
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
4
votes
2
answers
224
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 ...
- 193
1
vote
1
answer
181
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 ...
- 63.1k
1
vote
1
answer
171
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;"
...
- 595
1
vote
0
answers
36
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 ...
- 193
1
vote
1
answer
229
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 ...
- 23
1
vote
1
answer
126
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 ...
- 280
3
votes
1
answer
40
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, ...
- 151
1
vote
0
answers
112
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....
- 1,022
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
1
vote
0
answers
306
views
How to build shared library from spatialite as static library NDK
I want to build a shared library for android to link with JNI,
all libraries on which it depends are static libraries.
I used this to build spatialite and sqlite for android :
https://github.com/...
- 11
1
vote
1
answer
284
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
191
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
96
views
ogr2ogr OSM polygon layer export from PostGIS to SpatiaLite has no CRS
I used ogr2ogr to export osm layers from PostGIS to SpatiaLite using this command:
ogr2ogr -f SQLite -dsco SPATIALITE=yes ./mapfile/psql_polygon.sqlite PG:"host=localhost port=5432 dbname=gis ...
- 187
1
vote
1
answer
235
views
OSM to SpatiaLite (spatialite_osm_map or ogr2ogr) for mapnik
I am trying to convert OSM data of a region (136MB pbf) to SQLite.
I have two options: spatialite_osm_map or ogr2ogr.
My region's SpatiaLite file is 932MB with ogr2ogr, while it is 741MB with ...
- 187
1
vote
0
answers
78
views
In QGIS how do the providers 'spatialite' and 'ogr' influence loading, editing and processing sqlite/spatialite layers?
Referencing this older post it seems to me, that indeed the provider is dependent on how the file was loaded:
-> ogr: Loading via Vector (Data Source Manager) or drag & drop
-> spatialite: ...
-1
votes
1
answer
304
views
SQL statement for count of different ranges [closed]
In QGIS, I want to make a SQL-statement which should return the counts of different year ranges e.g. in column 1 should be the total count for distinct values for all years, in column 2 1950-1990, in ...
- 911
0
votes
1
answer
150
views
Changing projection of SpatiaLite layer in QGIS
I created a SpatiaLite layer from Geofabrik OSM data (.pbf) using ogr2ogr. When I load the data into QGIS it only shows up as WGS84. I would like to change it to ESRI:102003 or similar so i can do ...
- 822
0
votes
1
answer
56
views
CreateTopology fails Spatialite
I wanted to experiment with the topology-geometry support that is available in SpatiaLite 5.0.1, but hitting a roadblock upon creating a topology. I am following the tutorial Familiarizing with ...
2
votes
0
answers
103
views
Keep date and timestamp when converting from PostgreSQL/PostGIS to Esri File-Geodatabase using Spatialite
I use ogr2ogr to export geodata from PostGIS to SpatiaLite like follows:
ogr2ogr -f SQLite -dsco SPATIALITE=yes C:/target.sqlite PG:"connectionstring" -sql "select * from blabla"
...
- 1,171
1
vote
1
answer
301
views
Making ogr2ogr convert from CSV to SpatiaLite while keeping capital letters on the header
Let's say I have the following input.csv file:
Column1,Column2,latitude,longitude
testData,TestData,-23.547384,-46.643363
testData,TestData,-23.555297,-46.635642
I know I can convert it from CSV to ...
- 1,211
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
0
answers
338
views
How to Multipart to Singlepart a QGIS virtual-layer?
I have a polygon layer I want to explode into single lines. I preferably wish to do so in spatialite since I work almost exclusively in QGIS DB-manager. No matter which function I use the number of ...
- 1,098
2
votes
1
answer
45
views
OSM timestamp missing in SpatiaLite layer
I have downloaded and converted a geofabrik pbf file to a db file. When I open it as a SpatiaLite layer there is no timestamp for each feature. Is there a way to get this information or is it lost ...
- 822
2
votes
1
answer
255
views
Calculating the angle of view from points to a polygon
This question Calculate field of view / angle of a point to a shapefile was attempting to achieve a similar outcome to my need (calculating the angle of view between observer locations and a polygon). ...
- 1,022
2
votes
1
answer
550
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
1
vote
1
answer
518
views
Aggregating point geometries and creating envelope using Virtual layer in QGIS
I am trying to group points using a Virtual layer and create a rectangle covering an extents but I cannot get it to return the geometry.
Current code is this:
SELECT
VP.Page,
st_envelope(...
- 1,022
1
vote
1
answer
233
views
Changing the value of geometry using changeGeometryValues in SpatiaLite layer
I am trying to update a geometry using changeGeometryValues, it works fine when the layer is of type shp or geopakage, but when I use it in SpatialLite layer it gives me the following error.
This is ...
0
votes
2
answers
142
views
SpatialIndex table broken after copying databases
I have several map layers in a SpatiaLite database in memory. All of these have been copied from multiple separate on-disk databases.
I want to do a query that involves updating the columns in a layer ...
1
vote
0
answers
508
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 ...
5
votes
1
answer
486
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 ...
- 191
5
votes
1
answer
202
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 ...
- 53