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:

• real SQL statements and SQL functions are always executed internally to the SQL engine itself.
  This practically means that any SQL statement or SQL function could indifferently be executed from any possible sw component supporting a SQLite connection with exactly identical effects; and the specific language used to develop the calling application is completely irrelevant.
  All the logic supporting execution of SQL statements and SQL functions will always be internally implemented in libsqlite3 itself (or may be in libspatialite in the case of Spatial Functions): the calling application plays absolutely no direct role into the execution process of SQL statements, except for initially starting the execution request.
• on the other hand dot macros will always be directly executed by the spatialite CLI front-end.
  The intended scope of the mandatory dot character being placed in the first position of a self confined line is exactly the one to act as a special marker intending that the whole line has to be directly filtered out by the front-end tool, then carefully avoiding to pass it to libsqlite3 for normal SQL execution.
• short conclusion: ordinary SQL statements and SQL functions are intended to be parsed and executed by the SQL engine (libsqlite3). Dot macros are instead completely outside the ordinary SQL execution path, and will be immediately processed by the front-end tool itself.
  Any attempt to execute some dot macro in the libsqlite3 SQL context will simply raise an invalid syntax exception.

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

---