Tag Info

Hot answers tagged

19

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


15

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


15

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


14

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


12

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


11

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


11

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

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

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

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


8

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


7

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


7

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'); ...


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

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


7

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


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


6

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


6

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.


6

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


6

In SQLite, and thus also in SpatiaLite, there's no date type per se. See Datatypes In SQLite. When a string column is saved in the format "YYYY-MM-DD" then you can apply some date functions to it, such as strftime() to get the date formatted in other ways. However, if you've saved your dates in any other way, they are NOT recognized as dates. What I would ...


6

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


6

That should not happen (4236 uses degrees) . AsText() returns WKT geometry always in current srid, so you probably use wrong srid. You can set correct srid using UPDATE table SET geom = SetSrid(geom, 3857) assuming that your data really is in google mercerator. If you need to transform geometry then you can use UPDATE table SET geom = Transform(geom, 4326) ...


6

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.


5

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


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

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

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.



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