A Simple Netkernel Spatial Application
The simple Netkernel application that I had previously put together was quite basic, and not that interesting from a real world perspective. Here’s a more practical example that also uses PostGIS to perform some spatial processing.
Requirement: Given an X/Y coordinate, find me the nearest piece of equipment.
Request:
http://hostname/webdb/nearest-equip?x=9555555&y=4555555
Application:
(Apology: My syntax highlighter has trouble with XML namespaces, so I’ve had to present the plain text version.)
<idoc>
<seq>
<instr>
<type>SQLEscapeXML</type>
<operand>this:param:param</operand>
<target>var:param</target>
</instr>
<instr>
<type>xslt</type>
<operand>var:param</operand>
<operator>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:template match="/nvp">
<sql>
SELECT equip.equipment_id,
Distance(
GeomFromText(
'POINT(<xsl:value-of select="x"/>
<xsl:text> </xsl>
<xsl:value-of select="y"/>
)'
), equip.the_geom
) as distance
FROM equip
ORDER BY distance ASC
LIMIT 1;
</sql>
</xsl:template>
</xsl>
</operator>
<target>var:sql</target>
</instr>
<instr>
<type>sqlQuery</type>
<operand>var:sql</operand>
<target>var:queryResult</target>
</instr>
<instr>
<type>xslt</type>
<operand>var:queryResult</operand>
<operator>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output indent="no" method="xml"/>
<xsl:template match="/results/row">
<equipment>
<xsl:copy-of select="./*"/>
</equipment>
</xsl:template>
</xsl>
</operator>
<target>this:response</target>
</instr>
</seq>
</idoc>
Normally I’d store the XSL in separate files, but have included it here inline so you can see what’s going on.
The result is a small XML document that looks like:
<?xml version="1.0" encoding="UTF-8"?>
<equipment>
<equipment_id>123456</equipment_id>
<distance>78349.5745430395</distance>
</equipment>
Of course, all the cool cats will want JSON output rather than “boring old XML”, so here’s how to do it. The new <instr>uction at the bottom of the DPML document adds a whopping 20% to the amount of code in the application. :-)
<idoc>
<seq>
<instr>
<type>SQLEscapeXML</type>
<operand>this:param:param</operand>
<target>var:param</target>
</instr>
<instr>
<type>xslt</type>
<operand>var:param</operand>
<operator>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:template match="/nvp">
<sql>
SELECT equip.equipment_id,
Distance(
GeomFromText(
'POINT(<xsl:value-of select="x"/>
<xsl:text> </xsl:text>
<xsl:value-of select="y"/>
)'
), equip.the_geom
) as distance
FROM equip
ORDER BY distance ASC
LIMIT 1;
</sql>
</xsl:template>
</xsl>
</operator>
<target>var:sql</target>
</instr>
<instr>
<type>sqlQuery</type>
<operand>var:sql</operand>
<target>var:queryResult</target>
</instr>
<instr>
<type>xslt</type>
<operand>var:queryResult</operand>
<operator>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output indent="no" method="xml"/>
<xsl:template match="/results/row">
<equipment>
<xsl :copy-of select="./*"/>
</equipment>
</xsl:template>
</xsl>
</operator>
<target>var:xmlResult</target>
</instr>
<instr>
<type>JSONFromXML</type>
<operand>var:xmlResult</operand>
<target>this:response</target>
</instr>
</seq>
</idoc>
Spatially enabled database. 100% declarative code. Love it!
Update: The above spatial query takes 80ms to execute on my 2Ghz laptop, although the equip table only contains 270 records.