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
- 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
- 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
- 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
- In SpatiaLite, type in the following SQL statement to form the SQL update statements to populate the geometry field.
'UPDATE newroads SET Geometry='||'ST_GeomFromText('''||
''', 4326) WHERE pkuid='||
pkuid||';' FROM roads
Note: 4326 is the coordinate system reference used for the roads feature.
- Execute the statement.
The update SQL statements are generated.
- In SpatiaLite GUI, click the grey area above and left of the resultant rows, as shown above.
All the resultant rows are selected.
- Mouse right click on the selected rows.
A pop up menu appears.
- Choose Copy.
The resultant rows are copied to the OS Clipboard.
- In a text editor, paste the rows into a new file, e.g. demorun.sql.
- Change all occurrences of the string "MULTILINESTRING" to "LINESTRING".
- Change all occurrences of the string "((" to "(".
- 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;
- Save the changes.
- In SpatiaLite, click the Execute SQL script button as shown below.
The SQL Script Windows appears.
- Select the newly created SQL script file, e.g. demorun.sql. Click Open.
The geometry field in newroads is updated.
Fix the spatial metadata
- In SpatiaLite GUI, mouse right click on the geometry field of the new table newroads.
A pop up menu appears.
- Choose Recover geometry column.
The Recover geometry column dialog box appears.
- In the SRID field, type in the coordinate system SRID value e.g. 4326.
- In the Dims field, choose the appropriate dimension of the geometry field, e.g. XY.
- In the Geometry Type list, choose LINESTRING.
- Click OK.
The geometry field is recovered.