AsGml() to GeoRSS

Question

Does anybody have an elegant way of taking the GML representation of a geometry object from the AsGml() method and turn it into a valid GeoRSS markup which can be consumed by, say Virtual Earth?

My current (very inelegant) method is as follows:

1.) Define a new variable @GeoRSS (xml datatype)

Code Snippet

DECLARE @GeoRSS xml

2.) Retrieve the GML representation of objects and store them in the variable using the SELECT ... FOR XML construct

Code Snippet

SET @GeoRSS = (SELECT 'title' AS title, 'description' as description, RadarGeography.AsGml() AS [where] FROM RadarStations FOR XML PATH('entry'), ROOT('feed'))

3.) Use xquery modify method to insert <title>, <author> etc. elements at the beginning of the feed

Code Snippet

SET @GeoRSS.modify('insert <title>SQL Server GeoRSS Feed</title> as first into (/feed)[1]')

4.) So far so good. Now here lies the problem.... the values returned by the AsGml() method are:

Code Snippet

<Point>
  <pos>45.256 -71.92</pos>
</Point>

Whereas GeoRSS expects:

Code Snippet

<gml:Point>
   <gml:pos>45.256 -71.92</gml:pos>
</gml:Point>

So basically for every child element of the <where> element (the alias I gave to the AsGml() column), I need to append the namespace gml: prefix - other than that the structure is identical.... the options I've considered are:
a.) Using xquery, but as far as I know while xquery can update the values of xml elements, it can't change the element names themselves.
b.) I could apply an XSL transformation, but to do that in SQL Server I would have  to compile a .Net CLR assembly and I want to do this using tools that come 'out-of-the-box' with SQL Server alone.
c.) The answer may lie in the options of the SELECT ... FOR XML options, (like using the EXPLICIT switch) but I couldn't see how.

At the moment, I'm solving this by casting my (well-formed) xml statement into a varchar(max), then using a T-SQL REPLACE statement to update the <Point>, <Polygon>, <posList> element names etc. to include the <gml: prefix, before casting the whole thing back as xml again. It works, but.... Yuck!

Any suggestions would be much appreciated!

