none
How should the data be generated for using with Spatial Web Services and EF5. RRS feed

  • Question

  • Hi,

    Regarding your article http://blogs.msdn.com/b/rbrundritt/archive/2013/11/08/how-to-create-a-spatial-web-service-that-connects-a-database-to-bing-maps-using-ef5.aspx

    I would like to have my own data in the same format so that the application can use the Web Service and display the pin points on the map.

    My query is

    1. For example I want to display some particular tourist locations for a city. What do I need before hand to generate the Location data in the table Cities. Let us say Central Park, Statue of Liberty, Empire State Building, Rockefeller Center, Times Square, China Town all in the city of New York.  

    Kindly assist how to generate the Location data in the table Cities so that I can get it converted to a Well Know Text representation of the center coordinate and then converts this into a DbGeography object in the application.

    Please note that it should be able to generate the Location column for any location in the world.

    For example the table Cities has this row.

    ID Name Country_ISO Population Location
    9820 Chennai IN 4328063 0xE6100000010CB08F4E5DF92C2A40B2BAD573D2115440

    I would like to have the data in my table as

    ID LandmarkName  city_ISO Type Location
    3333 Empire State Building NY TouristSpot <Need to generate the location for Empire State Building>

    Thanks and regards,

    Rajesh.


    • Edited by M.Rajesh Monday, April 28, 2014 11:21 AM Edited and added data.
    Monday, April 28, 2014 11:01 AM

Answers

  • Ok, you first need to geocode your locations (Turn the string address data into a coordinate). This can be a number of different ways;

    Once the data is geocoded you can then use the latitude and longitude values to create and SqlGeography or SqlGeometry object in your database. (SqlGeography is more accurate but can be a bit slower and harder to work with. I would start with that and if all else fails then switch to SqlGeometry). You can then use these values like so to create an SqlGeography object:

    DECLARE @g geography;
    SET @g = geography::STPointFromText('POINT([longitudeValue] [LatitudeValue])', 4326);
    SELECT @g.ToString();

    This is documented here: http://technet.microsoft.com/en-us/library/bb933979.aspx 4326 is the Spatial Reference Identifier for WGS84 Mercator projection (the spatial projection used in Bing Maps). This helps to ensure accurate spatial calculations.


    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Monday, April 28, 2014 7:11 PM
    • Marked as answer by M.Rajesh Wednesday, April 30, 2014 12:48 PM
    Monday, April 28, 2014 7:11 PM

All replies

  • Ok, you first need to geocode your locations (Turn the string address data into a coordinate). This can be a number of different ways;

    Once the data is geocoded you can then use the latitude and longitude values to create and SqlGeography or SqlGeometry object in your database. (SqlGeography is more accurate but can be a bit slower and harder to work with. I would start with that and if all else fails then switch to SqlGeometry). You can then use these values like so to create an SqlGeography object:

    DECLARE @g geography;
    SET @g = geography::STPointFromText('POINT([longitudeValue] [LatitudeValue])', 4326);
    SELECT @g.ToString();

    This is documented here: http://technet.microsoft.com/en-us/library/bb933979.aspx 4326 is the Spatial Reference Identifier for WGS84 Mercator projection (the spatial projection used in Bing Maps). This helps to ensure accurate spatial calculations.


    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Monday, April 28, 2014 7:11 PM
    • Marked as answer by M.Rajesh Wednesday, April 30, 2014 12:48 PM
    Monday, April 28, 2014 7:11 PM
  • Hi,

    Thanks a lot for this help and I was able to get it done based on the latitude and longitude.

    I used this sql query to update the values based on the latitude and longitude.

    UPDATE [dbo].[Landmarks]
    SET [Position] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                        CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
    GO

    Regards,

    Rajesh.

    Wednesday, April 30, 2014 12:48 PM