Digital Earth Weblog

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.