Questions tagged [spatialite]

SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.

Filter by
Sorted by
Tagged with
1 vote
1 answer
35 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 ...
user avatar
1 vote
1 answer
28 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 ...
user avatar
2 votes
1 answer
24 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, ...
user avatar
1 vote
0 answers
37 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....
user avatar
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
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
0 votes
0 answers
55 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/...
user avatar
  • 1
1 vote
1 answer
103 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
1 vote
1 answer
42 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 ...
user avatar
  • 187
1 vote
1 answer
33 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 ...
user avatar
  • 187
1 vote
0 answers
39 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: ...
user avatar
-1 votes
1 answer
72 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 ...
user avatar
  • 851
0 votes
1 answer
54 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 ...
user avatar
  • 710
0 votes
1 answer
21 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 ...
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
2 votes
0 answers
73 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" ...
user avatar
  • 1,131
1 vote
1 answer
37 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 ...
user avatar
  • 1,021
1 vote
1 answer
324 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
2 votes
0 answers
91 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 ...
user avatar
  • 924
2 votes
1 answer
33 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 ...
user avatar
  • 710
2 votes
1 answer
106 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). ...
user avatar
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
1 vote
1 answer
154 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(...
user avatar
0 votes
1 answer
80 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 ...
user avatar
0 votes
2 answers
51 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 ...
user avatar
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
147 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
88 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
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
1 vote
1 answer
54 views

Show linestring geometry as centroid of a joined polygon layer with QGIS and spatialite ASSVG

In QGIS, I have a linestring and a polygon layer which are joined and I want to show the linestring as a centroid in the polygon layer. I used ASSVG() from SpatiaLite but wont't get a correct SVG. In ...
user avatar
  • 11
6 votes
2 answers
420 views

Is it possible to run a PostGIS query on a SpatiaLite database?

I like using SpatiaLite because, for me, it's easier to manage files on my computer instead of having tables inside a PostgreSQL database. However, there are sometimes I need PostGIS functions that I ...
user avatar
  • 1,021
3 votes
2 answers
133 views

Is there any way of dividing a polygon into multiple different polygons using SpatiaLite?

I have a SpatiaLite dataset (with the projection EPSG:4326) with the following content: I'd like to find a way of dividing this polygon into multiple smaller polygons... Something like the following: ...
user avatar
  • 1,021
1 vote
0 answers
21 views

Export part of schema to SpatiaLite

I want to export a part of a schema to SpatiaLite. It seems that I am to be able to export the whole schema with gr2ogr -skipfailures --config PG_LIST_ALL_TABLES YES --config PG_SKIP_VIEWS YES -f &...
user avatar
  • 346
1 vote
0 answers
101 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
3 votes
1 answer
226 views

Spatialite - equivalent of ST_AsMVT

I managed to make vector tile server in Java, using some libs, and simple code. In first example, I used PostGIS and example of it is this code: public byte[] vratiNestoPostGis(Integer z, Integer x, ...
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
0 answers
126 views

Generating line features with "must not have dangles" constraint in QGIS

I am making line features in QGIS and sometimes, if I work inaccurate, they have dangels (loose ends). That is what I want to avoid. There are ways to detect dangles after creating a feature (topology ...
user avatar
0 votes
0 answers
21 views

Qgis Work-around geometry-loss-mix-up-of-a-spatialite-view-joining-two-tables

Hi i use SpatiaLite views in QGIS (Version 3.16) with joined layers and it works well but it has a little flaw. It seems the same problem appears as mentioned in: Geometry loss/mix up of a spatialite ...
user avatar
1 vote
1 answer
80 views

Is it possible to execute the command line inside SpatiaLite?

Let's say I have a dataset filled with points on SpatiaLite and I don't have these points address location on this database. So, I make a new column on it for adding this address as the following: ...
user avatar
  • 1,021
5 votes
1 answer
120 views

Unable to save changes after merging features in QGIS

I have a Spatialite layer with two features, the result of a QuickOSM search. I'd like to merge them into a single feature. Before editing, the layer looks like this: I can enable editing for the ...
user avatar
  • 1,184
2 votes
1 answer
175 views

How can I create a new geometry column of points with the first value of a geometry column of polygons?

Let's say I have a Spatialite database filled with polygons. I've learned on this question that I can have as many geometry columns as I want in my database. So, I'd like to find a way of getting a ...
user avatar
  • 1,021
2 votes
1 answer
142 views

How can I filter values using a regular expression on SpatiaLite?

I know that on PostgreSQL I can filter columns using a regular expression as the following: select * from mytable WHERE mycolumn ~ '^word.*' It works perfectly fine on PostregreSQL. However if I try ...
user avatar
  • 1,021
6 votes
5 answers
318 views

Is there any way to differentiate and select polygons according to their shape on SpatiaLite/PostGIS?

I have this small SpatiaLite database and it looks like the following: It's made of polygons and I'd like to find a way of excluding all polygons that look more like a line. Something like excluding ...
user avatar
  • 1,021
2 votes
1 answer
186 views

Is it possible to add an extra column with a specific value on SpatiaLite using ogr2ogr?

Let's say I have a SpatiaLite database with a column called source where I have different kinds of values like the following source value1 value1 value1 value2 value2 Let's also say that outside the ...
user avatar
  • 1,021
0 votes
2 answers
199 views

Having changes to SpatiaLite table column to automatically update GIS layer

If the length of a line is changed (direct SQL), I would like the layer to reflect the new line length and also update the label for the feature. How can that be accomplished? It appears that ...
user avatar
  • 384
1 vote
1 answer
335 views

Using QGIS SpatiaLite/Geopackage databases on shared network drives

I am a GIS professional who has recently migrated over to QGIS from MapInfo and have been experimenting with the use of SpatiaLite and Geopackage databases to store our ecology data. I have structured ...
user avatar
0 votes
0 answers
81 views

QGIS Spatialite Performance/Data Limit

I am trying to load a table with over 27mn rows from my spatialite DB using QGIS. It has seem to hung while loading but I am not sure if it is just taking time to load everything. Even if it loads, I ...
user avatar
  • 23
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
3 votes
2 answers
555 views

Can I use the function ST_Buffer in meters with the projection EPSG:4326 on SpatiaLite?

I have a SpatiaLite database using the projection EPSG:31983 and I can see it inside QGIS as the following: When I use the following query I can successfully create a buffer of 100 meters on a point ...
user avatar
  • 1,021

1
2 3 4 5
14