Hot answers tagged spatialite
14
EDIT
Oh, so many typos in one post must be some sort of record. Table names was messed up, I hope it is better now.
I also realized on the way home that something is wrong here. 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 ...
13
I've described the process of installing and spatially enabling an sqlite db here: SpatiaLite Quick Start. Basically, you need to get init_spatialite-2.3.sql and run it on your db.
You can then create point geometries using this function:
MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry
9
In terms of OGC Simple Feature SQL specifications, Spatialite is the only open source implementation on a single file basics. For this reason (and others!) it has major benefits compared to other flat vectorial formats like shapefile etc...
Being fully supported by GDAL as an "official" OGR driver [0], this is a warranty for future support of major GIS ...
9
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 ...
9
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 ...
9
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 ...
8
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 ...
7
Here are two non-Spatialite solutions.
For Rtree with Python, try this example with 13000 points -- should take a few seconds:
from random import randrange
from rtree import index
from math import sqrt
# Create a 3D index
p = index.Property()
p.dimension = 3
idx3d = index.Index(properties=p)
# Make and index random data
coords = []
for id in ...
7
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.
...
7
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. ...
6
I couldn't say for Android but I've been able to do it for iOS.
The thing you need to consider is that spatialite requires native GEOS and PROJ4 libraries to work properly (for spatial indexes), so those ones also need to be compiled.
The version of sqlite required by spatialite may also be different from the one that is distributed in the platform (this ...
6
I think the easiest is to never let the duplicate in. add a unique constraint on the geometry field. I don't know how that will work in spatiallite but in postgis the constrint would compare the bounding boxes which will dive the wanted effect in the case of points.
if it doesn't matter which one of the duplicates to remove you could build a query that ...
6
Auto-joining the table would allow you to find duplicates rows. Something like that should work :
DELETE t1
FROM mytable t1, mytable t2
WHERE t1.the_geom = t2.the_geom
if points :
DELETE t1
FROM mytable t1, mytable t2
WHERE t1.x = t2.x
AND t1.y > t2.y
(not tested .....)
5
Well, it really depends of your needs. I also think geojson, gml, citygml, and the google kml could also be considered as spatially exchange formats.
Could your request be more precise?
5
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.
5
Suppose your district table looks like this
districts(name text, the_geom geometry)
Then you would select the district a point falls into using
SELECT name
FROM districts
WHERE ST_Within(ST_SetSRID(ST_MakePoint(lon,lat),4326),
the_geom);
Replace lon and lat with your values.
If your districts are in a different projection than WGS84, ...
5
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');
...
5
Qspatialite is incompatible with spatialite 3 (see http://code.google.com/p/qspatialite/issues/detail?id=6) and AFAIK DB Manager is incompatible too. Use spatialite-gui (https://www.gaia-gis.it/fossil/spatialite_gui/index) to manage your database and ogr2ogr to add or export layers. Or install spatialite 2.x.
4
There is now a stable version of SpatiaLite for Android as announced by Sandro Furieri on the Google Group: https://groups.google.com/forum/?fromgroups=#!topic/spatialite-users/tnmc-sOK1PM
You may also want to check out Bill Dollin's blog post: http://blog.geomusings.com/2012/11/26/spatialite-for-android-available/
4
You can use the command line spatialite_tool and embed it in python with subprocess module.
In command line
spatialite_tool -e -shp shape_towns -d test-2.3.sqlite -t Towns -g Geometry -c CP1252 --type POINT
In Python
import subprocess
subprocess.call(["spatialite_tool", "-e", "-shp", "shape_towns", "-d", "test-2.3.sqlite", "-t", "Towns", "-g", ...
4
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 ...
4
In Grass, v.type.bl can be used to convert polygons to lines. You can follow it up by v.split to convert to line segments. The screens are from Qgis. Set max number of vertices to 2 in v.split as shown below.
Edit: The above method will not give you duplicates at d and f. If duplicate segments are required, you could probably replace the first ...
4
With a DB Manager (unless your spatialite version is 3) you are able to execute any query including INSERT, so yes (plus there is spatialite-gui-plugin, but again spatilite 3 is not supported). But I prefer to use for this spatialite-gui in parralel with QGIS. Changes are synchronised nicely (at least so far for me).
4
The coordinate format is not SpatiaLite specific - its WKT, and the meaning depends on the spatial reference system. In this case you're using EPSG:4326 which is WGS84 Longitude - Latitude. So the first coordinate is degrees of longitude and second is degrees of latitude, referenced from the WGS-84 origin.
The insert looks OK, but something is obviously ...
3
Have you tried something like [1]:
spatialite test.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'.
And the correct routing query seems to be [2]:
SELECT *
FROM Roads_net
WHERE NodeFrom = 1 AND NodeTo = 512;
[1] ...
3
I used the 2to3 tool (see geographika's answer) to convert Shapely-1.2.13 and then manually had to change 3 lines of code to handle Python 3.x's explicit handling of string and byte encoding. I have put this as a separate answer just for clarity and in case anybody else needs to do the same here are the lines I manually changed:
\geos.py (line 97)
old: v ...
3
Found the solution:
The tables are in FDO-Ogr format, you can see that when opening the db with spatialite-gui. Then you need to do:
SELECT AutoFDOStart();
and
SELECT link_id, SRID(GEOMETRY), AsText(GEOMETRY) FROM fdo_streets1
instead of
SELECT link_id, SRID(GEOMETRY), AsText(GEOMETRY) FROM streets1
C.f. here: ...
3
I don't think you need to bother with style files. If all of the project data is in a Spatialite database all you should need to do is send your colleague the database together with your QGIS project file (*.qgs). When your colleague opens the *.qgs file in QGIS the project should load, showing exactly the view that you saved, with the styles, labelling and ...
3
Probably the easiest way is with PostGIS. There are some tutorials on the internet how to import csv/txt point data into PostGIS. Link1
I am not sure about performance of point-in-polygon searches in PostGIS; it should be faster than ArcGIS. GIST spatial index that PostGIS uses is pretty fast. Link2 Link3
You could also test MongoDB geospatial index. But ...
Only top voted, non community-wiki answers of a minimum length are eligible
