Tag Info

Hot answers tagged

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

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


3

Every spatial table in a SpatiaLite database has a foreign key constraint so that the SRID has to match the value stored in a metadata table named geometry_columns. You can see the constraint with: SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = 'ggu_mytable_geom'; You can see the actual SRID enforced for the table with: SELECT * ...


3

SQLite operates in a last edit wins model, which leads to issues with more weight than write blocks. There is no editing session tracking in the QGIS/SQLite relationship. So, even if you did get a single writer block (SQLITE_BUSY) QGIS will simply fail to edit and just return an error then submit the change again. The below discusses the handling of this ...


2

This is an answer to how you could design a heatmap. My suggestion is you look into the Quarter Degree Grid Cell system. QDGC represents a way of making (almost) equal area squares covering a specific area to represent specific qualities of the area covered. The squares themselves are based on the degree squares covering earth. Around the equator we have 360 ...


2

If you have to use an older version of spatialite, here is the SQL equivalent for the postgis ST_Azimuth(A,B): case when X(A) == X(B) then case when Y(A) Y(B) then 3.141592653589793 else 0 end when Y(A) == Y(B) then case when X(A) X(B) then 3.141592653589793 + 3.141592653589793/2 else 0 end ...


2

This may not apply to your situation as it may depend on the attributes of the points but I have a point layer where I use their unique ID attribute and created 10 classes of equal count (quantile): You can create unique attributes if you don't already have one by using the Field Calculator and adding $rownum in the expression. Hope this helps.


2

Use either ST_Distance or PtDistWithin (see documentation). With the former you can test for the distance of your points and then select based on some distance criteria. If you are wanting to test whether a point lies exactly on your line you could try doing an Intersection or use ST_Contains. My only hesitation with these approaches is that, because a ...


2

give the qspatialite plugin a try (https://plugins.qgis.org/plugins/QspatiaLite/) (Create empty db, now you can select all your layers and add at one go. see screenshot 1)


2

This reminds the old quiz: 5 person will shake hands with each other. How many handshakes will there be in total? Is it 20 because everybody must shake hands with 4 other person? No, because if Mike shakes hands with John, John does not need to shake hands with Mike for another time. Let's assume that you have 5 points. Point number 1 does not shake hands ...


2

You are running against SQLite version 3.7.17, but Common Table Expressions (WITH clause) were not supported until 3.8.3. See change log here: http://www.sqlite.org/changes.html Unless you are trying to do a recursive query, you can either write your WITH clause as a view or use a subquery.


2

There is a function to reproject geometry: ST_Transform( geom Geometry , newSRID Integer ) : Geometry List of functions can be found here. Note that in case you would want to rewrite existing geometry in Geometry column with reprojected data (instead of creating new table), you will need to update geometry_columns table and enter new SRID (in srid ...


2

I do not believe that the AddGeometryColumn part is possible at all. This thread tells that it is not possible to use it for attached databases https://groups.google.com/forum/#!topic/spatialite-users/rRMSHk2Lt38 and the temporary database is an attached, automatically created database. You must create your temporary tables into the main database and drop ...


2

rasterlite_load does have logic to handle imagery other than RGB, but it may not deal with your situation. From the code, the supported combinations are: bits_per_sample == 1 && samples_per_pixel == 1, interpreted as a CCITT 4 fax. bits_per_sample == 8 && samples_per_pixel == 1 && photometric == 3, interpreted as paletted image ...


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

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

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


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

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

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

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

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


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


1

Sandro Furieri, the author of Spatialite, explains in this post how to initialize Spatialite correctly: So the "new style" initialization is as follows: invoke first of all spatialite_alloc_connection() this will create an independent "connection object" containing any thread specific memory allocation. (this including XML and GEOS ...


1

Use the QGIS plugin QSpatiaLite controlling your Spatialite db, for storing CSV files. With this plugin you can create a new database, upload, query and edit data.


1

You can try MapServer with GDAL. Here you can find more info on RasterLite driver in GDAL.


1

The GDAL osm driver makes use of an osmconf.ini file: http://www.gdal.org/drv_osm.html Within that file, you can uncomment the line #other_tags=no to avoid saving all possible names of Sweden in that database column. By the way, GDAL can read the osm.pbf file directly, no need to extract it first.


1

Try to use RecoverGeometryColumn. The syntax is: SELECT RecoverGeometryColumn('test_geom', 'the_geom', 4326, 'POINT', 'XY'); For more info see http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/new-geom.html


1

You could do some processing before with osmconvert (http://wiki.openstreetmap.org/wiki/Osmconvert) or osmosis. Basically you cut the area you're interested (clip based on bbox) in from the two extracts, then merge the two resulting files to have a single file. Then you can convert this file in spatialite, probably faster than doing on the complete file! ...



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