Answered by:
How to create Geometry data from Easting and Northing points??
Question

Hi All,
I currently doing some experimenting with SQL Server 2008 and the new FDO SQL Server Spatial driver with Open Source MapGuide.
I have a table of Easting and Northing points, that I wish to convert and insert into a Geometry field, that I have created. I have found a couple of queries on the web, which do this but when I then try to create a layer, from the new geometry data, it either can't read the geometry field, or thinks that there is no data.
I just want to make sure that I have been creating the geometry data correctly, from the easting and northing points.
I have been using the following query:UPDATE TestPoints
SET [Geometry] = GEOMETRY:TGeomFromText('POINT(' + CAST(Easting AS VARCHAR(10)) + ' ' + CAST(Northing AS VARCHAR(10)) + ')', 0)
(Easting and Northing are the two fields with the TestPoints table).
Does anyone know if I am on the right lines with this, or if I am going off in completely the wrong direction??
Any info is greatly appreciated
Thanks,
BevTuesday, September 9, 2008 2:04 PM
Answers

A couple of points:
What you've written appears to be syntactically correct (apart from the smiley, that is!), to create a geometry point at the given Easting and Northing coordinates, BUT you are using SRID 0 (the last parameter supplied to the method)). This means that the given coordinate values are not defined in any given spatial reference system. So, the above code will let you create a geometry point at coordinates (40,20)  but where is this on Earth? It's 40 somethings from somewhere, and 20 somethings from somewhere else, but who knows where?
When using the geometry datatype to store coordinates from a projected coordinate system, you must always supply the EPSG SRID corresponding to the spatial reference system from which they were obtained. So, if these are UK National Grid coordinates, use 27700. If they are UTM zone 35N, use 20135. That is what makes them relate to a given place on the Earth.
Also, you may find it easier to use the Point() method  it has slightly simply syntax nad you can do away with all that CASTing:
Code SnippetUPDATE TestPoints
SET [Geometry] = geometry::Point(latitude, longitude, srid)
(Also  is there something up with the forums today  I had to refresh three times before I could submit this post...)Tuesday, September 9, 2008 2:35 PMAnswerer
All replies

A couple of points:
What you've written appears to be syntactically correct (apart from the smiley, that is!), to create a geometry point at the given Easting and Northing coordinates, BUT you are using SRID 0 (the last parameter supplied to the method)). This means that the given coordinate values are not defined in any given spatial reference system. So, the above code will let you create a geometry point at coordinates (40,20)  but where is this on Earth? It's 40 somethings from somewhere, and 20 somethings from somewhere else, but who knows where?
When using the geometry datatype to store coordinates from a projected coordinate system, you must always supply the EPSG SRID corresponding to the spatial reference system from which they were obtained. So, if these are UK National Grid coordinates, use 27700. If they are UTM zone 35N, use 20135. That is what makes them relate to a given place on the Earth.
Also, you may find it easier to use the Point() method  it has slightly simply syntax nad you can do away with all that CASTing:
Code SnippetUPDATE TestPoints
SET [Geometry] = geometry::Point(latitude, longitude, srid)
(Also  is there something up with the forums today  I had to refresh three times before I could submit this post...)Tuesday, September 9, 2008 2:35 PMAnswerer 
"When using the geometry datatype to store coordinates from a projected coordinate system, you must always supply the EPSG SRID corresponding to the spatial reference system from which they were obtained."
This is not correct. It's always good practice to specify the SRID, but only the geography type requires you to specify a valid SRID. For the geometry type its nothing more than some metadata and could technically be seen as an extra column. For geography however the SRID affects several of the spatial functions like distance, area etc...
Wednesday, September 10, 2008 3:56 AMAnswerer 
Sorry  "When using the geometry datatype to store coordinates from a projected coordinate system, you SHOULD always supply the EPSG SRID corresponding to the spatial reference system from which they were obtained."Wednesday, September 10, 2008 7:44 AMAnswerer