Investigating the new Spatial Types in SQL Server 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bennie Haelen » Investigating the new Spatial Types in SQL...

16 posts, Page 1 of 2

1 2 »»

Investigating the new Spatial Types in SQL Server 2008 - Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

Bennie Haelen

Posted Thursday, November 20, 2008 12:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 19, 2011 12:16 AM
Points: 4, Visits: 61

Comments posted to this topic are about the item Investigating the new Spatial Types in SQL Server 2008 - Part 2

Post #605616

Duong Qua

Posted Thursday, November 20, 2008 8:00 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 15, 2011 7:30 PM
Points: 13, Visits: 24

Good article. 5 stars from me.

Waiting for ArcSDE interface.

Post #605841

alastair-804470

Posted Thursday, November 20, 2008 12:18 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 10:31 AM
Points: 17, Visits: 105

Another good article. Well done.

Readers might want to note that the spatial reference identifier not only refers to projected spatial reference systems as suggested, but to geodetic spatial reference systems too (i.e. ones based on lat/long coordinates).
In fact, the sys.spatial_reference_systems table is *only* used for non-projected spatial reference systems, since these are required to apply the relevant model of curvature to the earth when using the geography datatype.
When using a projected system based on the geometry datatype, it doesn't matter what SRID is supplied - the results obtained of any methods will be the same.

For any readers wanting a further introduction to different spatial reference systems, and their signficance to the geometry and geography datatype, try:
"Beginning Spatial with SQL Server 2008" (Apress)
http://www.apress.com/book/view/9781430218296

Post #606040

Harry Bouckley-440138

Posted Thursday, November 20, 2008 4:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 5:44 PM
Points: 2, Visits: 12

Thanks for Spatial documents and code. They all show points in text, but I want to take data from one table in sql 2005, which holds X & Y values and create Points in SQL 2008.
For normal data I would write something like :
Insert into NewTable (NewX, NewY) Select X, Y from OldTable
As SQL 2008 uses geometry::STPointFromText('POINT(20 25)', 0), what do I use for a recursive call, I cannot put geometry::STPointFromText('POINT(X Y)', 0).
The only method I have found so far is to extract the data to a text file, include all the SQL commands, then use the file as an SQL file to run. I thought of CTE, but surely I am missing something, it has to be easier.
Except for some grey cells, what am I missing, can you give some examples for a simple transfer?
Thanks
Harry

Post #606178

alastair-804470

Posted Friday, November 21, 2008 1:55 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 01, 2012 10:31 AM
Points: 17, Visits: 105

I think you're making this more complicated than it needs to be! You can construct the WKT representation of a point by concatenating together the string values of each coordinate, and then passing that to the STPointFromText() method, as follows:

INSERT INTO NewTable (point)
SELECT
  geometry::STPointFromText('POINT('+ CAST(X AS varchar(255)) + ' ' + CAST(Y AS varchar(255)) + ')',0)
FROM
  OldTable

Alternatively, if it's only points that you're creating, you'll probably find it easier to use the Point() method instead. Point() accepts two floating point coordinate values directly (X/Y, or Lat/Lng), so you don't have to bother with the CASTing:

INSERT INTO NewTable (point)
SELECT
  geometry:: Point(X,Y,0)
FROM
  OldTable

Note: You should not include a space between the double colons of geometry:: and the word Point in the above example - it's just that I needed to insert the space to stop the emoticon :P appearing!

------
Alastair Aitchison
Beginning Spatial with SQL Server 2008
http://www.apress.com/book/view/9781430218296

Post #606359

Harry Bouckley-440138

Posted Sunday, November 23, 2008 11:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 04, 2010 5:44 PM
Points: 2, Visits: 12

Well, my flabber is absolutely gassed!
I assumed it had to be like that and I can assure you that I tried every combination I could. Obviously not Every combination, I assumed I would need double quotes for the first quotation.
I really appreciate your help, I can now continue with my life and apologise for all those remarks I made about the Microsoft implemtation of Spatial.

Thanks for that.
Harry

Post #607309

Anipaul

Posted Monday, November 24, 2008 3:27 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:50 AM
Points: 4,785, Visits: 1,334

Excellent one...

Post #607393

lucassouzace

Posted Tuesday, January 20, 2009 8:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 PM
Points: 109, Visits: 79

Perfect, but i'm don't understand when you say: "then method Parse is simular STPointFromText" i'm don't understand too, How do say "SRID" im look, but i'm don't understand, thanks a lot! EXCELENTE TOPIC!

Post #639971

tom-1057534

Posted Tuesday, March 31, 2009 1:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 06, 2009 12:35 PM
Points: 1, Visits: 3

Part 1 and 2 were great. Where's part 3??

Post #687394

Bennie Haelen

Posted Tuesday, March 31, 2009 3:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 19, 2011 12:16 AM
Points: 4, Visits: 61

I had an unexpected death in the family, which kept me occupied for quite a while. Look for the remaining articles in the series within the next couple of weeks

Post #687490

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions