locked
Latitude/Longitude RRS feed

  • Question

  • Hi,

    I've a SQL query that uses Latitude and Longitude to convert them in to geography as follows:

    geography::STPointFromText('POINT('
       + CAST([GIS_LONGTD_NO] as VARCHAR(10))
       + ' '
       + CAST([GIS_LAT_NO] AS VARCHAR(10))
       + ')', 4326) AS GEODATA 
      
    We've promoted both of the attributes to OLAP as two new attributes in our dimension table.

    Can you please help me know how will I convert these newly promoted attributes to return something similar to "geography" in SQL. The final goal is to plot this data on SSRS Maps. Any alternative suggestions are welcome as well.

     

    Thanks,

    NS

    Thursday, January 13, 2011 12:12 AM

Answers

All replies

  • i can think of one work around

    Instead of Analysis Service in SSRS , use T SQL,  in T SQL use "Openquery ". this will give you data in T SQL platform and now you can use "geography::STPointFromText". hope this will help.

     

    For OPENQUERY - http://msdn.microsoft.com/es-es/library/ms188427.aspx

    For how to use SSAS in Open Query - http://geekswithblogs.net/darrengosbell/archive/2006/04/26/76418.aspx

     


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
    Thursday, January 13, 2011 9:15 AM
  • Thanks Gaurav for your help. I am aware of this option, but this doesn't allow my users (who are using Reports Builder or Excel) to have an access to this data. I will leave this question open for any other ideas for a little bit otherwise I will mark your response an answer. Thanks again!

    Thursday, January 13, 2011 5:47 PM
  • thanks, how about creating a model or data Source view publishing it on the RS and provide user access to that??

    this might solve your report builder problem.

     


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
    Friday, January 14, 2011 3:34 AM
  • If you're trying to plot geography data on an SSRS map, then this is the best approach I know about at the moment:
    http://blogs.technet.com/b/andrew/archive/2010/09/20/a-worked-example-of-using-spatial-and-olap-data-in-reporting-services.aspx

    SSAS just doesn't support true geography datatypes in AS2008 R2. So this approach is to get the geography datatypes from SQL and then using other SSAS to color the map.

    If this isn't appropriate, then contact the IT-Workplace guys and suggest then get geography datatypes into their IQ custom SSRS data extension. We think it's possible, but they need customer demand before implementing it. http://it-workplace.co.uk/_layouts/CustomerSitePages/IQ.aspx


    http://artisconsulting.com/Blogs/GregGalloway
    • Marked as answer by Raymond-Lee Sunday, January 23, 2011 6:30 AM
    • Unmarked as answer by NSMP Wednesday, February 2, 2011 11:49 PM
    • Marked as answer by NSMP Wednesday, February 2, 2011 11:49 PM
    Sunday, January 16, 2011 12:00 AM
  • Thanks furmangg,

    I think there is no workaround!

    Anyways,Its better to contact  IT-Workplace.

     

    Thanks again,

    NS

    Tuesday, January 18, 2011 11:42 PM