locked
Inserting Float type data into geography fields?? RRS feed

  • Question

  • I want to insert data into a table containing Long/Lat data of the "geography type"....The Long/Lat data that is in the import is the float type data. How do I insert float type data into fields that are geography type???

    I have the following code

    INSERT INTO dbo.tbl_Stations
    	(Station_Name, Province_ID, Station_Elevation, Station_Longitude, Station_Latitude)
    	SELECT ENG_STN_NAME, ENG_PROV_NAME, ELEVATION, LONGITUDE_DECIMAL_DEGREES, LATITUDE_DECIMAL_DEGREES 
    		FROM dbo.tbl_ImportStation
    		GROUP BY ENG_STN_NAME, ENG_PROV_NAME, ELEVATION, LONGITUDE_DECIMAL_DEGREES, LATITUDE_DECIMAL_DEGREES;

    The fields Station_Longitude and Station_Latitude in the  table that data is being inserted into are geography type data. The fields (LONGITUDE_DECIMAL_DEGREES and LATITUDE_DECIMAL_DEGREES) in the table from which the data is coming (tbl_ImportStation) are float type.

    Thanks,


    • Edited by ChemEng Tuesday, November 13, 2012 5:39 PM
    Tuesday, November 13, 2012 5:36 PM

Answers

  • "Station_Longitude and Station_Latitude are geography data type." <- really? In that case, you have a very add column-naming convention! A single geography field records both latitude and longitude (and, optionally, Z and M) coordinates of a location in a single value, so there's no need to use more than one field.

    Anyway, the correct syntax to insert into a geography column when you have latitude and longitude float coordinate values is:

    INSERT INTO yourTable (yourGeographyField)
    SELECT geography::Point(latitudeField, longitudefield) FROM yourOtherTable;



    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by ChemEng Tuesday, November 13, 2012 5:55 PM
    Tuesday, November 13, 2012 5:42 PM
    Answerer

All replies

  • "Station_Longitude and Station_Latitude are geography data type." <- really? In that case, you have a very add column-naming convention! A single geography field records both latitude and longitude (and, optionally, Z and M) coordinates of a location in a single value, so there's no need to use more than one field.

    Anyway, the correct syntax to insert into a geography column when you have latitude and longitude float coordinate values is:

    INSERT INTO yourTable (yourGeographyField)
    SELECT geography::Point(latitudeField, longitudefield) FROM yourOtherTable;



    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by ChemEng Tuesday, November 13, 2012 5:55 PM
    Tuesday, November 13, 2012 5:42 PM
    Answerer
  • You are right....I am new to the geography data type....I think I can get rid of one of those fields. Thanks

    Also, can you help me to understand what the SRID is...Apparently t is required as an argument in geography::Point(latitudeField, longitudefield, SRID)


    • Edited by ChemEng Tuesday, November 13, 2012 6:17 PM
    Tuesday, November 13, 2012 5:45 PM
  • Also, how do I incorporate the elevation into the geography value

    When I try and run the following code I get an error stating the Point function requires 3 arguments..

    INSERT INTO myTable (myGeographyField)
    SELECT geography::Point(latitudeField, longitudefield, elevation) FROM myOtherTable;

    Tuesday, November 13, 2012 6:23 PM
  • The Point static method requires three arguments which are the latitude, longitude and SRID (rather than the lat, long, and elevation). If you want elevation, you’ll need to use the static method STPointFromText (or even the static Parse method) specifying the point as a string ‘POINT(Long Lat Elevation)’ that is, the Open Geospatial Consortium well-known text format (see http://msdn.microsoft.com/en-us/library/bb933979.aspx). If you do use elevation, bear in mind that the SQL Server spatial library does not currently use elevation in its calculations.
     
     
    Hope this helps, Bob
    Tuesday, November 13, 2012 6:49 PM
  • If you're using latitude and longitude from a GPS receiver, from Bing/Google Maps etc., just use SRID 4326 and don't worry too much about it. If you have coordinates from a national grid, UTM zone, or US state plane coordinate system, for example, you need to worry about it :)

    For an elevation example:

    INSERT INTO myTable (myGeographyField)
    SELECT geography::STPointFromText('POINT(' + CAST(longitudefield AS varchar(32)) + '  ' + CAST(latitudefield AS varchar(32)) + '  ' + CAST(elevationfield AS varchar(32)) + ')', 4326)
    FROM myOtherTable;

    But, as Bob says, while you can store/retrieve Z/M values and use them in your own custom spatial methods, elevation won't be used by any of the inbuilt spatial methods, so you might just want to leave it in a separate "Elevation" column.


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Tuesday, November 13, 2012 7:00 PM
    Answerer