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

Go to Processing -> Toolbox -> QGIS geoalgorithms -> Vector general tools -> Join attributes by location (if you don't see this algorithm, you might need to activate the "advanced interface" option at the bottom of the Processing Toolbox dock widget). This way you can save your results to several formats, including .sqlite.


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


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

Okay, got it meanwhile. There are probably different ways to do this but this one works fine. Apart from access to your Spatialite database via Python's sqlite3 module and the Spatialite extension, you'll need the geojson module (simply install with pip install geojson). For the sake of completeness, let's create a new Spatialite database first and fill it ...


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

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


2

It is not possible to directly delete a column in Sqlite and thus in SpatiaLite: http://www.sqlite.org/faq.html#q11. Therefore QGIS cannot offer this functionality. The alternative is to recreate the table. BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 ...


2

If they are SpatiaLite layers, they are already in a database. If you meant that they are in different databases (which you did not stated in your original question), you can use the DB Manager plugin, just expand your SpatiaLite connections in the Tree and drag & drop the SpatiaLite layers you want to import from each DB to your target DB.


2

If you're after a GUI solution, you can use the "Add Part" tool (QGIS >=2.2 only). First, make your layer editable. Then in an Attribute Table select a single row with a null geometry that you want to add a geometry to. Back in the map canvas, select the "Add Part" tool and draw your geometry. The null geometry will be replaced by your newly drawn shape.


2

You can use Foreign Data Wrappers, as of Postgres 9.2 (I think), from within Postgres to register a connection to another data source, either a Postgres server on another machine, or a completely separate data source. CREATE EXTENSION postgres_fdw; CREATE SERVER foreign_spatial_lite FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'some_dbname', ...


2

Your database is not really a SpatiaLite database but a SQLite database which contains geometries which are encoded according to FDO specification. Something about FDO can be read from http://trac.osgeo.org/fdo/wiki/FDORfc16. Some other software, like TatukGIS, are also creating SQLite databases with FDO geometries. Spatialite can read FDO geometries ...


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

Accordingly to Spatialite coockbook you must register your VIEW into the views_geometry_columns, so to make it become a real Spatial View, i.e.: INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column) VALUES ('buffer500m', 'geometry', 'ROWID', 'point', 'geom');


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

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

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

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

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

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


1

H2GIS may fit with your needs as it does not require any installation (except Java) and is really close to PostGIS (SFSQL compatible). In a nutshell: H2GIS is to H2 as PostGIS is to PostgreSQL. H2GIS adds support for (MULTI)POINT, (MULTI)LINESTRING and (MULTI)POLYGON types, the functions specified by the OpenGIS Simple Features Implementation Specification ...


1

As you said, you could use embedded instance of spatiallite. In that case, it will starts together with your application, and keep all the data as one file, somewhere with your app resources. You could try JavaTopologySuit (or it's analogue for other platform). There are no such things like SQL queries, but it could create Spatial indexes, quickly select ...


1

In a spatial database like spatialite this is a straightforward query. Suppose you have the polygons as parcels and the point layer is zoological (and both are spatial tables in spatialite). I'll assume the parcels has a unique id called parcel_id and the species has species_id as well as species_name and entity. The query would be something like: SELECT ...


1

The error message gives hints about what can be wrong: IntegrityError: tc_2010_00567.the_geom violates Geometry constraint [geom-type or SRID not allowed] Error messages may be misleading and the real issue may be somewhere else but it is worth checking the suggested reasons for the error first. In your case the input dataset is in EPSG:4618 and so ...


1

QGIS print composer in > 2.6 can create georeferenced output as described in https://docs.qgis.org/2.6/en/docs/user_manual/print_composer/print_composer.html#creating-output If you need to export your layout as a georeferenced image (i.e., to load back inside QGIS), you need to enable this feature under the Composition tab. Check [checkbox] World file on ...


1

You could add row numbers to the points and then join points and non-spatial table based on row number and ogc_fid (assuming ogc_fid starts with 1).


1

Okay, meanwhile I found out how this can be achieved. I don't know if this is the most efficient way but for my tables which are not huge it works fast enough. The idea is that geopandas stores geometries as shapely geometry objects in each row of the geometry column of a GeoDataFrame (which is in fact just a GeoSeries) so that most of shapely's methods can ...



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