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


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

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

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

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

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

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

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


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



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