13
You can connect to Spatialite via Python using the latest version of pysqlite instead of pyspatialite. Spatialite is just the spatial enablement of SQLite so this works, but if you are connecting in this way (via pysqlite) you need to load the libspatialite extension to be able to use the spatial functionality of Spatialite.
from pysqlite2 import dbapi as ...
13
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 revealed to me that there is a distinction between Spatialite geometries and GeoPackage geometries.
To work with GeoPackages in Spatialite, there are several ...
12
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 used within a query
ST_Equals - ST_Disjoint - ST_Touches - ST_Within - ST_Overlaps - ST_Crosses - ST_Intersects - ST_Contains - ST_Relate
For this type of ...
11
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 edit the SQLite/SpatiaLite data via code as Insert/Update/Delete are supported.
What you can't do is open an edit session in ArcMap.
11
In the documentation, it is alluded to that you cannot edit data in a SQLite database from ArcMap:
You can connect from ArcGIS to an SQLite database to create maps and perform spatial analysis on your data.
However, the only place this appears to be explicitly stated by Esri is in the ArcGIS Discussion Forum:
Yes; you cannot edit data in a SQLite ...
10
For sake of posterity, the short answer is that MapInfo and FDO (AutoDesk technology) are far removed from any development with SQLite, compared to the members of the SQLite Consortium. MapInfo and FDO (AutoDesk) would be contributing binary to their downstream users, via their products and services.
Spatialite is specked under a triumvirate of licenses-...
9
The sqlite file from NE is in FDO-OGR format, not the native spatialite geometry.
If you're willing to do some manual labor, here's a way to convert to a spatialite db:
First make a new, empty spatialite database (I call it "nev.sqlite"), then in a separate terminal session open the original natural_earth_vector.sqlite with spatialite. (I used the newer ...
9
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 ID_ref, group_concat(a.id) AS ID_within_30m
FROM city_lots a, city_lots b
WHERE b.id = 1 AND b.id != a.id
AND ST_Distance(a.GEOM, b.GEOM) < 30;
...
9
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 initspatialmetadata(1);
It is necessary to have Spatialite installed on the system where these commands are run (sudo apt install libsqlite3-mod-spatialite ...
8
In SQLite, and thus also in SpatiaLite, there's no date type per se. See Datatypes In SQLite.
When a string column is saved in the format "YYYY-MM-DD" then you can apply some date functions to it, such as strftime() to get the date formatted in other ways. However, if you've saved your dates in any other way, they are NOT recognized as dates.
What I would ...
8
From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.
8
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 this:
And that means instead of querying the table d I can query vgpkg_d and the Spatialite geometry functions will work:
If I query from the original table ...
8
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 detection like with PostgreSQLs type-modifier system.
Having created a new table as you specified you should be able to 'spatially enable' it with
SELECT ...
7
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 rename them with SrcLayer
<OGRVRTDataSource>
<OGRVRTLayer name="temperature">
<SrcDataSource>rice_temperature.sqlite</SrcDataSource>
...
7
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 in order to represent the geometry of the features in SQLite SQL dialect and input in the SQL statement is the input layer name.
7
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 alias assigned to input ( and that is then fine, it can find the qualified attributes input.*, but there is no join_tbl.
Try:
""select input.*, joinedtb.* from ...
6
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.com/forum/#!topic/spatialite-users/n8l977RL9-0
conclusion:
1 = TRUE = ReadOnly View (unsupported write ops)
0 = FALSE = Not ReadOnly, thus Writable View
So ...
6
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 summe,
ROUND(DIST_KM +0.5, 0) AS Dist_group
FROM "flows_workday_GK5"
GROUP BY ROUND(DIST_KM +0.5, 0)
Edit:
There will be gaps in the groups if the data is sparse ...
6
In QGIS, make sure your processing toolbox is visible, then use the "Vector creation" > "Create Points Layer From Table" tool.
5
Firstly, wrap your input.shp and overlap.shp into an OGR VRT file (e.g. test.vrt) :
<OGRVRTDataSource>
<OGRVRTlayer name="input">
<SrcDataSource>input.shp</SrcDataSource>
</OGRVRTlayer>
<OGRVRTlayer name="overlap">
<SrcDataSource>overlap.shp</SrcDataSource>
</OGRVRTlayer>
&...
5
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 them into QGIS' own internal formats.
Once you have a project fully loaded, including a .shp file plus a .dbf, plus another CSV joined, are all the data in ...
5
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 construct a point with an unknown SRID
select ST_SetSRID(ST_MakePoint(x,y),26913) from data
or:
--Here, you have to concatenate your x and y values to form a WKT ...
5
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 query is searching for the rowids of points in "tbl1" by using the bounding box of the same geometries as search_frame. Every point will for sure find itself from ...
5
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 the SQLite database. The script requires that you run it from an ArcToolbox tool with 2 parameters: [0] the input .sqlite file, and 1 the output file ...
5
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 could not find any way to check with QGIS if spatial index exists or not. The index is stored into virtual layer that is named as [rtree][tablename][geometry ...
5
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
5
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 number and do what you want with, here your flow's summation SELECT.
Here the Virtual Layers / SQLite / GeoPackage working code :
-- number series
WITH ...
5
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.execute("PRAGMA foreign_keys = ON")
con.execute("SELECT load_extension('mod_spatialite')") -- add this line
cur.execute("SELECT InitSpatialMetaData(1);")
4
Here's something you might try:
You can create buffers around all the points at a "reasonable" distance that you choose based on the clustering. Then merge the circular buffers together. That should give you polygons enclosing the clusters of points.
In spatialite you would do:
Create a polygon table for the buffers and a second one for the merged circles:
...
Only top voted, non community-wiki answers of a minimum length are eligible
