SpatiaLite: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
(Updated the explanation how to export a grass vector layer to a spatialite database)
(added a brief section describing how to use gdal Rasterlite driver)
 
(One intermediate revision by one other user not shown)
Line 1: Line 1:
== Spatialite ==
== Spatialite ==


A {{Wikipedia|SQLite}} database with geospatial extensions, much list [[PostGIS]] is to {{Wikipedia|PostgreSQL}}. To export a layer to a Spatialite database with v.out.ogr, use the SQLite [[OGR]] driver and set the SPATIALITE data creation option to "yes".
A {{Wikipedia|SQLite}} database with geospatial extensions, much list [[PostGIS]] is to {{Wikipedia|PostgreSQL}}. To export a vector map layer to a Spatialite database with v.out.ogr, use the SQLite [[OGR]] driver and set the SPATIALITE data creation option to "yes". Raster can be exported to a Spatialite database with r.out.gdal, using the Rasterlite format driver.
 
==== Vector maps ====
==== New database ====
===== New database =====


   v.out.ogr in=mymap output=mydbase.sqlite format=SQLite \
   v.out.ogr in=mymap output=mydbase.sqlite format=SQLite \
     type=area dsco='SPATIALITE=yes' output_layer="mymap"
     type=area dsco='SPATIALITE=yes' output_layer="mymap"


==== Existing spatialite database ====
===== Existing spatialite database =====
Note that you need to use --overwrite, otherwise v.out.ogr will complain that the output already exists. Using --overwrite should maintain the existing tables in the spatialite database. However, it is strongly suggested to run this on a copy of the database, or make sure you have a recent backup.
Note that you need to use --overwrite, otherwise v.out.ogr will complain that the output already exists. Using --overwrite should maintain the existing tables in the spatialite database. However, it is strongly suggested to run this on a copy of the database, or make sure you have a recent backup.


Line 14: Line 14:
     type=area dsco='SPATIALITE=yes' output_layer="mymap" --overwrite
     type=area dsco='SPATIALITE=yes' output_layer="mymap" --overwrite


==== Using QGIS (1) ====  
====== Using QGIS (1) ======
An alternate way is to load the layer in QGIS's GRASS toolbox, then right click on the layer name and select "Save As...".
An alternate way is to load the layer in QGIS's GRASS toolbox, then right click on the layer name and select "Save As...".


==== Using QGIS (2) ====  
====== Using QGIS (2) ======  
Open the layer in QGIS using the browser panel. Next, open the spatialite database in the QGIS DB manager and use the 'import layer' option to import the grass vector layer you just opened in QGIS into the spatialite database.
Open the layer in QGIS using the browser panel. Next, open the spatialite database in the QGIS DB manager and use the 'import layer' option to import the grass vector layer you just opened in QGIS into the spatialite database.
==== Raster maps ====
Given an existing <var>mydbase.sqlite</var> file, and a Byte type rastermap <var>myraster</var>, invoking the following command will incorporate myraster to mydbase.sqlite as a LZW compressed PNG image :
  r.out.gdal input=myraster output="RASTERLITE:mydbase.sqlite,table=myraster" format=Rasterlite type=Byte createopt="DRIVER=PNG,COMPRESSION=LZW"
For more information on the Rasterlite driver, have a look a [http://www.gdal.org/frmt_rasterlite.html gdal documentation].
Note: the GDAL Rasterlite driver does not implement the SetProjection() method, thus srid value in the geometry_columns table will be set to -1 instead of a valid srid (12345 in the following example). You'll probably have to set it manually within the database:
spatialite mydbase.sqlite
.head on
select srid from geometry_columns where f_table_name='myraster_metadata';
update geometry_columns set srid=12345 where f_table_name='myraster_metadata';
.exit
[[Category:FAQ]]

Latest revision as of 16:55, 2 February 2017

Spatialite

A SQLite database with geospatial extensions, much list PostGIS is to PostgreSQL. To export a vector map layer to a Spatialite database with v.out.ogr, use the SQLite OGR driver and set the SPATIALITE data creation option to "yes". Raster can be exported to a Spatialite database with r.out.gdal, using the Rasterlite format driver.

Vector maps

New database
 v.out.ogr in=mymap output=mydbase.sqlite format=SQLite \
   type=area dsco='SPATIALITE=yes' output_layer="mymap"
Existing spatialite database

Note that you need to use --overwrite, otherwise v.out.ogr will complain that the output already exists. Using --overwrite should maintain the existing tables in the spatialite database. However, it is strongly suggested to run this on a copy of the database, or make sure you have a recent backup.

 v.out.ogr -u in=mymap output=mydbase.sqlite format=SQLite \
   type=area dsco='SPATIALITE=yes' output_layer="mymap" --overwrite
Using QGIS (1)

An alternate way is to load the layer in QGIS's GRASS toolbox, then right click on the layer name and select "Save As...".

Using QGIS (2)

Open the layer in QGIS using the browser panel. Next, open the spatialite database in the QGIS DB manager and use the 'import layer' option to import the grass vector layer you just opened in QGIS into the spatialite database.

Raster maps

Given an existing mydbase.sqlite file, and a Byte type rastermap myraster, invoking the following command will incorporate myraster to mydbase.sqlite as a LZW compressed PNG image :

 r.out.gdal input=myraster output="RASTERLITE:mydbase.sqlite,table=myraster" format=Rasterlite type=Byte createopt="DRIVER=PNG,COMPRESSION=LZW"

For more information on the Rasterlite driver, have a look a gdal documentation.

Note: the GDAL Rasterlite driver does not implement the SetProjection() method, thus srid value in the geometry_columns table will be set to -1 instead of a valid srid (12345 in the following example). You'll probably have to set it manually within the database:

spatialite mydbase.sqlite
.head on
select srid from geometry_columns where f_table_name='myraster_metadata';
update geometry_columns set srid=12345 where f_table_name='myraster_metadata';
.exit