38
A small comparison between GeoPackage and SpatiaLite in QGIS. Right to the moment it is not really productive to use GeoPackage with QGIS. May change pretty fast though (see addtions!).
Some additions regarding new QGIS 2.99 alias QGIS 3, Nov. 2017 (also added in comparison): GeoPackage now works nearly without any problems in QGIS 3. Did encounter some ...
23
I have reproduced your example with shapefiles.
You can use Shapely and Fiona to solve your problem.
1) Your problem (with a shapely Point):
2) starting with an arbitrary line (with an adequate length):
from shapely.geometry import Point, LineString
line = LineString([(point.x,point.y),(final_pt.x,final_pt.y)])
3) using shapely.affinity.rotate to ...
18
You could directly integrate your line into the database via a geometry column if you load spatialite as an extension of sqlite:
import sqlite3
connR = sqlite3.connect(':memory:')
connR.enable_load_extension(True)
#now we can load the extension
# depending on your OS and sqlite/spatialite version you might need to add
# '.so' (Linux) or '.dll' (...
16
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.
16
You're looking for the "Dimensionally Extended 9 Intersection Matrix" or DE-9IM for short.
DE-9IM by FME
That FME link has great examples of the spatial operators you listed above. It breaks it down into a 3x3 true/false matrix with examples and descriptions of each predicate attribute.
16
The issue is with the way I am using Spatialite.
After talking with the company behind the web service that provides these datasets (since I initially assumed it may have been an export bug), they revealed to me that there is a distinction between Spatialite geometries and GeoPackage geometries.
To work with GeoPackages in Spatialite, there are several ...
15
Spatialite could be interesting because it is fast for local use. You can load it directly into the RAM of your machine and it is only one file, so it is really portable and shareable. You can create R-tree indexes on geometry.
Just like PostGIS, Spatialite follows OGC standards and much of its functions are similar to PostGIS's and usable in SQL. It is ...
15
If the only reason you're contemplating adopting a new system is "'cause there's a shiny new system", don't do it. Until and unless you actually run into problems with the limitations of shapefiles, or want to take advantage of some specific features of postgis/spatialite/whatever, there's no need to change. Keep designing and producing maps.
Sooner or ...
14
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. ...
13
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 ...
12
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 ...
12
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 ...
11
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.
11
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 * FROM ...
11
How about a local PostGIS instance? PostgeSQL can be downloaded from https://www.postgresql.org/download/ to which a PostGIS extension can be created to make it a spatial database. Also recommended would be to download pgAdmin https://www.pgadmin.org/
See How do I setup a PostGIS database and open it in QGIS on Windows? which may help with the set up.
10
Add first a geometry column into your table with SQL
SELECT AddGeometryColumn('your_table','geometry',4326,'POINT',2);
Then populate the new geometry column by constructing point geometries from your Latitude and Longitude fields with SQL
UPDATE your_table SET geometry = GeomFromText('POINT('||"Longitude"||' '||"Latitude"||')',4326);
10
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 ...
10
I have no technical background on how Geopackage works internally, so after searching for information on the simultaneous editing capabilities of Geopackage and barely finding anything conclusive, I decided to try it myself. I loaded the same Geopackage layers in two different Qgis (3.8.2) projects, and this is what I found out.
If the layer you want to ...
9
The sqlite file from NE is in FDO-OGR format, not the native spatialite geometry.
If you're willing to do some manual labor, here's a way to convert to a spatialite db:
First make a new, empty spatialite database (I call it "nev.sqlite"), then in a separate terminal session open the original natural_earth_vector.sqlite with spatialite. (I used the newer ...
9
One nice way of doing this is using the new LATERAL JOIN syntax in Postgres in conjunction with the <-> operator.
A lateral join essentially runs the right hand query for each row in the left hand one, and can avoid some quite nasty array logic and sub-queries, which you would otherwise need to use to find k nearest neighbours.
CREATE TABLE sometable AS
...
9
The following commands can be used to add a Spatialite view to non-spatial tables in a SQLite database.
Add Spatialite capabilities to the database.
SELECT load_extension('mod_spatialite');
SELECT initspatialmetadata(1);
It is necessary to have Spatialite installed on the system where these commands are run (sudo apt install libsqlite3-mod-spatialite ...
8
The reference work I use for expectations of spatial operators is the Clementini paper ("A Small Set of Formal Topological Relationships Suitable for End-User Interaction", Eliseo Clementini, Paolino Di Felice, and Peter van Oosterom, 1993). It lays out the theory behind the operators with respect to interiors, exteriors, and dimensionality, which eliminates ...
8
You could, of course, do this with conversion via Well Known Text:
spatialite> SELECT AsText(GeomFromText("LINESTRING(1 2, 3 4, 1 3)"));
AsText(GeomFromText("LINESTRING(1 2, 3 4, 1 3)"))
LINESTRING(1 2, 3 4, 1 3)
However it is definitely possible to use MakeLine() with more than two points.
Apart from the obvious version that you know about already, ...
8
From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.
8
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 ...
8
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 ...
8
Unfortunately fedora does not ship a pyspatialite package in the repositories.
You can install it using pip but since pyspatialite is more than just some python files and contains compiled parts you will also need some dependencies installed before.
sudo dnf install gcc python-devel sqlite-devel geos-devel proj-devel python-pip redhat-rpm-config
CFLAGS=-I/...
8
Banks are tagged as amenity=bank so you need to look for the amenity key.
This tag can appear on nodes and ways. This means that you will not only get buildings but also individual POIs. Keep in mind that in OSM properties don't have to be added to buildings, adding a single POI node inside a building is perfectly valid. In fact this approach is necessary ...
8
SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic detection like with PostgreSQLs type-modifier system.
Having created a new table as you specified you should be able to 'spatially enable' it with
SELECT ...
8
You can use SpatiaLite tables as if they were PostGIS tables but without importing them physically by using the OGR Foreign Data Wrapper https://github.com/pramsey/pgsql-ogr-fdw.
Once the virtual FDW table is created you can query the SpatiaLite database with a PostgreSQL client. Also ogrinfo and ogr2ogr can automatically see the FDW tables.
A complete ...
Only top voted, non community-wiki answers of a minimum length are eligible