asked on

Spatial Data conversion

I have a tabel in a SQL 2008 database that contains lat/lon of customers. They are in the format (xxxxx.xxxx -xxxxxx.xxxxx) I would like to be able to use it to map them using and web service. If I am understanding it correctly I have to store the data as geography data type. I am having trouble writing the T-SQL to do this. I have attached the code that I have been using but I am not sure how to use varibales with in SQL. Could someone please provider the T-SQL to convert a Varchar(max) to a Geography type.

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable;
GO
 
CREATE TABLE SpatialTable 
  ( [id] [int] IDENTITY (1,1),
  [SVCIDMapping] [int],
     [GeogCol1] [geography], 
    [GeogCol2] AS GeogCol1.STAsText() )
GO
 
Declare @mygeo as varchar(max)
Declare @SVCID as int
Select @Mygeo =  Point FROM tblGeoData
Select @SVCID = SvcMappingID from tblGeoData
 
INSERT INTO SpatialTable(GeogCol1,SVCIDMapping)
VALUES (geography::STGeomFromText(('Point' + @Mygeo),4326),
@SVCID

Open in new window

elimesika

HI

See
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2798362&SiteID=1

Mark Wills

Have you read through Books On Line under geography ? http://msdn.microsoft.com/en-us/library/cc280766.aspx

basically a point is a lat long pair e.g.

DECLARE @point GEOGRAPHY;
SET @point = geography::Parse('POINT(49.274138 -123.098562)');

So, unless you have tblGeoData set up with POINTS, might need to simply use the lat long pair...

now to convert a lat or longitude to a varchar, need to use the format 2 option in the convert statement (though the granularity to 6 decimals should be OK for most) e.g.

DECLARE @point GEOGRAPHY;
SET @point = geography::Parse('POINT(convert(VARCHAR(16), Lat, 2) + ' ' + convert(VARCHAR(16), long, 2))');

is that sounding more like it / the type of direction ? Or do you already have that covered ?

jagwynn

ASKER

I am still getting an error when I run the SP. I am sure that it is because i am making a mistake somewhere. The code and error is below.

Declare @point geography 
set @point =geography::Parse('Point(Covert(Varchar(16),tblGeoData.Lat,2)+''+ convert(varchar(16),tblGeodata.lon,2))')
 
INSERT INTO dbo.tblGeoData 
           ([Geopoint])
     VALUES        
           (@point)
 
 
 
 
 
 
Msg 6522, Level 16, State 1, Line 15
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24141: A number is expected at position 23 of the input. The input has Covert(Varchar(16.
System.FormatException: 
   at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

Open in new window

Mark Wills

OK, a few little problems....

first one is pretty obvious, spelled the first convert incorrectly...

second is a bit more fundamental. tblgeodata is a table containing many rows. You have to decide if you are working at the row level, or at the table level. Second part of that is you would not insert back into the same table, you would update that column...

So, it would be more like :

update tblgoedata set geopoint = geography::Parse('Point(Convert(Varchar(16),Lat,2)+' '+ convert(varchar(16),lon,2))')

or, going back to your original code...

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL
DROP TABLE dbo.SpatialTable;
GO

CREATE TABLE SpatialTable
( [id] [int] IDENTITY (1,1),
[SVCIDMapping] [int],
[GeogCol1] [geography] -- not sure what you want with col2, so have removed it for now...
)
GO

INSERT INTO SpatialTable(GeogCol1,SVCIDMapping)
select geography::Parse('Point(Convert(Varchar(16),Lat,2)+' '+ convert(varchar(16),lon,2))') , SvcMappingID
from tblgeodata

jagwynn

ASKER

Thank you for all of the help but i am still having problems getting the SP to run with out error. I copied the text from the 1st part of your post and ran it as is, you can see how it errors out. I have made a few changes and it still will not run with out giving some syntax error. I don't know much about sql so again thank you for helping me out.

Thanks

1st update tblgoedata set geopoint = geography::Parse('Point(Convert(Varchar(16),Lat,2)+' '+ convert(varchar(16),lon,2))')
			Error:Msg 102, Level 15, State 1, Line 3
					Incorrect syntax near '+ convert(varchar(16),lon,2))'.
					
2nd update tblgeodata set geopoint = geography::Parse('Point(Convert(Varchar(16),Lat,2)'+' '+ 'convert(varchar(16),lon,2))')
			Error:Msg 6522, Level 16, State 1, Line 3
			A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
			System.FormatException: 24141: A number is expected at position 24 of the input. The input has Convert(Varchar(16.
				System.FormatException: 
					at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
					at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
					at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
					at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
					at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
					at Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(SqlChars geometryTaggedText, Int32 srid)
					at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
					
3rd update tblGeoData set GeoPoint = geography::Parse('Point(' + (convert(VARCHAR(16), Lon,2)) + ' ' + (convert(VARCHAR(16), Lat,2)) + ',' +  ')')
			Error:Msg 232, Level 16, State 2, Line 2
				Arithmetic overflow error for type varchar, value = -113.986008.

Open in new window

Mark Wills

OK, it does sound like there are some funny numbers in there, and also the encapsulation of <value> in the Point(<value>) is a bit wrong...

You will need to check that all lat and lon have correct values.

I will check the manuals (have momentarily lost access to sql2008 - not in the office).

Mark Wills

Can you do a select top 10 * from tblgeodata where isnull(lat,0) <> 0 and isnull(lon,0) <> 0 ? and save as a text files and post back here ?

jagwynn

ASKER

The results are attached. I had deleted all but 4 of the records in the table to limit the amount of test data that I was working with. I reran the SP and still giving me the same error. I am going to try to change the Lat Lon values to find out if that is what is causing it.

tblGeoData.txt

Mark Wills

OK,

A couple of things, lat and lon are functions so might be some naming conflicts...

second, there is a conversion from text :

geography::STPointFromText('POINT(-122.34900 47.65100)', 4326)

so that will become :

geography::STPointFromText('POINT( convert(VARCHAR(16), tblgeodata.[Lat],2) + ' ' + convert(VARCHAR(16), tblgeodata.[Lon],2) )', 4326)

Mark Wills

Oh, and the point format should be :

geography::Point(lat, lon, 4326)

which should be :

geography::Point(tblgeodata.[Lat] , tblgeodata.[lon], 4326 )

so long as the lat and lon columns within tblgeodata are float expressions...

Mark Wills

oops, the STpointFromText should be :
geography::STPointFromText('POINT( convert(VARCHAR(16), tblgeodata.[Lat],2) + '' '' + convert(VARCHAR(16), tblgeodata.[Lon],2) )', 4326)

the embedded quotes + ' ' + each need a prefix of anoth single quote because it is embedded ie + '' '' + (plus single_quote single_quote space single_quote single_quote plus)

jagwynn

ASKER

It seems to be getting a little further. Looking at the error it looks like it is reading the text and not the value, but any way that i put it in i get an error.

update tblgeodata set geopoint = geography::STPointFromText('POINT(  convert(VARCHAR(16), tblgeodata.[Lat],2) + '' '' + convert(VARCHAR(16), tblgeodata.[Lon],2)  )', 4326)
 
 
 
Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24141: A number is expected at position 26 of the input. The input has convert(VARCHAR(16.
System.FormatException: 
   at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.STPointFromText(SqlChars pointTaggedText, Int32 srid)

Open in new window

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jagwynn

ASKER

Mark i must say that you are truly a savior. Thank you for all of the help. I am been moving slowly from Access to SQL for out biz database. i am sure that this will not be the last time that i will be thanking you.

Thanks

Mark Wills

A pleasure, Should have nailed it a bit earlier and so thanks back at ya for your patience as well...

Since you are going from Access to SQL - have you seen : Code Cheat Sheet Syntax Comparisons
http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

Look forward to next time...