AutoCAD Map 3D Forum
Welcome to Autodesk’s AutoCAD Map 3D Forums. Share your knowledge, ask questions, and explore popular AutoCAD Map 3D topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

SQL Server Spatial - Geometry Types

13 REPLIES 13
Reply
Message 1 of 14
Baz_man1
1403 Views, 13 Replies

SQL Server Spatial - Geometry Types

We are testing a SQL Server Spatial Connection that has been built to replace a SDE Connection.

After I log in with the proper credentials I see all of the features available. When I select a feature to bring in and 'Add To Map' it comes in with 3 datatypes visible in the Task Pane, point. line and polygon. If I try to style one (if the data type is really a line for instance) I only get a preview for the line and point but the style dialogue area has a polygon styling option (by default?). The same holds true for a point data type. If I bring in a polygon, it still shows all three data types but I am able to style the polygon.

I looked at the schema and it lists all three data types as available.

We have ESRI users on the same test database and they do not have this problem. A point comes in only as a point, a line comes in only as a line, and a polygon comes in only as a polygon.

 

Anyone else encounter this and have a fix? It's almost if the data source needs to be defined at the server level.

 

See the attachments for visual illustration.

13 REPLIES 13
Message 2 of 14
Murph_Map
in reply to: Baz_man1

Unlike an ESRI shp file the SDF format allows mulitpe types in one file by default that's why tou have the pline,point and polygon types listed. You can create a new SDF schmea for each type and set the geometry as you need then do a bulk copy from the orginal if all you want to see is the one type.

Murph
Supporting the troops daily.
Message 3 of 14
AlbertKalman5692
in reply to: Baz_man1

The MS SQL geom can store point,line,polygon type of geometry. As ESRI as Autodesk to limit use only one type of geometry, therefore is need to create the schema in those software. I think in ESRI have some where a schema definition structure and when was create the database the geom type was declared. But because if proprietary to ESRI the Map provider can't read this schema structure (I think).

 

Also, if you choose to create your own schema in Map for MS SQL you can have option to declare type of feature class but in this case Map will create some additional table F_*** in MS SQL.

 

Personally I not use because in practically is not useful. If after some time you want to create a query this not be available without some hacking in database (you need to insert some records  in F_*** table ) but is not very easy.

My opinion (I have a very huge practical experience), if you deselect  "Show in layer list" those type of feature that you are not interest everything will be ok.

 

The user must be carefully when he want to create new feature, unfortunately  he can create all tree type (six) off feature therefore to prevent this we choose to create trigger in database to not permit insert wrong type of feature.

 

The big advantage of this type using MS SQL is the we can create(edit) any time query (view) and this became available.

 

 

 

 

 

Albert Kalman

 

 

 

 

Message 4 of 14
maherich
in reply to: Baz_man1

hi I am not sure if I got you right. But you want to style your layers that are loaded as multigeometry into Map. What you get in style dialog is always polygon styling. If that's the problem, then simply scroll down in the styling dialog. You will find the point and line options there.

Martin

Message 5 of 14
Baz_man1
in reply to: maherich

Thank you for that. I didn't notice it since when we were running our SDE we never had to do that.

That is a temporary solution but I still don't know why all three types are showing up when our ESRI users get the proper type from the same database.

Message 6 of 14
Baz_man1
in reply to: AlbertKalman5692

Thank you Albert and Murph.

This might be a solution but it is one that would have a lot of administration and essentially duplicates a database that has a lot of different datasets. These same datasets get updated either quarterly, semi-annually or annually which means a bulk copy each and every time.

The item I find most curious is why this happens on SQL Server Spatial and not on the SDE we previously had access (until release 2013 came out).

I reiterate that our ESRI usrs do not have to deal with superfluous data types showing up. Why can't either the database (schemas on the server?) or Autodesk make the default data types show as they should.

Message 7 of 14
Murph_Map
in reply to: Baz_man1

Your ESRI users are using an stystem that can only hold one geo type in their database file/table. Where as AutoCAD SDF can hold all three types of geomerty. As to why, think if you're a civil designer using civil3d to design a storm water system, theres pipes, hmanholes and holding pools.  To export to ESRI shp format that's 3 types with at least 3 files each or 9 files not counting the .prj files. With Autodesk's SDF you get the same data/info with just one file. If you need to share that data with others would you want to send one file or 12 files.