(For those not familiar, the schema of the GeoRSS GML profile can be found here - http://www.georss.org/xml/1.1/gmlgeorss.xsd)

A sample GeoRSS feed of a point object would be as follows:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:georss="http://www.georss.org/georss"
      xmlns:gml="http://www.opengis.net/gml">
   <title>Earthquakes</title>
   <subtitle>International earthquake observation labs</subtitle>
   <link href="http://example.org/"/>
   <updated>2005-12-13T18:30:02Z</updated>
   <author>
      <name>Dr. Thaddeus Remor</name>
      <email>[e-mail]</email>
   </author>
   <id>urn:uuid:60a76c80-d399-11d9-b93C-0003939e0af6</id>
   <entry>
      <title>M 3.2, Mona Passage</title>
      <link href="http://example.org/2005/09/09/atom01"/>
      <id>urn:uuid:1225c695-cfb8-4ebb-aaaa-80da344efa6a</id>
      <updated>2005-08-17T07:02:32Z</updated>
      <summary>We just had a big one.</summary>
      <georss:where>
         <gml:Point>
            <gml:pos>45.256 -71.92</gml:pos>
         </gml:Point>
      </georss:where>
   </entry>
</feed>

Answer 1

OK - solved it using xquery. Should anybody else want to know how.....

Had Sql server allowed you to construct xml elements with computed node names this would have been (relatively) easy, by looping through each child element of the <where> element and concatenating the old element name with the namespace as follows:

Code Snippet

element { QName("http://www.opengis.net/gml", concat("gml", ":x")) }{ oldelement }

Unfortunately, whilse this is defined in the xquery specification, it doesn't seem to be included in the SQL server implementation - boo
However, since GeoRSS only contains a limited number of cases for child elements under the <where> element, it is possible to write a series of if...then conditional statements to manually specify the namespace for each possible element name as follows:

Code Snippet

for $child in /where

if (fn:local-name($child) = "Point")
        then  <gml:Point> { $child/* } </gml:Point>

else  if (fn:local-name($child) = "LineString")
        then  <gml:LineString> { $child/* } </gml:LineString>

etc.

etc.

Using this method SQL Server can output the results of a spatial query as valid GeoRSS with no need for XSLT transformations - hopefully this might help somebody in the future!

Answer 2

Hello,

 

I'm not sure I see the problem here.  If I declare a point, say (1, 2), and retrieve the GML for it, I get:

 

Code Snippet

<Point xmlns="http://www.opengis.net/gml">

  <pos>1 2</pos>

</Point>

 

Not:

 

Code Snippet

<Point>

  <pos>1 2</pos>

</Point>

 

I.e., the namespace is defined, although we use the default namespace to do so.  This should be fine.  E.g., see here (Section 6.2):

 

The scope of a default namespace declaration extends from the beginning of the start-tag in which it appears to the end of the corresponding end-tag, excluding the scope of any inner default namespace declarations. In the case of an empty tag, the scope is the tag itself.

A default namespace declaration applies to all unprefixed element names within its scope.

 

Cheers,

-Isaac

Answer 3

tanoshimi wrote:

OK - solved it using xquery. Should anybody else want to know how..... Had Sql server allowed you to construct xml elements with computed node names this would have been (relatively) easy, by looping through each child element of the <where> element and concatenating the old element name with the namespace as follows: Code Snippet element { QName("http://www.opengis.net/gml", concat("gml", ":x")) }{ oldelement } Unfortunately, whilse this is defined in the xquery specification, it doesn't seem to be included in the SQL server implementation - boo ...

 

Dear Tanoshimi

 

If you find this functionality in XQuery very useful, please file a connect feedback item requesting it at http://connect.microsoft.com/sqlserver/feedback.

 

On the topic at hand, I wonder too (like Isaac), why you are having problems. The XML that gets generated from the method should output the namespace as the default namespace.

 

Thanks

Michael

Answer 4

 

Thanks for the responses guys - I think this has to do with Virtual Earth GeoRSS parser expecting the namespace to be aliased as <gml:...> rather than having the full namespace declaration in the element tag, but I'm not near the right computer at the moment to be able to confirm this. It's not strictly a fault with SQL spatial which, as you point it, outputs the correct namespace.

 

When I get chance to look at this again, I'll post this to the Virtual Earth forum instead and get a second opinion.

 

Thanks again.

 

t.

 

 

 

 

Answer 5

Hey,

I hope some of the folks from this thread are still around. I am looking at solving the same problem, where I transform a SqlGeometry instance to a format that can be read by Virtual Earth/Bing maps. I understand the query above, but I am having a hard time getting it working - I am probably missing something obvious. Can you post the full query?  

Thanks,
Erick

Answer 6

Would you please post the sample code how you did this. I am sorry I am very new to xquery i could not grasp what you are talking about i am able to get the gml and i am trying to do the following

for $child in /where

if (fn:local-name($child) = "Point")
        then  <gml:Point> { $child/* } </gml:Point>

else  if (fn:local-name($child) = "LineString")
        then  <gml:LineString> { $child/* } </gml:LineString>

etc.

etc.

but if i can see a sample of working code i can easily learn the xquery and its structures

 

thanks

---

Chamy07

Answer 7

In the time passed since I first made this post, I've developed a preference for GeoJSON over GeoRSS (Bing Maps v7.x no longer directly loads GeoRSS, and GeoJSON is more lightweight and easy to pass around between layers).

Nevertheless, FWIW, here's a script to generate a sample geography data table and a sproc to create a GeoRSS feed from that table that Google Maps/Bing Maps v6.x both parse:

-- Create the table
CREATE TABLE PropertiesForSale (
 id int,
 address varchar(255),
 location geography,
 description varchar(max)
)
GO


-- Insert sample data
INSERT INTO PropertiesForSale VALUES
(1,
'Pilgrims Way, Chew Stoke, Somerset',
geography::Point(51.354940,-2.635765,4326),
'Grade II Listed former Rectory, with magnificent architectural features and stunning gardens.'
),
(2,
'Moulsford, Wallingford, Oxfordshire',
geography::Point(51.549963,-1.149013,4326),
'Situated on the River Thames, this period house features landscaped gardens extending up to 240ft, and private mooring.'
),
(3,
'Pantings Lane, Highclere, Newbury',
geography::Point(51.347206,-1.375828,4326),
'A newly developed 5-bedroom house on the edge of Highclere, with very high build specifications used throughout.'
)
GO


-- Create stored procedure to generate GeoRSS
CREATE PROCEDURE [dbo].[uspGeoRSSFeeder]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Declare an XML variable to hold the GeoRSS output
DECLARE @GeoRSS xml;

/**
 * Create the elements of the feed using SELECT … FOR XML and AsGml()
**/
WITH XMLNAMESPACES (
 'http://www.opengis.net/gml' AS gml,
 'http://www.georss.org/georss' AS georss
)
SELECT @GeoRSS =
 (SELECT
  [address] AS title,
  [description] AS description,
  location.AsGml() AS [georss:where]
 FROM
  PropertiesForSale
 FOR XML PATH('item'), ROOT('channel')
)

/**
 * Style the results using XQuery
 **/
SELECT @GeoRSS.query('
<rss version="2.0"
 xmlns:georss="http://www.georss.org/georss"
 xmlns:gml="http://www.opengis.net/gml">
<channel>
 <title>SQL Server GeoRSS Feed</title>
 <description>This feed contains information about some fictional properties for 
sale in order to demonstrate how to syndicate spatial data using the GeoRSS format. 
</description>
 <link>http://www.beginningspatial.com</link>
 {
  for $e in channel/item
  return
  <item>
  <title> { $e/title/text() }</title>
  <description> { $e/description/text() }</description>
  <georss:where>
   {
    for $child in $e/georss:where/*
    return
    if (fn:local-name($child) = "Point") then <gml:Point> { $child/* } </gml:Point>
    else if (fn:local-name($child) = "LineString") then <gml:LineString> { $child/* } </gml:LineString>
    else if (fn:local-name($child) = "Polygon") then <gml:Polygon> { $child/* } </gml:Polygon>
    else if (fn:local-name($child) = "MultiPoint") then <gml:MultiPoint> { $child/* } </gml:MultiPoint>
    else if (fn:local-name($child) = "MultiCurve") then <gml:MultiCurve> { $child/* } </gml:MultiCurve>
    else if (fn:local-name($child) = "MultiSurface") then <gml:MultiSurface> { $child/* } </gml:MultiSurface>
    else if (fn:local-name($child) = "MultiGeometry") then <gml:MultiGeometry> { $child/* } </gml:MultiGeometry>
    else ()
   }
  </georss:where>
 </item>
 }
</channel>
</rss>
') AS GeoRSSFeed
END
GO


EXEC uspGeoRSSFeeder;

The results are as follows:

<rss xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml" version="2.0">
  <channel>
    <title>SQL Server GeoRSS Feed</title>
    <description>This feed contains information about some fictional properties for
sale in order to demonstrate how to syndicate spatial data using the GeoRSS format.
</description>
    <link>http://www.beginningspatial.com</link>
    <item>
      <title>Pilgrims Way, Chew Stoke, Somerset</title>
      <description>Grade II Listed former Rectory, with magnificent architectural features and stunning gardens.</description>
      <georss:where>
        <gml:Point>
          <gml:pos>51.35494 -2.635765</gml:pos>
        </gml:Point>
      </georss:where>
    </item>
    <item>
      <title>Moulsford, Wallingford, Oxfordshire</title>
      <description>Situated on the River Thames, this period house features landscaped gardens extending up to 240ft, and private mooring.</description>
      <georss:where>
        <gml:Point>
          <gml:pos>51.549963 -1.149013</gml:pos>
        </gml:Point>
      </georss:where>
    </item>
    <item>
      <title>Pantings Lane, Highclere, Newbury</title>
      <description>A newly developed 5-bedroom house on the edge of Highclere, with very high build specifications used throughout.</description>
      <georss:where>
        <gml:Point>
          <gml:pos>51.347206 -1.375828</gml:pos>
        </gml:Point>
      </georss:where>
    </item>
  </channel>
</rss>

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 8

In the time passed since I first made this post, I've developed a preference for GeoJSON 

Hi Alastair :)

Is there any chance you could do a blog post about this? I'll gladly donate one of my kids, for this info!

-PK-

---

-Pure Krome-

Answer 9

In the time passed since I first made this post, I've developed a preference for GeoJSON over GeoRSS (Bing Maps v7.x no longer directly loads GeoRSS, and GeoJSON is more lightweight and easy to pass around between layers).

Can you explain your preference, please?

I'm currently using the code from your book and Richard Brundritt's parsing code to load into Bing Maps 7.

I'd like to know how this is a better and easier format to utilize within Bing. As Krome mentioned, a blog post would really be appreciated.

Answer 10

How to create SqlGeometry from GeoJSON.

Here is my GeoJSON.

{ "type": "MultiPolygon",
  "coordinates": [
    [[[102.0, 2.0], [103.0, 2.0], [103.0, 3.0], [102.0, 3.0], [102.0, 2.0]]],
    [[[100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]],
     [[100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2]]]
    ]
  }