Convert MultiLineString geometries to LineString using SpatiaLite SQL

Recently I made a mistake in creating a linear feature as a multi-line string instead of a plain line string geometry in SpatiaLite. I really did not want to digitize the features again so I decided to use SQL statements to convert the existing multi-line string geometries to line strings.

The following simple example shows how to do the conversion from a multi-line string table roads to a new line string table newroads. The basic steps involve (1) creating a new table, (2) populate the new table, (3) updating the new table with the line string geometry, (4) fixing the spatial metadata.

The table roads with the incorrect Multi-Line String geometry

Create a new table

1. In SpatiaLite, type in the following SQL statement to create a new empty table newroads from the roads table. Execute the statement.
   
   CREATE TABLE newroads AS SELECT * FROM roads LIMIT 0
   
   A blank table is created with the same structure as the roads table.
   
   

Populate the new table with the textual attribute values

1. In SpatiaLite, type in the following SQL statement to copy the rows (without the geometry field) from the original roads table into the new table newroads.
   
   INSERT INTO newroads(pkuid,name)SELECT pkuid,name FROM roads 
   
2. Execute the SQL statement.
   
   All the rows are copied into the new table newroads.
   
   A simple query of the new table newroads show that all fields are populated with the exception of the geometry field, which remains as null.
   
   
   
   

Update the geometry field

1. In SpatiaLite, type in the following SQL statement to form the SQL update statements to populate the geometry field.
   
   SELECT
   'UPDATE newroads SET Geometry='||'ST_GeomFromText('''||
   AsText(Geometry)||
   ''', 4326) WHERE pkuid='||
   pkuid||';' FROM roads
   
   Note: 4326 is the coordinate system reference used for the roads feature.
   
2. Execute the statement.
   
   The update SQL statements are generated.
   
   
3. In SpatiaLite GUI, click the grey area above and left of the resultant rows, as shown above.
   
   All the resultant rows are selected.
   
4. Mouse right click on the selected rows.
   
   A pop up menu appears.
   
   
5. Choose Copy.
   
   The resultant rows are copied to the OS Clipboard.
   
6. In a text editor, paste the rows into a new file, e.g. demorun.sql.
   
   
   
7. Change all occurrences of the string "MULTILINESTRING" to "LINESTRING".
   
8. Change all occurrences of the string "((" to "(".
   
9. Change all occurrences of the string "))" to ")".
   
   The statements should now look like the following example.
   
   UPDATE newroads SET Geometry=ST_GeomFromText('LINESTRING(-123.013367 49.328037, -122.980708 49.353917, -122.981324 49.386576, -122.975779 49.409992)', 4326) WHERE pkuid=1;
   
10. Save the changes.
    
11. In SpatiaLite, click the Execute SQL script button as shown below.
    
    
    
    The SQL Script Windows appears.
    
    
12. Select the newly created SQL script file, e.g. demorun.sql. Click Open.
    
    The geometry field in newroads is updated.
    
    
    
    

Fix the spatial metadata

1. In SpatiaLite GUI, mouse right click on the geometry field of the new table newroads.
   
   A pop up menu appears.
   
   
2. Choose Recover geometry column.
   
   The Recover geometry column dialog box appears.
   
3. In the SRID field, type in the coordinate system SRID value e.g. 4326.
   
4. In the Dims field, choose the appropriate dimension of the geometry field, e.g. XY.
   
5. In the Geometry Type list, choose LINESTRING.
   
6. Click OK.
   
   The geometry field is recovered.