May 29, 2013 at 8:50 am
Hi,
I'm going crazy here. I need to store Google map locations in a SQL table.
I create the table with two fields Latitude and Longitude - both with Geography data types.
When I go to insert my Latitude value '38.80345' it says :
"Invalid value for cell ..invalid cast from system.string to Types SQLGeography - type a value appropriate for the data type"
what the...pretty sure I've done this in the past..what I'm am not doing right?
May 29, 2013 at 11:38 am
A geography type consists of both latitude and longitude. You're trying to store them seperately and you can't do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2013 at 1:45 pm
Hi
As Grant said the Geography (and Geometry) datatypes are design to store and manipulate sets of coordinates. They can't be used to store an ordinate.
I guess the question is what do you wish to do with the information once you have it. If you want to be able to do spatial queries or other spatial operations based on it, you should probably store it as a geography. Otherwise storing it as a couple of floats is okay.
To construct a point geography from the lat/lon that you get from google use the following:
Geography::Point(Lat, Lon, SRID). The SRID in your case is likely to be 4326 (from memory)
If you are going to start storing geographies other than points then you will need to learn the Well Known Text formats.
If you do decide to use a Geography, the next thing would be to determine if you are going to query on it. If you are, you will need to create a spatial index.
Here's the MSDN link for the Geography datatype
May 30, 2013 at 12:12 pm
Like the other said, geography and geometry store points, which are already pairs of Lat/Long values. Geographies require a SRID while geometry does not.
If you want to store the lat/longs individually like that, just create your Latitude and Longitude fields and give them a float data type. You can easily combined them into a geography with something similar to the following code:
SELECT [SQL_ID]
, [Latitude]
, [Longitude]
, geography::STPointFromText ('Point (' + Cast ([Longitude] as varchar (10)) + ' ' + Cast ([Latitude] as varchar (10)) + ')', 4326) as SQL_GEOG
FROM myTable
Remember that 'Longitude' values are the 'X' value when constructing point features with X-Y pairs (Long-Lat). The SRID 4326 is analogous to WGS84 and able to be used in most web-mapping platforms (BingMaps, Google, ESRI, Openlayers, etc) right away.
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply