如何将DWG文件导入SQL Server空间数据类型列（SSMS无第三方工具方案）

Alright, let's walk through how to get your AutoCAD DWG files into SQL Server's spatial columns, including a method that uses only SSMS (no third-party tools required).

1. General Approach to Import DWG Files into SQL Server Spatial Columns #

SQL Server doesn't natively support the DWG format, so we first need to convert the spatial data in the DWG into a format SQL Server understands (like WKT, WKB, or GML). Here's the step-by-step workflow:

• Step 1: Export DWG spatial data to a compatible format
  Use AutoCAD's built-in tools to extract spatial objects and save them as WKT (Well-Known Text), GML (Geography Markup Language), or a CSV with coordinate data that you can later format into WKT. For example:

  • Run the DATAEXTRACTION command in AutoCAD, follow the wizard to select your target spatial objects, and export the data to a CSV file that includes each object's coordinate information (you can manually structure this into WKT strings if the export doesn't do it automatically).
  • Alternatively, use the EXPORT command in AutoCAD and select GML as the output format to generate a file with spatial data in GML.

• Step 2: Prepare your SQL Server table
  Create a table with a spatial column (either geometry for planar data or geography for geodetic data). Example:

  CREATE TABLE DWGSpatialData (
      ID INT IDENTITY(1,1) PRIMARY KEY,
      SpatialObject geometry,
      ObjectName NVARCHAR(100)
  );
  

  Replace geometry with geography if you're working with geographic coordinates, and adjust the SRID (spatial reference ID) in later steps to match your coordinate system (e.g., 4326 for WGS84).

• Step 3: Import and convert the data
  Once you have your WKT/GML/CSV file, import it into SQL Server and convert the text-based spatial data into the geometry/geography type using SQL Server's built-in spatial functions:

  • If using WKT:

    -- Assuming you've imported the WKT data into a temporary table #TempDWG
    INSERT INTO DWGSpatialData (SpatialObject, ObjectName)
    SELECT geometry::STGeomFromText(WKTString, 4326), ObjectName
    FROM #TempDWG;
    

  • If using GML:

    INSERT INTO DWGSpatialData (SpatialObject)
    SELECT geometry::GeomFromGml(GMLString, 4326)
    FROM #TempGMLData;
    

  You can use tools like SSMS's Import/Export Wizard, BULK INSERT, or OPENROWSET to load the initial text data into a temporary table first.

2. SSMS-Only Method (No Third-Party Software) #

This method relies on AutoCAD's native export tools (which aren't third-party) and SSMS's built-in features to complete the import:

• Step 1: Extract DWG data to WKT/CSV using AutoCAD
  Open your DWG file in AutoCAD and run the DATAEXTRACTION command. Follow these steps in the wizard:

  1. Select "Create a new data extraction" and name your extraction.
  2. Choose the objects you want to extract (filter by spatial object types like lines, polygons, etc.).
  3. In the "Select Properties" step, check boxes for coordinate-related properties (e.g., Start X/Y, End X/Y for lines; Vertex X/Y for polygons).
  4. Export the data to a CSV file. Then, manually edit the CSV to format each row's coordinates into valid WKT strings (e.g., LINESTRING(10 20, 30 40) for a line, POLYGON((0 0, 0 10, 10 10, 10 0, 0 0)) for a square).

• Step 2: Import the CSV into SQL Server via SSMS

  1. In SSMS, right-click your target database > Tasks > Import Data to launch the Import/Export Wizard.
  2. Select "Flat File Source" as the data source, browse to your edited CSV file, and configure the column settings (make sure the WKT column is set to a text type like NVARCHAR(MAX)).
  3. Choose your target database as the destination, and map the CSV columns to a temporary table (e.g., #TempDWGImport).
  4. Run the wizard to load the CSV data into the temporary table.

• Step 3: Convert and insert into the spatial table
  Execute a SQL query to convert the WKT strings into spatial objects and insert them into your permanent table:

  INSERT INTO DWGSpatialData (SpatialObject, ObjectName)
  SELECT 
      CASE 
          WHEN WKTString LIKE 'POINT%' THEN geometry::STGeomFromText(WKTString, 4326)
          WHEN WKTString LIKE 'LINESTRING%' THEN geometry::STGeomFromText(WKTString, 4326)
          WHEN WKTString LIKE 'POLYGON%' THEN geometry::STGeomFromText(WKTString, 4326)
          ELSE NULL
      END AS SpatialObject,
      ObjectName
  FROM #TempDWGImport
  WHERE WKTString IS NOT NULL;
  

  Adjust the SRID (4326 in this example) to match your DWG's coordinate system.

Key Notes #

• Always verify the SRID matches between your DWG's coordinate system and the SQL Server spatial column—mismatched SRIDs will cause conversion errors.
• Double-check the WKT syntax for complex objects (like multipolygons) to ensure they're valid (SQL Server will throw an error if the WKT is malformed).
• If you're using the geography type instead of geometry, use geography::STGeomFromText() instead of the geometry equivalent.

内容的提问来源于stack exchange，提问作者ThinkTank