locked
Using SQL Server 2008 spatial data types and ZM polylines RRS feed

  • Question

  • I am attempting to edit/create features in SQL Server 2008 (ESRI's SDE) using the geometry data type. I can create 2D point and line features no problem and they show up in ArcGIS. However, 3D lines and points with ZM values are much trickier. I can put a point record in:

    use GeometryTesting
    go
    insert into ZMPOINTS (OBJECTID, SHAPE)
    values (1, geometry::STGeomFromText
    		('POINT(1703722.03 12853873.58 1175.94 1176)', 8))

    and all is good:

    SELECT TOP 1000 [OBJECTID]
          ,[SHAPE].AsTextZM() as c
      FROM [GeometryTesting].[dbo].[ZMPOINTS]
    
    -->1	        POINT (1703722.03 12853873.58 1175.94 1176)

    But these features will not show up in my GIS - its not liking something about the ZM values I think. Same thing with ZM lines:

    use GeometryTesting
    go
    insert into [dbo].DirsXYZM (ObjectID, PA_Well_ID, SHAPE)
    	select 4, 9542, geometry::STGeomFromText(
    		'LINESTRING(
          1703721.05 12853884.74 0.00 0,
          1703721.23 12853883.50 286.00 286,
          1703721.45 12853881.12 468.98 469,
          1703721.93 12853877.69 789.96 790
    			)',
    		9
    	)
    I get valid line records in my table, but they won't show up in my GIS maps.

    What is the support level of SQL Server for ZM points and lines? WKB doesnt support ZM values, right? Again, I can do this all day with 2D points and lines and they show up just fine in my GIS app. I really need to be able to add ZM line records to my table.  Any thoughts appreciated.

    Thanks

    chad

    Monday, April 5, 2010 5:05 PM

Answers

  • Hi Chad,

    As you've noticed, most of the spatial methods do not support Z and M.
    Neither STAsBinary (WKB) or STAsText (WKT) will put out Z and M values. Your
    choices are to use the AsTextZM method, get the Z and M values separately,
    or use SQL Server's own binary format. But if you want to display these in
    SDE, it seems as though SDE would have to support one of those formats.

    Cheers,
    Bob Beauchemin
    SQLskills

    Monday, April 5, 2010 6:36 PM
  • Hi super-cooper,

    For information about how sql server stores geometries, read this:

    http://blogs.msdn.com/isaac/archive/2009/08/10/spatial-data-type-structure.aspx

    Tuesday, April 6, 2010 6:10 AM

All replies

  • Hi Chad,

    As you've noticed, most of the spatial methods do not support Z and M.
    Neither STAsBinary (WKB) or STAsText (WKT) will put out Z and M values. Your
    choices are to use the AsTextZM method, get the Z and M values separately,
    or use SQL Server's own binary format. But if you want to display these in
    SDE, it seems as though SDE would have to support one of those formats.

    Cheers,
    Bob Beauchemin
    SQLskills

    Monday, April 5, 2010 6:36 PM
  • Bob, thanks for the reply. What do you mean by "use SQL Server's own binary format"?
    Monday, April 5, 2010 6:53 PM
  • Hi super-cooper,

    For information about how sql server stores geometries, read this:

    http://blogs.msdn.com/isaac/archive/2009/08/10/spatial-data-type-structure.aspx

    Tuesday, April 6, 2010 6:10 AM