In case you are getting tired of reading examples from my book (did I mention that I have a textbook you can buy from Amazon?), I thought I would throw a post in here and there that illustrates other aspects of SQL and GIS. A friend recently sent me a file of stream segments, and wanted to determine the sinuosity of each stream segment. You may recall the sinuosity formula as the length of the segment / distance between the start and endpoint of a line. This is essentially a ratio of the theoretical straight line distance between the endpoints and the actual distance along the segment.
Lets suppose we have a vector file named Stream_Sample. Sinuosity is easily calculated with SQL and a few spatial constructs as:
SELECT Length([Geom (I)]) / Distance(StartPoint([Geom (I)]),EndPoint([Geom (I)])) AS ss FROM [Stream_Sample]
The above query uses the length function on the geometry object (which returns the length of the segment) and divides it by the straight line distance of the endpoints. Here we are able to utilize the startpoint and endpoint functions to return two point objects. And, like previous queries, we are using the distance function to determine the distance between both points. Easy enough. But, this does not actually populate a field with that particular value.
To do that, we have to issue an UPDATE statement. Manifold GIS is doing something really interesting with their implementation of the UPDATE statement in SQL. To my knowledge, this is not official SQL behavior, but becomes one of the most powerful features in Manifold SQL. You can treat the results of an SQL query as a table, and actually update the fields referenced in the query. Ordinarily, the UPDATE statement updates a table like this:
UPDATE sometable SET somefield = 5
But in Manifold, we can wrap our previous query into an UPDATE statement. The update statement would look like this:
UPDATE (SELECT streamsinu, length([Geom (I)]) / distance(startpoint([Geom (I)]),endpoint([Geom (I)])) AS ss FROM [Stream_Sample]) SET streamsinu = ss
That’s it! If anyone from Manifold is reading this, PLEASE DO NOT REMOVE THIS FEATURE FROM THE UPDATE STATEMENT WHEN YOU COME OUT WITH MANIFOLD 9.0.