Hot answers tagged

5

I increasingly use Spatialite with QGIS effectively as a good, portable store for projects - and would say that the quick answer is 'yes, it's usable'. Store and access spatial and non-spatial tables in one place; Create views with linked data easily; Store style data together with tables / layers. However - I am also occasionally frustrated by some of ...


4

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.


4

This is doable with QGIS and SAGA (either standalone SAGA, or via the Processing panel in QGIS) I assume by 'XZY DEM' you mean an ascii grid. If the points are not regularly gridded (e.g. from UAS survey or LIDAR), you can also do this by importing into SAGA as a Point Cloud from an .xyz text file, then converting the point cloud into raster. If it's an ...


4

I have a doubt about your expectations If it's doing SQL query on a SQlite/Spatialite DB within QGIS Select the layer that use SQlite as a source, then do # Import QtSql function(s) from PyQt4.QtSql import QSqlDatabase # Get selected layer layer = iface.activeLayer() # Get file path uri = QgsDataSourceURI(layer.dataProvider().dataSourceUri()) # Create DB ...


4

Use Spatialite Database! It is a lightweight file based spatial DB supported out of the box by QGIS. First set-up a spatialite DB following theses instructions Push your two tables to this spatialite DB using QGIS DB manager Assuming that your tables are called "polygon" and "line" run the following SQL command in DB manager query interface. SELECT ...


4

cur.execute(sql) rows = cur.fetchall() for row in rows: print row or for one record: row = cur.fetchone() print row


3

