Tag Info

Hot answers tagged

6

And, after a bit more reading the ogr2ogr help I found the answer more easily than I expected. ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb -sql "SELECT * FROM table" -dialect spatialite -nln new_table EDIT: As suggested by user30184 in the comments a cleaner, simpler method is: ogr2ogr -f "PostgreSQL" PG:"dbname=db" spatialitedb ...


5

Yes. It does as it appears from looking at the source code for the Spatialite Data Provider. The QgsSpatiaLiteFeatureIterator class is the one that supplies the features to the map upon sending a rectangle extent. You can just search for 'spatialIndex' in that class to see they actually use the index if available.


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.


3

The value stored in a cell from a heatmap is often normalized by its area. In this case I would rather suggest an equal area projection so that you can easily aggregate to larger scale


3

Standard OSM tiles are in Spherical Mercator (SRID=3857) so it will probably be easiest to build your grid using the same projection. If you use SM, you might store the data at the highest zoom level OSM supports, or at the highest level zoom level you'll permit users to zoom into. If coverage is sparse, use a data structure along the lines of XIndex, ...


3

From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.


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


3

ptrv/gpx2spatialite does this remarkably well, saving timestamps for all points and deriving speed and length data for tracks. It also won't import duplicate tracks, so you can feed it a huge pile for GPX files and it will munge them appropriately. Update: usage examples, as requested: Initialize new database: gpx2spatialite_create_db db.sqlite Add a ...


3

Yes it can be done with QGis. Using the QSpatiaLite Plugin "Manage your SpatiaLite databases within QGis" After installing the plugin you can run it. You will receive a series of messages/errors stating that you do not have a spatialite db. it will step you through getting one installed (choose a location for the file) and finally converting it to ...


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


2

I get the same nasty effect if I load the data with Add vector layer using the ogr driver: <provider encoding="System">ogr</provider> Alternatively (and the preferred way) I can load the data with Add spatialite layer. The encoding line changes to: <provider encoding="System">spatialite</provider> which is wrong ...


2

Ok, found at least one working solution. After finding that on Linux everything is fine suspected the default encoding in Windows. So I opened the QGIS project file (*.qgs) in PSPad and found this in layer description: <provider encoding="System">ogr</provider> So probably QGIS uses CP1250 encoding as default in Windows while the database is ...


2

It is not the size of the database that makes it slow, it is that you select too much to render. Simplification can be a part of the solution but it will not be enough for making you happy. You can do lot of things once the data are in Spatialite. As you suggested, simplify "update lines set geometry=ST_Simplify(geometry,0.01);" Do the same but instead of ...


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

There is no direct equivalent, however you should be able to do something similar with a combination of RotateCoords() which rotates the geometry (in degrees, rather than radians), ShiftCoords() and ScaleCoords(). Here is an example, simplified from the test suite: SELECT AsText(RotateCoords(geom, 0)), AsText(RotateCoords(geom, 90.0)), ...


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

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

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

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

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

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

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



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