17
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 ...
- 7,763
13
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 ...
- 896
11
votes
Accepted
Documenting inability to Start Editing SQLite geodatabase layers in ArcMap?
It's not strictly read-only access. You can load data via copy/paste and import into a SQLite or SpatiaLite database. Tables and feature classes can be appended to using append or Load Data.
You can ...
- 126
10
votes
Accepted
How to rename ogr2ogr tables on export to Spatialite?
From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.
- 39.7k
9
votes
Accepted
Concatenate or group by values using spatial join in QGIS?
Concerning your first request (concatenate the list of unique lot id's which are bound within a radius of 30m from the given lot), I guess you can do that with a SQL query in QGIS :
SELECT b.id as ...
- 813
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 ...
8
votes
Accepted
ST_Intersect with ogr2ogr and Spatialite
With your VRT file GDAL tries to find layers "temperature" and "elevation" from the target databases. Either use the original layer names in VRT in OGRVRTLayer name: "rice_temp" and "rice_elev", of ...
- 51.5k
8
votes
Accepted
Merge all features in polygon shapefile with gdal
In order to merge all features into one, you should do:
ogr2ogr output.shp input.shp -dialect sqlite -sql "SELECT ST_Union(geometry) AS geometry FROM input"
where geometry is the special field used ...
- 13.6k
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 ...
- 51.2k
8
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 ...
- 23.3k
7
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,752
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 ...
- 854
6
votes
Accepted
Creating SpatiaLite View in QGIS?
The "read_only" table obviously needs some input, as I could not find anything in the cookbook, after some search I found this insightful discussion with mr. furieri himself:
https://groups.google....
- 1,145
6
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
- 176
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 ...
- 1,702
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.
- 51.8k
6
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 ...
- 219
5
votes
Accepted
QGIS - what is the core database system?
QGIS has its own internal handling of layers and features. Different data "providers" are used to pull layers in from a variety of sources (eg PostGIS, OGR file types, WFS servers, etc) and translate ...
- 26.2k
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,214
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 ...
- 3,885
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 ...
- 51.5k
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 ...
- 450
5
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 ...
- 51.5k
5
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"
- 325
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 ...
- 11.8k
5
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....
- 3,845
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 ...
- 28.6k
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 ...
- 51.5k
Only top scored, non community-wiki answers of a minimum length are eligible