It is definitely possible to pull all the layers into one database, although in a multiuser environment, this will be best if most users will only be reading (not editing) the data. The easiest way to quickly convert a large number of shapefiles to a single SpatiaLite database is to use ogr2ogr. Make sure your shapefiles are in a single folder (e.g. ...


3

You can do this with two UPDATE statements, one for the distance, and the second for the line ID, with a subquery to get the values from the line table. And use the ORDER BY ST_Distance(...) LIMIT 1 construct to get only the closest line. I have a cities point layer, and a hiways line layer. Each has a primary key column 'pk'. I added to the cities two ...


3

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 ...


3

As it says in the help document gave by ArMoraer. The command create the file for you if it not exist and if you have rigths to write in your work directory. A another explanation of your error may come from the querie that ask to display everything (*) in the 'table_name'. But I assume you know a bit of sql and python if you are asking this question. (No ...


2

You can create an empty SpatiaLite database using Layer > Create Layer > New SpatiaLite Layer. Once you've done so, it will appear in the DB Manager (an included plugin; enable from the plugin manager if it is disabled). Alternatively, if you have an existing database you would like to add layers to, add it with Layer > Add Layer > Add Spatialite ...


2

I do this with: echo [% "id" %]," [% "name" %], [% "year" %] OBSERVATION: If you have two or more point in the same local, you will get only one response. I need the next response, if someone knows how to do: id name year (for point 3) id name year (for point 3) id name year (for point 3) example: 17 Elsa 1985 ...


2

You are only inspecting osm_id field. It seems you didn't inspect your multipolygons table. On a local use case, I do : ogrinfo -so france.simple.spatialite multipolygons It returns FID Column = OGC_FID Geometry Column = GEOMETRY osm_id: String (0.0) osm_way_id: String (0.0) name: String (0.0) type: String (0.0) ... So the identifiers are not only ...


2

Try register your VIEW this manner: INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only) VALUES ('buffer500m', 'geometry', 'ROWID', 'point', 'geom', 1); read_only option boolean 1 for read only VIEW - read_only option boolean 0 for updatable VIEW. In Spatialite Cookbook not consign ...


2

Error means that your program is compiled with such Spatialite version that does not have ST_MakeValid function. Only way to fix is to recompile with a proper version. Notice that ST_MakeValid is a LWGEOM function so Spatialite must be compiled accordingly with liblwgeom. Notice also that liblwgeom is using GPL license. If you can't compile yourself as a ...


2

Now that the QuickOSM plugin installed I have no problems


2

The problem was with the version of Sqlite Python was using. The sqlite3.dll shipped with ArcGIS's Python 2.7.5 could not load the mod_spatialite extension. So solve the issue, I simply downloaded the .dll for the newest version of sqlite3 (available here) and then replaced the sqlite3.dll found in C:\Python27\ArcGIS10.2\DLLs with the new one.


2

If you use DB Manager, you can use plain SQL to union two tables. Something like this: SELECT field1, field2 FROM my_table_1 UNION ALL SELECT field1, field2 FROM my_table_2 Notice that the fields must match in both tables in terms of data type, but they do not need to have the same name.


2

I believe you can do this through default top menu: Vector - Geometry Tools - Singleparts to Multipart... Though above the input there is written "Input line or polygon vector layer" I just tested it with point layer and it did work. Also once you create multipoint layer to edit - add / delete parts (point from multipoint) you need to use Advanced ...


2

Yes I guess it is possible (with spatialite at least). In spatialite for example you probably can do something like: CREATE TABLE yournewtable AS SELECT group_concat(foo), CastToMultiPoint(Gunion(GEOMETRY)) FROM your50000recordstable GROUP BY your700categories_column which should result in 700 multipoint features (1 per category), with GEOMETRY as your ...


2

In spatialite you should be able to do a cast. Assuming a table "numbers" with one TEXT column, "number_str" (that represents integers) and a second column "number_ints" that was defined as INTEGER, then UPDATE numbers SET number_ints=(CAST(number_str AS INTEGER)); More details on the sqlite concept of "affinity" and the CAST function are here Regarding ...


2

If shortest path calculation on the VirtualNetwork returns a set as shown in your question, then column geometry in the first row contains the geometry of the shortest path as line string. An expression like AsText(Geometry) returns the line string, that is a list of point coordinates, in human readable WKT format. LINESTRING(x0 y0, x1 y1, ..., xn yn) ...


1

SQLite is using a dynamic type system and what it means is best to study from the documentation http://www.sqlite.org/datatype3.html. Here is an example that you can try yourself. create table test (geometry text); insert into test (geometry) values (GeomFromText('POINT (1 1)')); insert into test (geometry) values ('bar'); SELECT geometry,typeof(geometry) ...


1

rasterlite2 brings with it the wmslite wms server that can operate on rasterlite databases. So you run wmslite on your rasterlite db and then load the WMS in QGis. This https://www.gaia-gis.it/fossil/librasterlite2/wiki?name=tutorials is the index page for rasterlite2 tutorials, and in Step 5 of ...


1

It was obvious and I didn't read well the RTFM. See this discussion on the Spatialite mailing list. The answer is: SELECT ST_GeomFromText('POINT(1 2)', 4326); or SELECT MakePoint(1, 2, 4326); To make result as a text, do: SELECT hex(MakePoint(1, 2, 4326));


1

First you need to load the spatialite extension. On linux: expr = "SELECT load_extension('libspatialite');" c.execute(expr) Now your insert should succeed. On Windows it;s similar, but with the full DLL name. Check this answer.


1

Got it - use Spatialite 4.3 I tried Select ExportKML() from GUI, but doesn't seem to work. However the command-line .dumpkml works fine. See .help in Spatialite 4.3 for syntax.


1

got it - never mind: To export an SQLite table (or part of a table) as CSV, simply set the "mode" to "csv" and then run a query to extract the desired rows of the table. sqlite> .header on sqlite> .mode csv sqlite> .once c:/work/dataout.csv sqlite> SELECT * FROM tab1; sqlite> .system c:/work/dataout.csv In the example above, the ".header ...


1

The problem is that your geometries have to match in terms of reference (the Spatial Reference System, which is identified by a number, the SRID). So if you want to look up a point in degrees (longitude, latitude), but the data is in a UTM zone, you need to use a transformation to get from something referenced to SRID of 4326 to SRID of 26914. In your ...


1

Have you tried using the Offline plugin for QGIS? This syncs your PostGIS database to SpatiaLite and then syncs it back. https://docs.qgis.org/2.2/en/docs/user_manual/plugins/plugins_offline_editing.html If you need something custom, then go Spatialite; QGIS can edit it and you can setup triggers, views, querys etc that you have in your PostGIS database ...



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