Hot answers tagged

15

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


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


9

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


8

You have two tasks: (a) find the grid data for the vertices of a grid cell in which a GPS location (x,y) lies and (b) interpolate those data. An efficient way to accomplish (a) is to store the grid as an array with a single index. Often grids are stored by rows with data progressing left to right within each row. A grid is determined by the coordinates of ...


7

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


7

For sake of posterity, the short answer is that MapInfo and FDO (AutoDesk technology) are far removed from any development with SQLite, compared to the members of the SQLite Consortium. MapInfo and FDO (AutoDesk) would be contributing binary to their downstream users, via their products and services. Spatialite is specked under a triumvirate of licenses-...


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

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


6

Ryan, You should be able to with the SQLite dialect: $ ogrinfo -ro -geom=no -where "TZID='America/Boise' OR TZID='America/Denver'" tz_world.shp tz_world INFO: Open of `tz_world.shp' using driver `ESRI Shapefile' successful. Layer name: tz_world Metadata: DBF_DATE_LAST_UPDATE=2012-06-25 Geometry: Polygon Feature Count: 3 Extent: (-179.999900, -89.999900) ...


5

ogr2ogr? See here for more information and maybe here as well Cheers St├ęphane


5

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.


4

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] http://blog.mikeasoft.com/2010/09/24/local-map-rendering-and-route-finding-with-libchamplain-spatialite-and-...


4

You can use GeoDjango on a shared server at http://www.alwaysdata.com (just check the features list and the prices: http://www.alwaysdata.com/plans/shared ) They have VERY competitive prices and you can even test the env for free. They do the PostGIS configuration on their side and the installation of geoDjango so that you don't have to configure ...


4

You can install the Open Source ArcGIS OGR Workspace plugin from AmigoCloud that will give ArcMap read access to spatialite and a whole bunch of other formats. It is free. Let me know if you want to test the binaries - I already have some people doing that with success. They only work with 10.1 at the moment since that is the only ArcGIS version I have ...


4

I found the solution as for source table did not created with spatialite option which default is false. To do that simply add parameter to ogr2ogr utility -dsco "SPATIALITE=YES" then all SpatialLite parameters shall work as requested.


4

Here's something you might try: You can create buffers around all the points at a "reasonable" distance that you choose based on the clustering. Then merge the circular buffers together. That should give you polygons enclosing the clusters of points. In spatialite you would do: Create a polygon table for the buffers and a second one for the merged circles: ...


4

This seems to be a bug (see http://hub.qgis.org/issues/9628 - apparently fixed in 2.2.) Possible workaround: I have experienced that when using fields from joined tables (I have tested dbf and csv), the field calculator messes things up when I choose "Create new field". If I change to "Update existing field", things seem to work.


4

In native (out of the box) Python installations extensions are disabled within sqlite3. Unfortunately this means that you will need to build the base pysqlite2 library manually and use that instead. On Windows this will mean that you need a C compiler (Visual Studio comes with one - I've used Visual Studio 2010 for this before). However (with massive props) ...


4

QGIS has its own internal handling of layers and features. Different data "providers" are used to pull layers in from a variety of sources (eg PostGIS, OGR file types, WFS servers, etc) and translate them into QGIS' own internal formats. Once you have a project fully loaded, including a .shp file plus a .dbf, plus another CSV joined, are all the data in ...


3

Have you had a look at OpenLayer's "Dynamic POIs via a Text Layer" example? That should be even easier than an SQLite solution. Update: Geodjango doesn't seem to be an option on shared hosting. See GoDaddy Forum: You can install and use Python and FastCGI on our Deluxe and Premium Linux shared hosting accounts. However, Django will not function on ...


3

Also, in order to use spatialite style queries, you must specify -dialect sqlite in ogr2ogr and ogrinfo sql


3

I get the same picture, but there is nothing wrong with that. Spatialite stores REAL values as floating point, hence no precision is needed. The width and precision fields are necessary for shapefiles, which store REAL data in a database of an old dbase format which needs width and precision to be set explicitely. It seems that the pictures in the exercise ...


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

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

From the ogr2ogr docs, use the -nln name option to assign an alternate name to the new layer.


3

This should work: ##Database=group ##Access.mdb to SqLite=name ##inputmdb= input file ##outputsqlite = output file import sys, subprocess, os, sqlite3 as sql mdb_name = inputmdb sql_name = outputsqlite try: print "\nopening db" conn = sql.connect(sql_name) curs = conn.cursor() print "\ncreating schema" cmds = subprocess.Popen(["mdb-...


3

The "read_only" table obviously needs some input, as I could not find anything in the cookbook, after some search I found this insightful discussion with mr. furieri himself: https://groups.google.com/forum/#!topic/spatialite-users/n8l977RL9-0 conclusion: 1 = TRUE = ReadOnly View (unsupported write ops) 0 = FALSE = Not ReadOnly, thus Writable View So ...


3

Concerning your first request (concatenate the list of unique lot id's which are bound within a radius of 30m from the given lot), I guess you can do that with a SQL query in QGIS : SELECT b.id as ID_ref, group_concat(a.id) AS ID_within_30m FROM city_lots a, city_lots b WHERE b.id = 1 AND b.id != a.id AND ST_Distance(a.GEOM, b.GEOM) < 30; ...


3

The GDAL SQL dialect is a subset of select SQL (source). It looks like left isn't part of those functions implemented. Neither is it implemented in the sqlite core functions. Instead try the sqlite substr(X, Y, Z) function (credit to this SO answer). Your query would become: select FID, substr(FLOORKEY, 0, 8) AS BLDGKEY,* from b And the dialect should ...



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