Developing and Using SQL Spatial in Autocad Map 3D
Developing and Using SQL Spatial in AutoCAD® Map 3D
Developing and Using SQL Spatial in
AutoCAD® Map 3D
Adam Jonasson – City of Grand Forks, ND
GS4642 The class will look at how to set up and use SQL as your geodatabase. Attendees will see how a data store is created and the many ways that CAD and GIS data can be converted to SQL Spatial. Techniques for converting multiple types of GIS data will be covered as well as tips for best practice. This class will also address how to combine and utilize CAD and SQL data together in production. The class will be taught in a step-by-step process, giving you all of the needed instruction to get started developing in SQL immediately
Learning Objectives
At the end of this class, you will be able to:
· Create an SQL data store
· Use and edit data within the SQL data store
· Edit and maintain SQL schema
· Combine CAD and SQL data together for use in design
About the Speaker
Adam is currently the GIS Coordinator at the city of Grand Forks, North Dakota. Adam has been managing all aspects of GIS development and implementation at the city for over 10 years. He is also a GIS instructor for the North Dakota State College of Science, where he teaches an online class in GIS. Adam has been using Autodesk® products since the early 90s, and integrating AutoCAD® and GIS since the late 90s. Before working for the city, he worked for an IT consultant/Autodesk reseller as an applications technician and trainer, as well as working for Marvin Windows as an architectural project manager. Adam has an associate’s degree in architectural drafting and a BS in geography/GIS.
Creating an SQL Data Store
Load SQL server 2008 on your local PC or server within your network. SQL 2008 is required for spatial data. If you do not currently have an SQL 2008 server running, you can download a trial version or express version at www.microsoft.com/sqlserver/en/us/default.aspx. Both the trial version and express version can be used for testing purposes within AutoCAD Map 3D.
If you have an SQL 2008 server already in production, you can move right to setting up a data store.
To install SQL Express
1. Download install files at www.microsoft.com/sqlserver/en/us/default.aspx
2. Run executable file and install SQL Installation Center
3. From the SQL Installation Center, install based on your preferences
To install SQL 2008 R2 Trial
1. Download full version of SQL 2008 at www.microsoft.com/sqlserver/en/us/default.aspx
2. Run executable and expand files to your PC
3. From the SQL Installation Center, install based on your preferences
Setting up the data store
The first step to getting your data to the SQL server is to setup your data store. A data store is a predefined location on the SQL server that has your coordinate system, name, and project extents defined. You can have multiple data stores on one SQL server.
Note: It is a good idea to gather the mapping extents for your data store ahead of time. This can be accomplished by opening one of your maps in AutoCAD Map 3D and writing down the lower left coordinates and upper right coordinates for your map area.
1. Start Map 3D and open one of your maps
2. Move your crosshair to lower left corner and record the x,y coordinates of your map
3. Repeat for the upper right corner
4. From the Task Pane / Display Manager, click the “Data button” / “Connect to Data”
5. This will open the FDO data connection window
6. From the “Data Connection” window, click “Add SQL Server Spatial Connection”
7. Add a name for the connection
8. Enter the server name for your SQL server and login using either the sa account or windows authentication
9. Once you have logged in to the SQL server, drop the data store dropdown box
10. From the dropdown list choose “Add New Data Store” from the list
11. Enter a Data Store Name
12. Give the Data Store a Description
13. Chose the Coordinate System that will be used for all of the tables in the Data Store
14. In the Data Store extents fields, enter the x,y locations that you recorded earlier from your map
15. Check use FDO enabled scheme
16. Click OK
17. You should get a message letting you know that the data store was created successfully
18. Data store is ready for data
Using and editing data within the data store
Connecting to your SQL data store
1. From the Task Pane / Display Manager, click the “Data button” / “Connect to Data”
2. This will open the FDO data connection window
3. From the “Data Connection” window, click “Add SQL Server Spatial Connection
4. Add a name for the connection
5. Enter the server name for your SQL server and login using either the sa account or windows authentication
6. Once you have logged into the SQL server, drop the data store dropdown box
7. Chose the data store that you created in step 2
8. Click connect
The SQL data store you created is now connected to your map and is ready for data to be added or copied to the data store.
Bulk Copy
Bulk copy is a tool that allows you to copy data from one FDO data source to another. For this example, we will copy data from a shp file to our SQL data store.
1. Start Map 3D and connect to your SQL data store using the FDO Data Connection
2. Using the FDO Data Connection, also make a connection to a shape file (use “add shp connection from FDO)
3. Connect and add the Shape file to you map (should show in map area)
4. From the Task Pane / Display Manager / Click the Data button and choose “Bulk Copy”
5. From the Bulk Copy window, set the From source to point to your Shape File and the Target Source to point to your SQL Data Store
6. From the Source items, select the fields from the shape file that you would like to copy to the SQL data store
7. The name for the target table can be changed by clicking on the default name and changing it to fit your needs
8. Individual filed names can also be changed by clicking on them individually
9. When copy screen is set, click “Copy Now” to start the copy process
The bulk copy will copy all data from one FDO source to another. As soon as the copy completes, the data is available for use in your SQL data store.
CAD data export to SQL Spatial – Map 3D export
CAD data with object data or link templates cannot be converted directly to SQL through the FDO Bulk Copy feature. One way around this is to export your CAD data to a format that can be connected through the FDO data connection. To copy CAD data to a SHP file, we can first use a Map export to export the data to a shape file, then connect and bulk copy.
Map 3D export – CAD data to SHP
1. Open the CAD file that you would like to have converted to SQL
2. Using tool based workspace, select “Output” from the ribbon
3. From the Output Ribbon, select “Map 3D Export”
4. Chose ESRI Shapefile from the type
5. Chose the name and location for your file and click OK
6. From the selection tab choose your object type; point, line, polygon, or text
7. Select the objects either all or manually
8. Filter selections can also be added by layer or object classes
9. Polygon topologies can also be used to select objects
10. Next click on the Data Tab
11. Click select Attributes
12. The attributes window allows you to select which attributes you would like to have exported to your new shp file
13. Attributes can be combined from several different locations on the list
14. By expanding the list of attributes you will see CAD properties, Link templates, object data, block attributes, etc.
15. The Options Tab has a place to set the coordinate system as well as a check box for “Treating closed polylines as polygons.” This check box needs to be checked when working with exporting polygons
16. When all options have been set, click OK
17. A shape file will be created with the data you selected. That shape file can now be connected as an FDO connection and bulk copy used to copy the data to your SQL data store
Note: There are several ways to accomplish getting CAD data to an FDO data store, as well as other tools that can be used. This example uses one technique.
.
Add, edit, and delete data in SQL data store
To modify and add data to your SQL data store, you first need to connect to your SQL server through the FDO connection.
1. With the connection to your SQL server, you should now see the tables that you either bulk copied over to the server or created manually in the schema editor.
2. Once connected to the server, select the layers from the list you would like to work with and click add to Map
3. The layers will load and will be shown in the Task Pane / Display Manager
4. To create a new polygon in the AU_Demo table, right click on the AU_Demo layer in the Display Manager and select Create / new polygon AU_Demo (Your data layer name may be different)
5. Using standard CAD tools draw a closed polygon in the map area and hit enter when complete
6. A polygon has been added to your data store. The table for the data should have popped up as well for further data entry.
7. If you have existing CAD polygons that you would like to add to the data store, you can right click on the AU_Demo layer in the Display Manager and select Create / New Feature from geometry. Select the existing AutoCAD Polygon. (You will be prompted on whether you want to keep or delete the existing CAD polygon.
8. To delete a polygon from the SQL table, simply select the polygon and delete as normal.
9. To edit the shape of the polygon, select as normal and edit using Standard AutoCAD editing or FDO editing tools
10. When you are done editing right click on the AU_Demo layer in the task pane and click Check in Features. This will check in all of the features that you have added or edited.
There are a lot of tools available for editing FDO data sources, we will not go into all of them in this course, but all are available for SQL data objects.
Editing and Maintaining Schemas
Schemas
An important part of understanding how to create and use SQL data is understanding how schemas work and how to add a new table to your SQL database. A schema is a plan for how your data will be held in your data store. The schema editor will let you create, edit, and view your data store and each individual feature class. In this example we will look at an existing table and create a new table in you SQL data store.
1. To view the schema, switch from the display manager to the Map Explorer tab on your Task Pane
2. Select your SQL connection on the top of the list (AU Demo)
3. Click the schema button on the menu at top
4. Click Edit Schema
5. When the schema list comes up expand the lists to see your schemas available as well as feature classes and properties for each
6. A SQL feature class requires two pieces of information; Geometry & FeatId
To create a new SQL Feature Class
1. Open the schema editor and click on the default schema
2. With the default schema selected, click “New Feature Class” on the top menu bar
3. A new feature class will be added to your schema
4. In the “Logical Feature Class” tab enter a name for your feature. This will be the table name in SQL and the name that you will use to recognize this data when you later connect to the data.
5. Once you have given the feature class a name, click on one of the other feature classes in the schema list to set the name for the new feature.
6. Click back on the new feature to select it and click “New Property” at the top of the schema editor and create a new property for “FeatId”. Fill in the form to match the form to the right
7. Click back on the newly created feature in the schema list and check the box next to FeatId under “Specify identifier properties”
8. With the feature selected, click “New Property” at the top of the schema editor again
9. Fill in the information to match the information below
10. The FeatId, Geometry, and feature identifier are the minimum requirements for creating a new feature class. More properties for data collection can be created, but these are the minimum needs.
11. Click OK
12. A new SQL table has been created with the FDO schema needed for spatial data entry.
Combining CAD and SQL in Design
Exporting data from SQL to DWG – FDO import (Best for Map 3D Users)
1. Attach FDO data connection to SQL Spatial database.