Questions tagged [spatialite]

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

Filter by
Sorted by
Tagged with
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: ...
1
vote
0answers
26 views

Using Spatialite to add features to a layer does nothing in custom plugin but works in QGIS python console

Context I am developing a QGIS plugin which among other things can save digitized features to existing vector layers in a Spatialite database. I am using the utility class below, to help with ...
2
votes
1answer
67 views

ST_Distance in Virtual Layer query not working on QGIS on Mac but on Windows

I tried to use solution posted here, but ST_Distance in the Virtual Layer query doesn't work properly in Qgis (v3.2) on Mac, the result is showing blank while the same script is working properly in ...
3
votes
1answer
38 views

Extracting/casting mixed geometry types to single type using ogr2ogr?

I have a GeoJSON file with mixed geometry types, something like this: { "type": "FeatureCollection", "features": [{ "type": "Feature", "geometry": { "type": "GeometryCollection", "...
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 ...
2
votes
1answer
36 views

Updating GeoPackage records with R

I'd like to update records of a GeoPackage containing vector information in R. I've tried this: library(sf) library(RSQLite) nc <- st_read(system.file("shape/nc.shp", package="sf")) # Write Geo ...
0
votes
0answers
25 views

Extracting and inserting polygon geometry from SpatiaLite

I've created the following script which attempts to copy a SpatiaLite table from one db to another, one record at a time. It's a learning exercise and I will eventually be doing some per-record ...
1
vote
2answers
45 views

Refresh Oracle and Spatialite Layer with PyQgis

I'm working on a Dockwidget with PyQgis. I made an insert into an Oracle DB and a Spatialite DB and I would like to see the new insert (it's a point). The Layer where the new point is add is ...
4
votes
1answer
44 views

Referencing a polygon layer in the field calculator of a point layer to make a virtual field

I have a polygon layer that shows the counties of England and a point layer of project sites. I am trying to make a new virtual field within the point layer that will reference the county that it is ...
0
votes
0answers
32 views

Why is spatialite query taking so long (only) on first execution?

I have an AWS lambda function deployed with a spatialite table. When queried, a point lookup is done on the table to find the locations in the table containing the point. The first execution of the ...
1
vote
1answer
70 views

Extract part of multipolygon that does not intersect with another geometry in Spatialite / QGIS DB manager

I have a point layer (with a buffer value), and a polygon layer (footprint) that intersects the point buffer. Using only SQL in QGIS Virtual Layers (i.e. based on Spatialite), how can I get the ...
0
votes
0answers
34 views

Why can't I upload shapefiles into my SpatiaLite database

When I try to import a layer into my SpatiaLite Database, I get this error message. Why is this happening now ?
3
votes
1answer
42 views

Creating grid references with virtual fields

I wish to create a virtual field that will automatically update when new attributes are added; the equation is for working out the 10 figure grid reference of a point shapefile in SpatiaLite: floor(((...
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-...
0
votes
1answer
25 views

Adding“last modified” and “created” column in SQLite/SpatiaLite table

I am designing a new db schema for a SQLite/SpatiaLite database. Each table should get two extra columns called modified and created which should be automatically change as soon a row gets inserted ...
2
votes
0answers
18 views

Creating multipoints from points and setting incrementing M value in spatialite

I have a layer of points, which I want to group into multipoints grouping by some column. This part is easy: SELECT name_pre, CastToMulti(GUnion(geometry)) AS geometry FROM sp_survey GROUP BY ...
1
vote
0answers
30 views

Layers from SpatialLite Database are not displayed correctly in QGIS

While working a lot with layers from spatialLite databases I face a returning bug in which layers from databases aren’t displayed correctly in the project. The error affects persisting layers as well ...
0
votes
0answers
41 views

Spatial index not speeding up spatialite query containing Multipoint Geometry Data?

I am querying a sqlite format geodatabase in which there is a label for each geometry shape. Think of data as : label = name of a district in your state geometry: polygon which specify the ...
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 ...
1
vote
1answer
51 views

Cast 3D geometry as 2D for ogr2ogr GeoJSON

I'm trying to take a shapefile that can be downloaded here (specifically the shapefile titled PVS_19_v1_vtd_21031.shp), convert it from EPSG:4269 to EPSG:3857, and output the GeoJSON into CSV with ...
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 ...
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
0answers
15 views

How to load a merged geometry view from Spatialite to QGIS

In DB manager on QGIS 3.6 I have a view created from a table in a Spatialite database using st_union(geometry). The geometry field is created and seems to have geometry stored in it, but it loads as ...
0
votes
1answer
342 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'...
1
vote
1answer
77 views

Calculating distance from centroid points to multiple polygon edges with SpatialLite

My question builds on a question previously asked (Calculating all distances from single points to multiple polygons). User @alexandre-neto provided an answer that I've worked on, but it isn't giving ...
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,...
0
votes
1answer
80 views

Constructing query to return values from all polygons that line passes through using SpatiaLite? [closed]

I want an SQL query in Spatialite to return a list of values (Primary Key in this case, but could be any value) from all the polygons which are crossed by a line. Say, for example, a footpath passes ...
1
vote
1answer
177 views

How to create table with geometry column in GPKG with spatialite?

I want to create a vectorial GPKG with spatialite. With the help of the documenation (here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html#p16gpkg ), I've created a sql script that look ...
0
votes
0answers
22 views

Remove duplicate polygons in a single layer using ogr2ogr? [duplicate]

I have a spatialite database file containing buildings within a country as multipolygons. While trying to use QGIS to delete these duplicate features, QGIS keeps on crashing due to the millions of ...
0
votes
0answers
70 views

QGIS @parent or import to database(Error 7) not working for layer

I have a shape file layer that won't load into the SpatiaLite database and it gives Error 7. It also won't run functions that work in other layers and works fine without the @parent or if I remove ...
0
votes
1answer
22 views

Counting (Multi)Polygons Within (Multi)Polygons on same Table

So I have a table with approximately 10k rows, with each row having its corresponding geometry entry. What I am attempting to do is count how many row geometries are contained/within the geometry of a ...
0
votes
1answer
53 views

Initialise a database with SpatiaLite 4.3

I am starting to work with SpatiaLite 4.3 in an empty database. SpatiaLite requires a number of meta-data tables, where it stores information about geometry fields. For previous versions there are SQL ...
1
vote
1answer
60 views

Field added to SpatiaLite table doesn't show in QGIS

I have a spatial table in a SpatiaLite database that I'm editing in QGIS with no problem (the spatial table was created through SpatiaLite GUI). I then added a text field to the table through SQL ...
1
vote
1answer
92 views

Finding nearest line from points in SpatiaLite?

I have writtern a query in Spatialite to get the nearest line from a point. I want to do that for all points. How can I modify the below codes: SELECT g1.id As gref_id, g2.id As gnn_gid FROM ...
1
vote
1answer
71 views

Efficiently query two properties QGIS spatialite

I have a simple query that involves two steps: non-spatial criteria (should be super-fast) spatial criteria (which is really slow) I would like to run the spatial query only on the filtered layer (...
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=...
0
votes
0answers
175 views

Loading spatialite with python3's module under Windows

I installed spatialite's Python module using pip: pip install spatialite I copied the mod_spatialite.dll found on https://www.gaia-gis.it/gaia-sins/index.html into my windows/system32 folder (I have ...
0
votes
0answers
40 views

Apply gdal_rasterize with spatilite

I am trying to rasterize a spatialite database using gdal_rasterize. I am using gdalUtils in R to do so and have run the following gdal_rasterize -burn 1 -sql SELECT * FROM plants WHERE binomial="...
1
vote
0answers
39 views

'SELECT AddGeometryColumn' in C++ crashed

I am using MSVC 2018 developed a GIS program. I use spatialite API to create a SQLite geometry database. The program always crashed when executing "Select AddGeometryColumn'. I don't know if I need ...
1
vote
0answers
26 views

Auto delete 5th record while inserting 6th record of polygon to SpatiaLite table

I am having problem while inserting 6th record to SpatiaLite table it auto deletes 5th record, and after that it is ok. I am using SpatiaLite database to insert some polygon using Google Maps API in ...
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
46 views

Suitable Image or container that has ST_MakeValid functionality

I'm looking for an easy to deploy machine image that would allow me to use the ST_MakeValid function. I'm looking to use this in an ogr2ogr call, but can't get a version compiled correctly against ...
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 ...
2
votes
2answers
120 views

Seeking list of spatial functions available in PostGIS but unavailable in Spatialite?

It seems like most of the classic spatial function available in PostGIS (i.e. ST_Buffer) are usable with Spatialite, but some others don't seem to be supported in Spatialite (i.e.: no such functions: ...
1
vote
1answer
42 views

Arcs from a Delaunay Triagulation

I am trying to generate a Cost Distance table using Spatialite. I have several points in the table random_points for which I would like to compute the Delaunay triangulation and use the segments it ...