In previous posts, I showed how to create ARC/INFO functions in SQL using Manifold 8.  While I love SQL, it is somewhat frustrating to have to always rewrite the SQL code every time I want to run a GIS function.  The Radian engine allows me to create Functions, and thus, I can create a library of functions.

For example, the SQL below shows two functions within a query component.  The first one does Nearest Neighbor Analysis, (NNI), and the second one does the classic Clip function.

You will see the FUNCTION declaration which includes a name, the variable passed in and its type, and what the return value is. So, for Function NNI, it is receiving a TABLE that we are internally calling pp, and returning a TABLE based on the resultant SQL query.

The EXECUTE CALL calls the Function, and passes it a table (in this case, [L Table]).  So, I can put in as many functions as I want, and then call them at will.  This then becomes a sort of Function library where I can create all kinds of complex spatial queries.

--$manifold$

-- Nearest Neighbor Function

FUNCTION NNI (pp TABLE) TABLE AS
(SELECT avg(dist) AS NND 
 FROM 
 (
 SELECT a.ID, min(GeomDistance(a.[Geom (I)],b.[Geom (I)],1)) 
 AS dist
 FROM pp AS a, pp AS b
 WHERE a.ID <> b.ID
 GROUP BY a.ID
 )) 
END;

-- Clip Function

FUNCTION ESRICLIP(p TABLE, q TABLE) TABLE AS
(SELECT * FROM 
 (
 SELECT GeomClip(p.[Geom (I)],q.[Geom (I)],true, 1) AS g, p.id 
 AS ppid, q.id AS qid
 FROM p, q
 WHERE GeomTouches(p.[Geom (I)],q.[Geom (I)], 1)
)
 RIGHT JOIN p ON ppid = p.id
 RIGHT JOIN q ON qid = q.id
)
END;


EXECUTE CALL NNI([L Table])
--EXECUTE CALL ESRICLIP([L Table], [P Table])