Hot answers tagged

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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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.
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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
user avatar
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 ...
user avatar
  • 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.
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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"
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 51.5k

Only top scored, non community-wiki answers of a minimum length are eligible