| .loadshp parameters |
Step #1
simply a dot macro command understood by the SpatiaLite frontend tool, allowing to directly import an external Shapefile into a corresponding Spatial Table within the DB. |
|
CREATE TABLE Contours20FtClipped AS
SELECT c.Id AS Id, c.OBJECTID AS OBJECTID,
c.Contour AS Contour, c.Shape_Leng AS Shape_Leng,
CastToMultiLinestring(ST_Intersection(
c.Geometry, a.Geometry)) AS Geometry
FROM Contours20Ft AS c, StudyArea1MileBuffer AS a
WHERE c.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'Contours20Ft'
AND search_frame = a.Geometry
);
|
Step #2
this single SQL query performs all the actual Clipping task.
ST_Intersection() is a Spatial SQL function computing the intersection between the Study Area (polygon) and each Contour Line (linestring). This could be:
- a Linestring or a MultiLinestring (depending on the specific shape of both geometries)
- a NULL (i.e. absolutely nothing) if the Contour Line and the Study Area do not intersect at all.
- CastToMultiLinestring() simply is a cast operator ensuring that all returned geometries will be of the same type.
- the inner sub-query SELECT ROWID FROM SpatialIndex ... is simply intended to access the Spatial Index supporting the Contour Lines, so to speed up the whole query.
- CREATE TABLE Contours20FtClipped AS simply intends that the resultset returned by this SQL query has to be pemanently saved into another table to be created on the fly.
|
|
DELETE FROM Contours20FtClipped
WHERE Geometry IS NULL;
|
Step #3
just a stupid post-processing step; the previous query has surely generated many NULL geometries, and we'll now delete all them.
|
|
SELECT RecoverGeometryColumn('Contours20FtClipped', 'Geometry',
2260, 'MULTILINESTRING', 2);
SELECT CreateSpatialIndex('Contours20FtClipped', 'Geometry');
|
Step #4
the very final post-processing step: transforming the table containing all Clipped Contour Lines into a genuine Spatial Table supported by its own Spatial Index.
|
|
SELECT DateTime('now'), 'some message';
|
this simply is a weird but in this specifc case really useful SQL trick; we'll query the system clock immediately before and after performing each step, so to get full trace of the corresponding timings.
|
