Questions tagged [spatialite]
SpatiaLite is a spatial extension to SQLite, providing vector geodatabase functionality.
699
questions
2
votes
0
answers
407
views
Adding SpatiaLite to Visual Studio project (already has SQLite)
I am very new to SpatiaLite.
I am working on a large Visual Studio C++ project that already supports SQLite, and I am trying to add SpatiaLite capabilities to it.
After reading the documentation and ...
- 21
2
votes
1
answer
596
views
How to merge big and many polygons (SHP) in QGIS/Spatialite?
Background:
I have a bunch of (huge) SHP-files that I would like to merge to one big SHP-file that I can use for geo-processing (clip, etc.). Now I was able to merge a big part of the SHP-files to one ...
- 335
1
vote
3
answers
1k
views
How to modify spatialite table column names with QGIS?
I am trying to modify column names in a SpatiaLite table.
To do that, I run the following SQL statements in the DB Manager :
-- Rename existing table
ALTER TABLE compteur_routier RENAME TO ...
- 879
2
votes
1
answer
543
views
Adding per-row creation time and modification time in GeoPackage geometry table?
I need to add ctime and mtime to a GPKG sqlite database in table with geometry.
Apparently logic solution: add ctime column with DEFAULT CURRENT_TIMESTAMP and add trigger ON UPDATE to set mtime = ...
- 41
1
vote
0
answers
81
views
python 3.6 spatialite 4.3 on mac
In researching this problem, it appears that there is an issue with loading extensions in sqlite3 that requires a recompile of sqlite3 so SELECT load_extension("mod_spatialite.dylib") can be called. ...
- 11
0
votes
1
answer
407
views
Create vector layers in sql in spatialite db with PyQGIS / QtSql
I have a vector layer in a spatialite DB structured as follows:
In PyQGIS I try to create a derived vector layer from this like
from PyQt4.QtSql import *
db = QSqlDatabase.addDatabase('QSPATIALITE')
...
- 14.6k
0
votes
1
answer
567
views
ogr2ogr Export spatial data from SQL Server to Spatialite
I am trying to export some spatial data from SQL Server 2012 to Spatialite with the following command
ogr2ogr -f SQLite -update C:\tmp\Spatial.sqlite -nln "my_polygons" "MSSQL:server=host;database=...
- 335
5
votes
2
answers
1k
views
QGIS 3 does not load default styles from spatialite (defined in QGIS 2.18)
I have a set of styles stored in a spatialite db with QGIS 2.18, the layer_styles table looks like this:
Adding a layer in QGIS 2.18 loads the style set as default (waters light blue, categorized):
...
- 14.6k
3
votes
1
answer
362
views
Build spatialite db from scratch within build process
Having a scripted build process on Windows 10 (CMD or Powershell, both is possible) I want to build a spatialite database with the ready to use but empty schema created from a couple of sql files.
I ...
- 1,643
1
vote
2
answers
1k
views
Unique constraint for SpatialLite geometry column
I am creating a SpatiaLite database where I want to ensure that the contents of the geometry column for a table are unique. For normal SQL I would add a UNIQUE constraint to that column but, I don't ...
- 113
3
votes
1
answer
448
views
Plot points from a Sqlite (non spatial) table as a query on the canvas instead of making a new table
I have a table in an SQLite database with a non spatial table that contains 6 sets of coordinates, representing different features within a record. I would like to generate a series of layers (lets ...
- 355
2
votes
0
answers
153
views
Adding a Spatialite 2.3.1 layer in QGIS 3: invalid layer
I am running QGIS 3.0.3 and I am trying to add a Spatialite layer from this link
http://www.gaia-gis.it/spatialite-2.3.1/test-2.3.zip
The program says that it is an invalid layer and that it can't ...
3
votes
2
answers
1k
views
Can a Geopackage file be created by Spatialite
Starting with the Spatialite application, can a new Geopackage file be created, or is Spatialite only capable of working with existing Geopackage files?
- 3,367
5
votes
1
answer
1k
views
Sqlite ERROR: Too many connections: max 64
I would like to use the ST_SingleSidedBuffer SpatiaLite SQL function to create one-sided buffers around all lines in a layer. The following functions works fine, until the 64rd iteration. Then the ...
- 1,566
0
votes
1
answer
273
views
Reproject all layers in a spatialite file using ogr2ogr
I would like to reproject all layers in a spatialite file from one srs to another srs.
Is there a way I can archieve this without parsing the layers names to a txt-file?
I know I can write all ...
- 8,807
7
votes
1
answer
1k
views
Spatial indexes in GeoPackage files
I'm working with GeoPackage files built with QGIS3.
I tried to execute some spatial queries but it was really slow, so I'd like to speed it up with indexes.
After some readings I tried
create ...
- 699
3
votes
0
answers
307
views
How do I access my newly added fields in a spatialite layer from QGIS?
Forgive my language, I am a newbie in QGIS. I created a spatialite layer and added 4 fields. As I was working on the project I realised that I didn't need one of the fields, and I needed to add 3 ...
2
votes
1
answer
677
views
Open a Spatialite database in PHP
I need to open a Spatialite database by using php in a php's framework. I've already work on a basic php file to test how it work but it gave me some errors.
I've already try to load extension by ...
1
vote
2
answers
575
views
Spatialite geometry
FOR QGIS: Is there a benefit to having a single spatialite database that contains many different geometry types (e.g., point, line, polygon) over separate spatialite databases for each geometry type?
- 11
3
votes
2
answers
515
views
ST_ClosestPoint usage in QGIS 3.0
I try to use ST_ClosestPoint function to find the closest point to find the MultiLineString layer. My line layer (Reach) has 42 attributes and point layer (Stations) has 7 attributes. My code is:
...
- 1,581
1
vote
1
answer
736
views
Spatialite 4.3 err: no such function: gpkgAddGeomtryColumn
I'm opening a GeoPackage made by QGIS 2.18.17 then closing it and opening it with:
spatialite_gui (spatialite lib loaded:4.3.0a)
DB Browser (SELECT load_extension('mod_spatialite');)
Spatialite libs ...
- 612
2
votes
1
answer
2k
views
How to update/refresh fields/attributes after modifying SpatiaLite/SQLite database?
I have modified the columns in a SpatiaLite database table (by creating a new table with a renamed column, populating it with the old table, deleting old and renaming the new table to the old).
I ...
- 763
1
vote
2
answers
191
views
Styles embedded in Spatialite database are lost while copying database
I have created a Spatialite database with several layers. I have customised the style for each layer and have saved the style as the default style in the database. All works fine when I connect to ...
- 145
0
votes
1
answer
123
views
Store a style in spatialite using pyqgis and pyspatialite
Does someone knows is there a method of a qgis class to store styles to a postgis stored style. I know I can save a qlm file but I'm a searching for an alternative to this.
As an alternative I try ...
- 912
1
vote
0
answers
220
views
PyQGIS: Setting CRS for Buffer
I know that this is not an uncommon question, but I've struggled to find an answer that works for my application.
I'm trying to find the number of nearby permits for any given property. In order to ...
- 11
3
votes
1
answer
270
views
QGIS default style stored in spatialite db does not load when adding layer
I have a couple of default styles stored in a spatialite db. When I add the layer for which I have defined the default style in QGIS 2.14.22, everything looks good but trying the same in QGIS 2.18.16 ...
- 14.6k
2
votes
1
answer
265
views
Build spatialite_i.lib error
I am trying to build QGIS from source on x64 Windows machine.
QGIS: qgis2.99.0
VS: 2015
I downloaded latest cygwin, OSGeo4W etc. Now, CMake gives an error:
Looking for gaiaStatisticsInvalidate in ...
- 347
4
votes
2
answers
1k
views
Split planet.osm into multiple files based on UTM grid
Starting with the full OSM Planet data, I'm trying to create smaller, individual osm/pbf files, or eventually Spatia/SQLite or GeoJSON files, that are split on each UTM grid zone. I'm using osmfilter ...
- 195
2
votes
0
answers
6k
views
ogrinfo: symbol lookup error: /usr/lib/libgdal.so.20: undefined symbol: sqlite3_column_table_name [closed]
On trying to execute ogrinfo i got this error:
ogrinfo: symbol lookup error: /usr/lib/libgdal.so.20: undefined symbol: sqlite3_column_table_name
I got this on one of my PCs, though on the others it ...
- 138
4
votes
1
answer
661
views
Cannot select / move any labels with label movement tools
I'm working within a Spatialite database.
I have within my attribute fields Created X, Y, and Rotation columns.
Originally I could select a label using the "change label" tool.
Once the data ...
7
votes
1
answer
388
views
Spatialite KNN view in QGIS
My task is to calculate the average ground survey level for each tree in my dataset, using the 3 closest survey points (optionally within a 3m radius of the tree). I want to write this in a Spatial ...
- 12.5k
2
votes
2
answers
896
views
QGIS / Spatialite / DB Browser : basic : joining tables and more
Question : Is it possible to create a table in QGIS that will be a dynamic reference for geographical data entries?
As I'm sure this will sound very general and unclear, here's the actual situation I ...
- 33
4
votes
1
answer
423
views
QGIS Saving grid to SpatiaLite database efficiency
I wanted to make vector grid in QGIS basing on canvas extent and save it to SpatialLite db.
Therefore, I am creating new database:
Layer > Create Layer > New SpatiaLite Layer > ... > Save.
Then, I ...
- 53
6
votes
1
answer
1k
views
Using Polygon or Multipolygon for mapping fields in QGIS?
I'm trying to map medieval field systems in QGIS, this means that I'll be drawing quite a few polygons. I've mapped quite a few fields already using Spatialite layers using polygons, though I was ...
- 71
3
votes
1
answer
419
views
within polygon: why SpatialIndex slow down query
I create such simple demo table:
CREATE TABLE IF NOT EXISTS tbl1(id INTERNAL PRIMARY KEY UNIQUE, name TEXT NOT NULL);
SELECT AddGeometryColumn('tbl1', 'geom', 4326, 'POINT', 'XY');
SELECT ...
- 187
3
votes
2
answers
461
views
Keeping QGIS from writing all CRS to Spatialite database?
I am wanting to move from shapefiles to spatialite database files, but am new to QGIS and spatialite.
How do I keep QGIS from creating 4924 records in the spatial_ref_sys table and 4873 records in ...
- 53
4
votes
1
answer
1k
views
spatialite geometry vs PostGIS geography
When I worked with PostGIS I used geography type for geo objects with gps coordinates like latitude and longitude on the WGS-84 ellipsoid.
Now I have to use SQLite and libspatialite correspondingly.
I ...
- 187
2
votes
1
answer
699
views
Reprojecting coordinates using MakePoint() with SpatiaLite?
In my SpataLite-DB, I've got a table holding coordinates in EPSG:2056, splitted in two columns "x_coord" and "y_coord".
I want to copy them into another table, using MakePoint to convert the two ...
- 23
3
votes
2
answers
1k
views
Need help creating Europe reference grid map using EEA shape and spatialite files
I'm trying to generate a reference grid map of Europe with country and provincial boundaries using the EEA files available here. Here is an example of country and provincial boundaries with a raster ...
- 263
2
votes
1
answer
454
views
Getting banks layer from OpenStreetMap with QGIS?
I downloaded Mexico City data from with QGIS, and I want to make a layer only with the banks. When I select "Export topology from SpatialLite", it shows a long list of all the properties and asks me ...
- 143
2
votes
2
answers
404
views
Getting nearest line to point with virtual layer using QGIS?
I have a streetmap and the position of hundred sensorlocations. I am currently trying to calculate the nearest line for each point. I have already done it with the nnjoin plugin. But now I want to do ...
- 725
0
votes
1
answer
416
views
Dissolve polygons in Spatialite - New Table has no Geometry
I have followed some examples for dissolving polygons in Spatialite and successfully created my simpler polygons:
SELECT
"col1"
sum("total") AS "total",
st_union("GEOMETRY") AS "GEOMETRY"
FROM ...
- 437
1
vote
1
answer
337
views
How to iterate nearest polygons until threshold is reached?
I have a point layer and a polygon layer (representing buildings). I created a buffer around the points and extracted those polygons which intersect the buffer.
The points contain an attribute with ...
- 75.8k
2
votes
1
answer
87
views
QGIS behaviour when dealing with spatialite layers (splitting/merging features)
I'm wondering what is the behaviour of qgis when you work on spatialite layers.
For instance what I've discovered so far is that when you split a geometry in two. One remain the original (same id, ...
- 135
2
votes
1
answer
131
views
Is there a sequence stored in a QGIS layer that can be accessed via pyqgis?
I have a layer from spatialite. When I add a feature I want to know it's identifier (based on the primary key (integer) which is auto-incremental : for example XXXXXX-72) before the creation on the ...
- 135
2
votes
1
answer
641
views
Spatialite Spatial Views doesn't display in QGIS
I have views stored in a spatialite database accessed via QGIS. The views were created by another person on a different computer. The geometry column is well registered in the table ...
- 135
7
votes
2
answers
1k
views
Are there any QGIS tools or plugins that can display photos stored in a database?
A colleague's team has been using the eVis plugin in QGIS to store research photos. eVis relies upon a filepath or URL stored in the data source (shapefile, Spatialite db, etc.). They are using a ...
- 4,605
1
vote
2
answers
1k
views
How to check how many line intersects a intersecting point in same geometry
I want to know that how many line intersects a point. In figure below it can be seen that 3 lines crosses a point. 2 red lines and 1 blue line. I want to know in the same geometry that many lines ...
- 225
6
votes
2
answers
2k
views
How to create a SpatiaLite Layer in QGIS 3 with Python?
I have an existing SQLITE Database and I want to create several additional geometry Layers with Python.
Within QGIS 2.x I used the following library
from pyspatialite import dbapi2
According to ...
- 1,235
3
votes
2
answers
415
views
Can I save a newly created Intersection layer directly into a Spatialite database in QGIS without importing?
I have a point layer (xyz_coordinates_UTM33N) and a polygon layer (section1) in a SpatiaLite database.
I wish to extract the points within the polygon to a new layer, and I want that layer stored in ...
- 93