Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Investigating the new Spatial Types in SQL Server 2008 - Part 2 Expand / Collapse
Author
Message
Posted Thursday, November 20, 2008 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 10:30 AM
Points: 4, Visits: 63
Comments posted to this topic are about the item Investigating the new Spatial Types in SQL Server 2008 - Part 2
Post #605616
Posted Thursday, November 20, 2008 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, November 20, 2008 12:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 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
Posted Thursday, November 20, 2008 4:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 4, 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
Posted Friday, November 21, 2008 1:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 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
Posted Sunday, November 23, 2008 11:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 4, 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
Posted Monday, November 24, 2008 3:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,371, Visits: 1,391
Excellent one...


Post #607393
Posted Tuesday, January 20, 2009 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, March 31, 2009 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 6, 2009 12:35 PM
Points: 1, Visits: 3
Part 1 and 2 were great. Where's part 3??
Post #687394
Posted Tuesday, March 31, 2009 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 10:30 AM
Points: 4, Visits: 63
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 »

Add to briefcase 12»»

Permissions Expand / Collapse