locked
How to create Geometry data from Easting and Northing points?? RRS feed

  • 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:Tongue TiedTGeomFromText('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,

    Bev



    Tuesday, 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 co-ordinates, BUT you are using SRID 0 (the last parameter supplied to the method)). This means that the given co-ordinate values are not defined in any given spatial reference system. So, the above code will let you create a geometry point at co-ordinates (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 co-ordinates from a projected co-ordinate 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 co-ordinates, 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 Snippet

    UPDATE 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 PM
    Answerer

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 co-ordinates, BUT you are using SRID 0 (the last parameter supplied to the method)). This means that the given co-ordinate values are not defined in any given spatial reference system. So, the above code will let you create a geometry point at co-ordinates (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 co-ordinates from a projected co-ordinate 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 co-ordinates, 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 Snippet

    UPDATE 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 PM
    Answerer
  • "When using the geometry datatype to store co-ordinates from a projected co-ordinate 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 AM
    Answerer
  • Sorry - "When using the geometry datatype to store co-ordinates from a projected co-ordinate 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 AM
    Answerer