dot macros
Not logged in

back


Introduction

The so called dot macros supported by both sqlite3 and spatialite CLI front-ends aren't really genuine SQL statements, and shouldn't be confused with SQL functions because they respectively corresponds to completely different execution contexts:
Dot macros are frequently found freely intermixed between ordinary SQL statements in many SQL scripts intended to support batch data processing activities.
A full list of all supported dot macro commands will always be shown by simply executing the .help macro in spatialite CLI; anyway presenting a short comprehensive summary about all dot macro commands supported by SpatiaLite surely is an useful resource for the many users usually developing complex SQL scripts.

dot macro commands: reference list

Please note: only SpatiaLite specific dot macros will be reported; please consult the appropriate documentation for any other dot macro directly supported by sqlite3.
.shell CMD ARGS...     Run CMD ARGS... in a system shell

.system CMD ARGS...    Run CMD ARGS... in a system shell
Both .shell and .system are alias names identifying the same action, i.e. executing an external shell command possibly passing an arbitrary number of invocation arguments.
Hints: useful in order to copy, delete or rename files directly from within the SQL script.
Opens new unexpected perspectives significantly extending the power of SQL scripting if wisely used in conjunction with some GDAL or GRASS command.
.chkdupl <table>  Check a TABLE for duplicated rows

.remdupl <table>  Removes any duplicated row from a TABLE
respectively checks or removes duplicate rows eventually found within the same Table.
Two or more rows are considered to be duplicate when they contain exactly the same identical values, excluding any Primary Key.
.elemgeo <args>   derives a new table from the original one, so to ensure that
                  only elementary Geometries (one for each row) will be present
                  arg_list: in_tbl geom out_tbl out_pk out_old_id
useful in order to resolve complex Geometries into many distinct rows (more or less equivalent to PostGIS's own ST_Dump).
.loadshp <args>   Loads a SHAPEFILE into a SpatiaLite table
                  arg_list: shp_path table_name charset [SRID] [column_name]
                      [pk_column] [geom_type] [2d | 3d] [compressed]
                      [with_spatial_index] [text_dates]
                      geom_type={ AUTO | LINESTRING[ Z | M | ZM ]
                                 | MULTILINESTRING[ Z | M | ZM ]
                                 | POLYGON[ Z | M | ZM ]
                                 | MULTIPOLYGON[ Z | M | ZM ] }

.dumpshp <args>   Dumps a SpatiaLite table into a SHAPEFILE
                  arg_list: table_name column_name shp_path charset [geom_type]
                      geom_type={ POINT | LINESTRING | POLYGON | MULTIPOINT }
allowing to directly import or export Shapefiles.
.loaddbf <args>   Loads a DBF into a SpatiaLite table
                  arg_list: dbf_path table_name charset [pk_column] [text_dates]


.dumpdbf <args>   Dumps a SpatiaLite table into a DBF
                  arg_list: table_name dbf_path charset
allowing to directly import or export DBF tables.
.loadxl <args>    Loads a XL spreadsheet (.xls) into a SpatiaLite table
                  arg_list: xl_path table_name
                      [worksheet_index [first_line_titles{0/1}]]
allowing to directly import data from a Microsoft Excel spreadsheet (.xls binary format).
.dumpkml <args>   Dumps a SpatiaLite table as a KML file
                  arg_list: table_name geom_column kml_path
                      [precision] [name_column] [desc_column]

.dumpgeojson <args>  Dumps a SpatiaLite table as a GeoJSON file
                  arg_list: table_name geom_column geojson_path
                      [format] [precision]
                  format={ none | MBR | withShortCRS | MBRwithShortCRS
                           | withLongCRS | MBRwithLongCRS }
allowing to export data respectively in the KML or geoJSON formats.
.checkgeom <args> Checks a Geometry Column for validity
                  arg_list: table_name geom_column report_path
                  or (all vectors): output_dir

.sanegeom <args>  Sanitizes a Geometry Column
                  arg_list: table_name geom_column tmp_table report_path
                  or (all vectors): tmp_prefix output_dir
obsolete and deprecated: use if possible the standard SQL functions ST_IsValid and ST_MakeValid as a better, more robust and more powerful alternative.
.read <args>      Execute an SQL script
                  arg_list: script_path charset
useful e.g. in order to execute another SQL script from within the current SQL script.
.sqllog ON|OFF    Turn SQL Log on or off
switching on and off the SQL Log facility.
.dropgeo <table>  Drops a Spatial Table (or Spatial View) completely
                  removing any related stuff (metadata definitions,
                  Spatial Index and alike)
useful in order to safely and completely removing a Geometry Table.
.loadwfs <args>   Loads data from some WFS source into a SpatiaLite table
                  arg_list: WFS_path_or_URL layer_name table_name [pk_column]
                      [swap] [page_size] [with_spatial_index]
allows to directly import data from a WFS datasource.
.loaddxf <args>   Loads data from some DXF source into SpatiaLite tables
                  arg_list: DXF_path [srid] [append] [dims] [mode]
                      [rings] [table_prefix] [layer_name]
                  append={Y|N} dims={AUTO|2D|3D} mode={DISTINCT|MIXED}
                  rings={NONE|LINKED|UNLINKED}
allows to directly import data form a DXF file.
Please note: the reciprocal operation (i.e. exporting data to DXf) is directly supported by the ExportDXF SQL function.

back