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])
Are these functions callable inside sql, like that:
Select
…..
From
ESRICLIP([p], [q])
join
[r]
on …..
?
Next week I will show some.