18
votes
Accepted
Spatialite spatial queries do not function on GeoPackage
The issue is with the way I am using Spatialite.
After talking with the company behind the web service that provides these datasets (since I initially assumed it may have been an export bug), they ...
- 8,828
14
votes
Accepted
select by location using ogr2ogr sqlite
At the following link all SQL functions that test spatial relationships are described:
http://www.gaia-gis.it/spatialite-2.4.0/spatialite-sql-2.4.html#p12.
The following spatial relationships can be ...
- 966
11
votes
Accepted
CREATE TABLE AS with geometry in SpatiaLite
SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic ...
- 30.6k
9
votes
Accepted
Creating SpatiaLite view (for use in QGIS) from non-spatial SQLite database?
The following commands can be used to add a Spatialite view to non-spatial tables in a SQLite database.
Add Spatialite capabilities to the database.
SELECT load_extension('mod_spatialite');
SELECT ...
9
votes
Accepted
How do I read a layer from a .gpkg file whilst selecting on an attribute?
Although this does not explicitly resolve your issue, the following example should demonstrate that the st_read function can be used successfully in conjunction with an SQL query. As mentioned above, ...
- 4,912
8
votes
Accepted
Valid datetime in GeoPackage
The geopackage spec[1] calls for ISO-8601 formatting.
You can get this format using strftime('%Y-%m-%dT%H:%M:%fZ','now')
[1] http://www.geopackage.org/spec/#r5
- 196
8
votes
Accepted
How to read geometry from gpkg?
The binary format for geometry in GeoPackages is not compatible with the format used in Spatialite. That's (partly) why GDAL has separate drivers.
When I start the Spatialite GUI it helpfully displays ...
- 65.5k
8
votes
Converting osm.pbf to shp-format using ogr2ogr to extract major highways
Indeed, I actually had four distinct osmconf.ini files on my system, all in different subfolders of R, thanks for the comment @user30184. The following lines produced what I wanted, only highway lines ...
- 249
7
votes
Accepted
How does QGIS 3.2.3 handle or create spatial indexes within a geopackage?
When you create a new GeoPackage layer you can select if spatial index is created or not from the advanced options
If you want to add the index later goto layer properties and press the button
I ...
- 67.9k
7
votes
Accepted
Join non-spatial CSV to GeoJSON data source using ogr2ogr and SQLite dialect
My guess is the FROM part of the statement ( untested). The error states
no such table: join_tbl
In the query, you have:
""select input.*, join_tbl.* from input join 'join_tbl.csv'"
There is no ...
- 886
7
votes
Accepted
Making DB Browser for SQLite run SpatiaLite queries
Ensure you're not running a sandboxed version (snap or flatpak), install it from your distribution repositories instead. Sandboxed apps can't see the real /usr/lib directory (e.g. when accessing /usr/...
- 67.7k
7
votes
Adding non-spatial SQLite query to QGIS
There are at least four options: the three options before loading the data as a layer, the last one after the layer is loaded.
Option 1: Data source Mananger Filter
Use Data Source manager to load ...
- 73.8k
7
votes
Accepted
SQL functions supported by ogr2ogr
The doc says:
The syntax of the SQL statements is fully the one of the SQLite SQL
engine.
and
Spatialite SQL functions
When GDAL/OGR is build with support for the Spatialite library, a lot
of extra ...
- 42.5k
6
votes
Accepted
Add FID to attribute table using ogrinfo
I found a simple way to add a rownumber with ogr
ogrinfo layer.shp -dialect SQLite -sql "UPDATE layer set ID = rowid+1"
- 365
6
votes
Using RECURSIVE in Virtual Layer
I don't think you need recursive, it looks like you want subtotals for different categories of "DIST_KM"
Does this give something close to want you expect:
SELECT COUNT(*) as anzahl, SUM(FLUX) AS ...
- 2,072
6
votes
Accepted
How do I use pre-existing Lat/Long data in a SQLite table with QGIS?
In QGIS, make sure your processing toolbox is visible, then use the "Vector creation" > "Create Points Layer From Table" tool.
- 67.7k
6
votes
SQLite error - no such function: InitSpatialMetadata
You won't make a error if you load the extension mod_spatialite. Look at the following code :
import sqlite3
con = sqlite3.connect(dbname)
con.enable_load_extension(True)
cur = con.cursor()
cur....
- 4,110
6
votes
Accepted
How can I use ogr2ogr & sqlite to create a new GeoJSON file that contains only a specific set of rows while renaming two of the keys?
You can. You already found out most of the solution. You just need to ask for the geometry field e.g
ogr2ogr -f geojson -dialect sqlite -sql "SELECT ID as aid, min as amin, max as amax, geometry ...
- 31.1k
6
votes
Accepted
Difference between SQLite and SpatiaLite export from QGIS
The GDAL driver page gives rather good explanation https://gdal.org/drivers/vector/sqlite.html. SQLite database gives the platform and there are different ways to store geospatial data into SQLite ...
- 67.9k
6
votes
Accepted
How to decide when to use the SQLite dialect when running ogr2ogr on large files
It is impossible to give a generic answer especially because your use case is rather special. What makes it special is:
The source data is GeoJSON, a format that does not support any kind of indexing....
- 67.9k
5
votes
ogr2ogr add geometry from x y when importing to postgis
If the data is now in your database you can use ST_MakePoint or ST_GeomFromText to construct a geometry object from your x and y columns, e.g.:
--ST_SetSRID is also used here as ST_MakePoint will ...
- 4,075
5
votes
Accepted
within polygon: why SpatialIndex slow down query
Think about what your sub-query is really doing:
WHERE tbl1.ROWID IN
(select ROWID from SpatialIndex where f_table_name= 'tbl1'
AND f_geometry_column='geom'
AND search_frame = tbl1.geom);
The ...
- 67.9k
5
votes
What are the considerations for choosing between WKT and WKB geometries?
The comments cover this pretty well, but I'll summarize them in an answer.
WKT is text and human readable. So use this if you want a human to read them (eg. for quick verification that the data is ...
- 1,314
5
votes
Accepted
Spatial indexes in GeoPackage files
The "search_frame" shortcut is only used by the SpatiaLite library. GeoPackage standard does not define such a shortcut but you must use subquery or join in the query. Query that is utilizing r-tree ...
- 67.9k
5
votes
ogr2ogr mapFieldType giving an error
You seem to have misunderstood how -mapFieldType works, from the manual
-mapFieldType srctype|All=dsttype, ...:
(starting with GDAL 2.0) converts any field of the specified type to another type. ...
- 82.6k
5
votes
Accepted
Importing Survey123 SQLite Database from Device
I built this tool to export Survey123 data from a SQLite database (downloaded off the device used to capture the data) to a file geodatabase. It creates one feature class for each type of survey in ...
- 490
5
votes
Accepted
Using RECURSIVE in Virtual Layer
With RECURSIVE query, you have to do a generate_series (PostgreSQL function not supported by SQLite), which create you a number series from conf.start to conf.stop by conf.step.
Then, retrieve this ...
- 15.9k
5
votes
Howto list EPSG codes as proj.4 strings from the new Proj 6 sqlite3 proj.db
You can use Python for this purpose
Install the Python lib
# use version 2.4.2 as it install a version 6 of proj behind the scene
python3 -m pip install pyproj==2.4.2 pyproj
Then, run the following ...
- 31.1k
5
votes
Deleting overlaping vertex between line-features if not start/end vertex
If you have FME I think it's quite easy. I would use a LineOnLineOverlayer to extract the intersection points and in parallel a TopologyBuilder to extract the end points. The TopologyBuilder has a ...
- 159
5
votes
Accepted
GeoPackage, SQLite: features count but don't exist (QGIS)
GDAL and QGIS want to know how many features there are on a layer but SELECT COUNT(*) is slow for big tables. Therefore GDAL creates an additional metadata table gpkg_ogr_contents from where the ...
- 67.9k
Only top scored, non community-wiki answers of a minimum length are eligible