locked
Possible to mix sql2008r2 with autocad drawings? RRS feed

  • Question

  • Hi all,

    I have autocad drawings of my offices, which have storerooms in various locations. I have a database of all the items that are stored in the storerooms. This inventory is periodically counted, items removed, put in, etc..

    I'm wondering if there's anything "cool" that could be done by way of mixing the two, resulting in various sorts of heat maps indicating inventory activity, what's been counted and what hasn't, and so on.

    I'm not familiar with autocad - just came across the drawing files today and wondered what sorts of things were possible along these lines.

    Thanks for any ideas,

    sff

    Friday, May 18, 2012 2:15 AM

Answers

  • Hi,

    The AutoCAD DXF format stores vector data, not dissimilar to the way in which data is stored using the geometry datatype in SQL Server. I'm not quite sure if you're proposing loading the AutoCAD data to SQL Server or the other way around, but I'll assume the former. I know that Safe FME lets you read/write data between DXF and SQL Server, and I'm sure there are probably other tools that do the same (or you could write your own loader app using the SqlGeometryBuilder class).

    Once you've got the layout of your storerooms and your inventory in the same database, you can use any of the geometry methods to do cool stuff. ("Cool", I suppose, is a relative concept!):

    • You could use STDistance() to work out the minimum distance from any part of the building to the closest supply of paperclips.
    • You could use ShortestLineTo() to create a direct line path for restocking a storeroom from the depot
    • You could implement k-means clustering to identify clusters where surplus of stock was based, or areas that were poorly stocked.
    • ... ummm.

    One thing to note is that SQL Server is good for storing, retrieving, and querying spatial data, but it is, after all, a database, not a presentation tool. So while it can do the slicing 'n' dicing to create the dataset for an inventory activity report, if you want to display that as a snazzy heat map, animated time patterns etc. then you'll need to use some other tool as well (and I'm not sure that AutoCAD is it, either)


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Friday, May 18, 2012 7:49 AM
    • Marked as answer by sherifffruitfly2 Friday, May 18, 2012 4:40 PM
    Friday, May 18, 2012 7:17 AM
    Answerer
  • Well, if you don't want to splash out for Safe FME, you could try experimenting with http://www.guthcad.com.au/freestuff.htm, which claims to have an app that extracts coordinates from a DXF file - it's be pretty easy to go from there to WKT which you can supply to SQL Server.

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Friday, May 18, 2012 3:48 PM
    Answerer

All replies

  • Hi,

    The AutoCAD DXF format stores vector data, not dissimilar to the way in which data is stored using the geometry datatype in SQL Server. I'm not quite sure if you're proposing loading the AutoCAD data to SQL Server or the other way around, but I'll assume the former. I know that Safe FME lets you read/write data between DXF and SQL Server, and I'm sure there are probably other tools that do the same (or you could write your own loader app using the SqlGeometryBuilder class).

    Once you've got the layout of your storerooms and your inventory in the same database, you can use any of the geometry methods to do cool stuff. ("Cool", I suppose, is a relative concept!):

    • You could use STDistance() to work out the minimum distance from any part of the building to the closest supply of paperclips.
    • You could use ShortestLineTo() to create a direct line path for restocking a storeroom from the depot
    • You could implement k-means clustering to identify clusters where surplus of stock was based, or areas that were poorly stocked.
    • ... ummm.

    One thing to note is that SQL Server is good for storing, retrieving, and querying spatial data, but it is, after all, a database, not a presentation tool. So while it can do the slicing 'n' dicing to create the dataset for an inventory activity report, if you want to display that as a snazzy heat map, animated time patterns etc. then you'll need to use some other tool as well (and I'm not sure that AutoCAD is it, either)


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Friday, May 18, 2012 7:49 AM
    • Marked as answer by sherifffruitfly2 Friday, May 18, 2012 4:40 PM
    Friday, May 18, 2012 7:17 AM
    Answerer
  • Thanks for reply!

    And yes, the idea was to somehow cram the autocad into the database, where the inventory data already lives.

    "Once you've got the layout of your storerooms and your inventory in the same database..."

    Aye, that's the trick I was wondering about from a technical standpoint.

    And I like the travelling salesman style questions that could be answered, once all the data is in the same place.

    And yah, I understand that sql per se. My thought was to use ssrs and/or sharepoint for that. But of course that requires a way to hook up to the autocad file data (either directly connecting to the file, or translating/importing it into sql).

    Thanks for the ideas!

    sff

    Friday, May 18, 2012 2:28 PM
  • Well, if you don't want to splash out for Safe FME, you could try experimenting with http://www.guthcad.com.au/freestuff.htm, which claims to have an app that extracts coordinates from a DXF file - it's be pretty easy to go from there to WKT which you can supply to SQL Server.

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Friday, May 18, 2012 3:48 PM
    Answerer
  • coolness - thanks for the pointer - i'll see what i can whip up!

    -sff

    Friday, May 18, 2012 4:35 PM