32
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 ...
27
Shapefiles are the lowest common denominator of GIS vector data file exchange: send an archive of shapefiles, and you can pretty much guarantee that someone will be able to build a basic GIS from it.
SpatiaLite's advantages include:
everything's in one file; none of the shp/shx/dbf/idx/prj per layer mess.
logic as well as data can be included, in the form ...
26
ST_DWithin was faster in my test than ST_Intersects. That is surprising, especially since the prepared geometry algorithm is supposed to kick in on cases like this. I think there is a chance that this will be quite a lot faster than I showed here.
I did some more tests and two things almost 10-doubled the speed.
First, I tried on a newer computer, but ...
22
Shapefiles are bound to one type of geometry, so you get a bunch of files for a single project. The field names are restricted too due to using an antiquarian database format.
In spatialite, you can hold the whole project data in one file; and name the fields how you want (well, almost).
The only disadvantage of spatialite is the fast update cycle, making ...
19
Several options!
Within QGIS, you can "File|Save as" the shapefile directly as a
spatialite database; the limitation here is that you can make a
database with only one shapefile.
You can use the command line spatialite_tool to load and manage shapefiles (tutorial here).
It is really worth exploring the spatialite-gui
and spatialite-gis standalone ...
17
Found this in the FDO data concepts page:
A geometry is represented using geometric constructs either defined as
lists of one or more XY or XYZ points or defined parametrically, for
example, as a circular arc. While geometry typically is two- or
three-dimensional, it may also contain the measurement dimension
(M) to provide the basis for dynamic ...
17
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 ...
16
No, SpatiaLite isn't that slow, you just need to use a spatial index. Due to limitations in the SQLite design, using a spatial index in a query isn't as invisible as it is in PostGIS.
Here is an example modified from the SpatiaLite Cookbook http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/neighbours.html
After creating a spatial ...
15
In addition to scruss' answer, shapefiles has some limitations:
max field name length is 10 characters
maximum file size (.dbf / .shp) is 2GB
numeric attributes are stored as characters (integers/floats), causing potential problems with rounding etc
NULL values are interpreted differently between systems
15
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.
14
I started an open source GDAL/OGR ArcGIS plugin project this weekend that gives read support to spatialite and any other OGR vector data source like Google Fusion Tables.
I have it working locally reading spatialite and will cleanup and push the rest of changes this coming Friday. I hope you find it useful.
Update 1:
OK, got it working today. The ...
14
I think you have to register the view in geometry_columns table to be able to use it in QGIS.
A good resource is "Hand-writing your own Spatial VIEW" with the following example:
INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('italy', 'geometry', 'ROWID', 'local_councils', 'geometry');
...
13
SpatialLite has no way of converting multi-geometries to single-parts itself. There are some 'CastTo' functions but they are for special cases (where your multigeometry contains a single geometry - it won't fan-out). I have seen a reference to a function in the SpatialLite GUI but never found it (perhaps you need to compile from the latest source code. I'...
13
You can connect to Spatialite via Python using the latest version of pysqlite instead of pyspatialite. Spatialite is just the spatial enablement of SQLite so this works, but if you are connecting in this way (via pysqlite) you need to load the libspatialite extension to be able to use the spatial functionality of Spatialite.
from pysqlite2 import dbapi as ...
12
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 ...
12
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. ...
11
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 ...
11
In the documentation, it is alluded to that you cannot edit data in a SQLite database from ArcMap:
You can connect from ArcGIS to an SQLite database to create maps and perform spatial analysis on your data.
However, the only place this appears to be explicitly stated by Esri is in the ArcGIS Discussion Forum:
Yes; you cannot edit data in a SQLite ...
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
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
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 ...
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
You can right-click the layer and select "save layer as..." and choose spatiaLite as your format. Your only option there is to create a new database, i.e. you cannot add it to an existing database.
The QspatiaLite plugin adds a lot of functionality to QGIS, so give that a try as well.
10
This is a problem with the spatial index, not the data itself. You can check this by opening the file in spatialite-gui (available from https://www.gaia-gis.it/fossil/spatialite_gui/home) and right click on the geometry layer then select Check Spatial Index. It should say it is malformed / corrupted. You can then remove the index by right clicking again.
...
10
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.
10
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 ...
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
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' (...
9
I don't know if this is the answer you want, as it's not a point and click answer, but this is how I would do it probably.
In Spatialite, add a new geometry column. Assuming you want WGS84 (lat/lon)
AddGeometryColumn( yourTableName , geometryColumnName, 4326, 'POINT', 'XY')
Then create the geometry from WKT generated from the X/Y coordinates
update ...
9
After asking in several different forums and mailing list, I found my answer in the Spatialite dedicated google group, as posted by @BradHards.
In fact St_Union is not working as expected as sandro furieri have tested and explained here.
And this is the beauty of Open source, the issue is already taken care of, and will be available in Spatialite 4.0.0.
...
Only top voted, non community-wiki answers of a minimum length are eligible
