2

I've recorded my track to a text file and now I'd like to insert it into SQL Server 2008 R2 database's table as a Point type.

I read my text file using OpenRowSet and insert each line into temporary table. Then I parse every row and insert right values to real table. Each row in file looks like: 

$GPRMC,101142.415,A,6210.1547,N,02929.2220,E,3.9,231.8,150310,,,A*62

I'm parsing latitude and longitude with such a code:

INSERT INTO Track(Point)
SELECT
geography::Point(
SUBSTRING(entry,21,2)+'.'+SUBSTRING(entry,23,2)+
SUBSTRING(entry,26,CHARINDEX(',',entry,26)-26),
SUBSTRING(entry,33,3)+'.'+SUBSTRING(entry,36,2)+
SUBSTRING(entry,39,CHARINDEX(',',entry,39)-39)
,4326)
FROM #MYTEMP

After that when I'm looking into real table (named Track) I see something like that:

Point
0xE6100000010CE200FA7DFF0C4F40B43C0FEECE4A3D40

Those points are placed in the right positions on map (using Spatial results tab in SQL Management Studio) but is there any possibility to see them as normal geographical values in a database?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bananan
  • 613
  • 5
  • 19
  • I came here looking for a method for inserting into a table that uses the geometry type (displayed as the hex value), since SSIS doesn't currently support the transfer of this type of information. Found the answer on the GIS site - https://gis.stackexchange.com/questions/160629/how-to-convert-from-sql-server-geometry-blob-to-something-else?newreg=574a31a90c9142a9b6524ae0dd560d63 – B5A7 Dec 19 '17 at 22:44

2 Answers2

3

Call .STAsText() / .ToString() / .AsTextZM() on the value to see it in human readable form.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

I came here to say what Martin Smith said. However, it's your lucky day. Today is "teach a (wo)man to fish day". Take a look at the geography methods available. Also note in the "See also" section that there are yet more available.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68