Home

Welcome to the Spatialite-Tasks-with-Sql-Scripts wiki!

• Goals:
  • Expand on the Sql-Syntax used with Spatialite
    • specially with the 0-base and 1-based parameters
  • Completion of specific Tasks using a combination of different commands
  • Use of Sql-Scripts build Databases with Geometries
  • Creation of Spatial-Views

Most of these sample are based on experiences during an ongoing project

• therefore will be updated often

---

• Commands:
  • 'ST_AddPoint'
  • 'ST_Equals,ST_IsClosed,ST_IsRing,ST_IsSimple'
  • 'ST_CollectionExtract'
  • 'ST_Collect and ST_LineMerge'
  • 'ST_Intersects,ST_Intersection''
  • 'ST_Line_Substring'
  • 'ST_NumGeometries'
  • 'MakeLine (without 'ST_')'
  • 'ST_SetPoint'
  • 'ST_SplitLeft and ST_SplitRight'
  • 'CreateSpatialIndex'

---

• Themes:
  • 'cause(es) of a ST_Collect(LINESTRING) not creating a valid POLYGON?'
  • 'difference in the usage of ST_Collect(LINESTRING) and ST_LineMerge(ST_Collect(LINESTRING))'
  • 'using ST_LineMerge and ST_Intersection to rebuild 2 LINESTRINGs'
  • 'using a :memory: db in sql scripts'
  • 'using ST_Line_Substring to retrieve center of a LINESTRING'
    • with a sample for MULTILINESTRINGs
  • 'using VirtualText to import csv files'
    • with a sample for MULTILINESTRINGs
• SpatialView Themes:
  • 'Index Page for SpatialView'
• RasterLite2 Themes:
  • 'Index Page for RasterLite2'
• GeoPackage Themes (R10):
  • 'Index Page for GeoPackage'
• Spatialite Network Tools:
  • 'Index Page for Network Tools'
• Spatialite Importing Shapefiles:
  • 'Index Page for Importing Shapefiles'

---

• Database Designing Themes:
  • 'Index Page for Database Designing Issues'

---

• geopaparazzi-specific:

  • 'Changes in geopaparazzi spatialite support '
  • 'Sql used to retrieve Spatialite Geometries'
    • the sql shown can be used to test the validity of the Database that are to be used with geopaparazzi
      • these can be used in spatialite_gui
    • 'Description of the record returned'
      • for spatialite 4.0 with a valid vector_layers_statistics view
        • VECTOR_LAYERS_QUERY_EXTENT_VALID_V4
        • VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4
        • VECTOR_LAYERS_QUERY_EXTENT_LIST_V4
      • for spatialite 4.0 with non-working vector_layers_statistics
        • LAYERS_QUERY_EXTENT_VALID_V4
        • LAYERS_QUERY_EXTENT_INVALID_V4
        • LAYERS_QUERY_EXTENT_LIST_V4
      • LAYERS_QUERY_EXTENT_VALID_V3
        • tables
          • LAYERS_QUERY_EXTENT_VALID_V3
          • LAYERS_QUERY_EXTENT_INVALID_V3
          • LAYERS_QUERY_EXTENT_LIST_V3
        • views
          • VIEWS_QUERY_EXTENT_VALID_V3
          • VIEWS_QUERY_EXTENT_INVALID_V3
          • VIEWS_QUERY_EXTENT_LIST_V3
      • for spatialite 4.2 with a valid raster_coverages table
        • RASTER_COVERAGES_QUERY_EXTENT_VALID_V42
        • RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42
        • RASTER_COVERAGES_QUERY_EXTENT_LIST_V42

• for GeoPackage R10 with a valid geopackage_contents table
  • GEOPACKAGE_QUERY_EXTENT_VALID_R10 * GEOPACKAGE_QUERY_EXTENT_INVALID_R10 * GEOPACKAGE_QUERY_EXTENT_LIST_R10
  • 'How geopaparazzi checks for valid geometries'
  • 'Spatialite test Databases used to check for valid geometries'

---

2017-03-30: Mark Johnson, Berlin Germany

---