XQuery/Nationalgrid and Google Maps

From Wikibooks, open books for an open world
Jump to navigation Jump to search

In the UK, the XML standard for the exchange of timetable information is TransXchange. The location of, for example, bus stops, is expressed in Northings and Easting on the UK National Grid. To plot these on, say , Google Maps requires these coordinates to be transformed into latitude and longitude using the WGS84 datum.

TransXChange[edit | edit source]

Here is an extract from the beginning of a typical timetable document showing a single StopPoint definition:

<TransXChange xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:apd="http://www.govtalk.gov.uk/people/AddressAndPersonalDetails" xmlns="http://www.transxchange.org.uk/" xsi:SchemaLocation="http://www.transxchange.org.uk/ TransXChange_general.xsd" CreationDateTime="2006-12-07T14:47:00-00:00" ModificationDateTime="2006-12-07T14:47:00-00:00" Modification="new" RevisionNumber="0" FileName="SVRSGAO070-20051210-5580.xml" SchemaVersion="2.1" RegistrationDocument="false">
        <StopPoint CreationDateTime="2006-12-07T14:47:00-00:00">
                <CommonName>Rupert Street (CA)</CommonName>
                <Street>Rupert Street</Street>
                <Crossing>Colston Avenue</Crossing>

Coordinate transformation[edit | edit source]

Transformation from OS National Grid Coordinates to WSG84 latitudes and longitudes used in GoogleMaps requires two kinds of transformation:

  • between latitude and longitudes on an ellipsoidal model of the Earth and the Transverse Mercator projection used for the OS
  • between the latitude/longitude coordinates based on on different ellipsoids used in the OS coordinates and the global WGS84 coordinates.

An XQuery module which contains these functions and other utility functions is available in the Github .

Conversion from TransXChange[edit | edit source]

As an example of the use of these functions, the following script converts the StopPoints of a TransXChange file to a simpler format with lat/long coordinates. Here a local correction is required for more accurate local registration.

(:  Transforms the Stopcodes in a TransXchange file to a simpler format with National grid references converted to latitude and longitude :)
declare namespace tx="http://www.transxchange.org.uk/";

import module namespace geo="http://kitwallace.me/geo" at "/db/lib/geo.xqm";

declare option exist:serialize  "method=xml media-type=text/xml highlight-matches=none";
declare function local:camelCase($s) {
     for $word in tokenize($s,' ')
     return concat(upper-case(substring($word,1,1)), lower-case(substring($word,2))),
     ' ')

    {for $stopCode in distinct-values(//tx:StopPoint/tx:AtcoCode)
     let $stop := (//tx:StopPoint[tx:AtcoCode=$stopCode])[1]
     let $d := $stop/tx:Descriptor
     let $l := $stop/tx:Place/tx:Location
             {if ($d/tx:Landmark ne 'NONE') 
              then <LandMark>{local:camelCase($d/tx:Landmark)}</LandMark>
              else ()
            {geo:round-LatLong(geo:OS-to-LatLong(geo:Mercator($l/tx:Easting, $l/tx:Northing)),6)}


Output[edit | edit source]

The output of this transformation contains StopPoints e.g.

         <CommonName>Rupert Street (CA)</CommonName>
         <Street>Rupert Street</Street>
         <Crossing>Colston Avenue</Crossing>
         <geo:LatLong xmlns:geo="http://kitwallace.me/geo" latitude="51.455901" longitude="-2.596312" height="49.136187"/>

Mapping the bus stops[edit | edit source]

One application of this extracted data would be to plot the stops within a given range of a location. This requires a distance calculation which is good enough for small distances :

declare function geo:plain-distance ($f, $s as element(geo:LatLong))  as xs:double {
   let $longCorr := math:cos(math:radians(($f/@latitude +$s/@latitude) div 2))
   let $dlat :=  ($f/@latitude - $s/@latitude) * 60
   let $dlong := ($f/@longitude - $s/@longitude) * 60 * $longCorr
   return math:sqrt(($dlat * $dlat) + ($dlong * $dlong))

To generate the kml file:

(: return the StopPoints within $range of $latitude and $longitude :)

import module namespace geo="http://kitwallace.me/geo" at "/db/lib/geo.xqm";

declare option exist:serialize  "method=xhtml media-type=application/vnd.google-earth.kml+xml highlight-matches=none"; 

let $latitude := xs:decimal(request:get-parameter("latitude", 51.4771))
let $longitude := xs:decimal(request:get-parameter ("longitude",-2.5886))
let $range := xs:decimal(request:get-parameter("range",0.5))
let $focus := geo:LatLong($latitude,$longitude)
let $x := response:set-header('Content-Disposition','attachment;filename=stops.kml;')

   <name>Bus Stops  within {$range} miles of   {geo:LatLong-as-string($focus)}</name> 
   <Style id="home">
   <Style id="stop">

    {  for $stop in doc("/db/apps/xqbook/geo/stopPoints.xml")//StopPoint
       let $latlong := geo:LatLong($stop/LatLong/@latitude,$stop/LatLong/@longitude)
       let $dist := geo:plain-distance($focus,$latlong) * 0.868976242 (: distance is in nautical  miles :)
       where $dist < $range  
         {concat($stop/CommonName,' ',$stop/Landmark,' on ', $stop/Street, ' near ', $stop/Crossing)}  is {geo:round($dist,2)} miles away.

Stops within half a mile of my home as KML. On GoogleMaps the stops appear to be closely aligned to the bus stop overlay, presumably generated from the same base locations.

Icons[edit | edit source]

Selecting Icons for kml is eased if you can easily browse them. Here is a simple browser in XQuery:

declare variable  $base := "http://maps.google.com/mapfiles/kml/";
declare option exist:serialize "method=xhtml media-type=text/html";

   <h2>Google Earth icons</h2>
   <p>Base url {$base}</p>
    {for $pal in (2 to 5)
        <h2>Palette pal{$pal}</h2>
        {for $i in (0 to 63)
         let $icon := concat('pal',$pal,'/icon',$i,'.png')
            <img src="{$base}{$icon}" title="{$icon}"/>

Browse kml icons