Last modified on 16 April 2009, at 13:27

XQuery/Nationalgrid and Google Maps

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.

TransXChangeEdit

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">
    <StopPoints>
        <StopPoint CreationDateTime="2006-12-07T14:47:00-00:00">
            <AtcoCode>0100BRP90340</AtcoCode>
            <NaptanCode>BSTGAJT</NaptanCode>
            <Descriptor>
                <CommonName>Rupert Street (CA)</CommonName>
                <Landmark>NONE</Landmark>
                <Street>Rupert Street</Street>
                <Crossing>Colston Avenue</Crossing>
            </Descriptor>
            <Place>
                <NptgLocalityRef>N0076879</NptgLocalityRef>
                <Location>
                    <Easting>358664</Easting>
                    <Northing>173160</Northing>
                </Location>
            </Place>
            <StopClassification>
                <StopType>BCT</StopType>
                <OnStreet>
                    <Bus>
                        <BusStopType>MKD</BusStopType>
                        <TimingStatus>OTH</TimingStatus>
                        <MarkedPoint>
                            <Bearing>
                                <CompassPoint>N</CompassPoint>
                            </Bearing>
                        </MarkedPoint>
                    </Bus>
                </OnStreet>
            </StopClassification>
            <AdministrativeAreaRef>010</AdministrativeAreaRef>
        </StopPoint>

Coordinate transformationEdit

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 XQuery Examples Google Code project.

Conversion from TransXChangeEdit

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://www.cems.uwe.ac.uk/xmlwiki/geo" at "../lib/geo.xqm";

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

<StopPointSet> 
    {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
      return 
        <StopPoint>
             <AtcoCode>{string($stop/tx:AtcoCode)}</AtcoCode>
             <CommonName>{string($d/tx:CommonName)}</CommonName>
             {if ($d/tx:Landmark ne 'NONE') 
              then <LandMark>{local:camelCase($d/tx:Landmark)}</LandMark>
              else ()
            }
            <Street>{local:camelCase($d/tx:Street)}</Street>
            <Crossing>{local:camelCase($d/tx:Crossing)}</Crossing>
            {geo:round-LatLong(geo:OS-to-LatLong(geo:Mercator($l/tx:Easting, $l/tx:Northing)),6)}
       </StopPoint>
    }
</StopPointSet>

Convert


OutputEdit

The output of this transformation contains StopPoints e.g.

    <StopPoint>
        <AtcoCode>0170SGP90690</AtcoCode>
        <CommonName>Coldharbour Lane</CommonName>
        <Street>Coldharbour Lane</Street>
        <Crossing>Filton Road</Crossing>
 
        <geo:LatLong xmlns:geo="http://www.cems.uwe.ac.uk/xmlwiki/geo" latitude="51.503924" longitude="-2.544798"/>
    </StopPoint>

Mapping the bus stopsEdit

One application of this 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://www.cems.uwe.ac.uk/xmlwiki/geo" at "../lib/geo.xqm";
import module namespace gmap = "http://www.cems.uwe.ac.uk/xmlwiki/gmap" at "../lib/gmap.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;')

return
<Document>
   <name>Bus Stops  within {$range} miles of   {geo:LatLong-as-string($focus)}</name> 
   <Style id="home">
       <IconStyle>
          <Icon><href>http://maps.google.com/mapfiles/kml/pal2/icon2.png</href>
        </Icon>
       </IconStyle>
    </Style>
   <Style id="stop">
       <IconStyle>
          <Icon><href>http://maps.google.com/mapfiles/kml/pal5/icon13.png</href>
        </Icon>
       </IconStyle>
    </Style>

    <Placemark>
        <name>Home</name>
        <Point>
         <coordinates>{gmap:LatLong-as-kml($focus)}</coordinates>
         </Point>
         <styleUrl>#home</styleUrl>
     </Placemark>
    {  for $stop in doc("/db/Wiki/geo/stopPoints.xml")//StopPoint
       let $dist := geo:plain-distance($focus,$stop/geo:LatLong) * 0.868976242 (: distance is in nautical  miles :)
       where $dist < $range  
       return
     <Placemark>
        <name>{string($stop/CommonName)}</name>
       <description>
         {concat($stop/CommonName,' ',$stop/Landmark,' on ', $stop/Street, ' near ', $stop/Crossing)}  is {geo:round($dist,2)} miles away.
       </description>
       <Point> 
        <coordinates>{gmap:LatLong-as-kml($stop/geo:LatLong)}</coordinates>
       </Point>
       <styleUrl>#stop</styleUrl>
     </Placemark>
   }
</Document>

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

IconsEdit

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";

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

Browse kml icons