Murph
Supporting the troops daily.
Message 8 of 14
Murph_Map
in reply to: Baz_man1


@Baz_man1 wrote:

. Why can't either the database (schemas on the server?) or Autodesk make the default data types show as they should.



It can if the user knows how to export/create the data.

Murph
Supporting the troops daily.
Message 9 of 14
AlbertKalman5692
in reply to: Baz_man1

In Map and MS SQL is possible to manage all data as SDE

 

1. Create a data store step1 using FDO enabled schema

2. Create feature class as you need with proper geom type (point)

3. Create feature class as you need with proper geom type (line)

 

 

Message 10 of 14
AlbertKalman5692
in reply to: Baz_man1

4.  In MapSpace I can get only one type of feature class

5. In the MS SQL I have more F_* tables

6. In such a table the geometry type is manege by provider if this type exist.

 

I'm very sure also SDE have the same structure the problem is when we connect to MS SQL the Map provider recognize only Autodesk structure schema therefore we see all tree type geometry the SDE structure.

 

If you choose to use MS SQL Server  in the same way as SDE I think is need to rebuild the database structure for Map provider,  but I did not understand well what you want to do "bulk copy each and every time".  There are some others options or tricks but is important to know what we want or the work flow.

 

 

 

Albert Kalman

 

Message 11 of 14
JJuniewich
in reply to: Baz_man1

In the style editor uncheck show in Layer List

Message 12 of 14
Baz_man1
in reply to: AlbertKalman5692

Albert,

When we had the SDE running and when we added a geometry type that was say, a line, ONLY a line datatype would appear in the Task Pane window. The exact same data is loaded on to the SQL Server Spatial database and we get all three (2 are invalid) data types showing in the Task Pane. Our ArcGis users get only the proper data type once they load the same data without having to set a schema. The underlying question is why?

(The suggestion of doing a bulk copy is not practical as we get data updates very often which means we would have to bulk copy those to stay current. Further, why would we want to spend twice as much time administering two databases when the CAD and GIS users can share one?)

Message 13 of 14
Baz_man1
in reply to: Murph_Map

We in the CAD group do not export data to create our server database. We get the overwhelming majority of our shared (between GIS and CAD) data from outside (vendor) sources. We had this same data on a SDE database connection that we used to be able to access (before Civil 2013). Now, when we access the SQL Server Spatial database we get the three data types. In other words we didn't have to set up schemas on the SDE (or even burden ourselves with a bulk copy) and why is it we can't get the single data type as the ArcGis users do? They don't 'bulk copy' the data from the server either in the SDE or SQl Server servers. Why should we?

Message 14 of 14
AlbertKalman5692
in reply to: Baz_man1

Feature classes stored using the Microsoft geometry or geography type in a SQL Server DBMS

 

 

Beginning with ArcGIS 9.3, geodatabases stored in a SQL Server 2008 database can use the Microsoft geometry or geography storage types for feature storage. Feature classes that use one of these storage types also have a business table, as described in the previous section. However, for this storage type, the business table contains a column of type GEOMETRY or GEOGRAPHY, in which the feature class geometry is stored; there is no associated feature table.

 

 

Feature class tables that use geometry or geography storage also have associated system tables plus associated views. Click the following link to see a diagram of a feature class that uses the Microsoft geometry or geography storage type.

  

 

In the diagram, the feature class business table, buildings, is linked to the system tables by way of the table name
 
You have in SDE schema definition for features, therefore ArcGIS user can see only one feature class type.
If you connect with Map Spatial connection to MS SQL Server the map 'can't read" this schema structure from SDE, therefore Map ignore it and show you all three  types.
 
Did you try use ARCSDE or ARCGIS provider ? Is available for 2013 version of map but as I know 'Read only'.
 
As I understand that GIS user works with ArcGIS and CAD user works Map(or Civil) and for CAD user is forbidden to modify data? Is need only to read the data from SDE ?
 
Albert

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost