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 ...
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 ...
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.
9
I don't know if this is the answer you want, as it's not a point and click answer, but this is how I would do it probably.
In Spatialite, add a new geometry column. Assuming you want WGS84 (lat/lon)
AddGeometryColumn( yourTableName , geometryColumnName, 4326, 'POINT', 'XY')
Then create the geometry from WKT generated from the X/Y coordinates
update ...
9
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
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 ...
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 ...
9
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 ...
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
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;
...
7
I found the solution as for source table did not created with spatialite option which default is false.
To do that simply add parameter to ogr2ogr utility -dsco "SPATIALITE=YES" then all SpatialLite parameters shall work as requested.
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
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>
...
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
In QGIS, make sure your processing toolbox is visible, then use the "Vector creation" > "Create Points Layer From Table" tool.
5
You can install the Open Source ArcGIS OGR Workspace plugin from AmigoCloud that will give ArcMap read access to spatialite and a whole bunch of other formats. It is free. Let me know if you want to test the binaries - I already have some people doing that with success. They only work with 10.1 at the moment since that is the only ArcGIS version I have ...
5
ogr2ogr?
See here for more information and maybe here as well
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
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
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 ...
4
Since QGIS can read OGR layers, try using OGR's Virtual Format: http://gdal.org/ogr/drv_vrt.html .
Create a text file with the following content and open it as vector layer in QGIS (Layer -> Add Vector Layer). Just replace the table name as well as the latitude and longitude column names according to your database table. It's recommended to store the file ...
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
