Jump to content

Guidance Required: Link to Database and Populate Drawings with Data


nick777

Recommended Posts

I have some files (OK, 11,000 of them) and they have drawings on the bottom and text on the top. I want to erase this text and replace it with data that comes from a database. I don't know where to start.

 

I need to:

1. preferably find a way to erase the old data on the top, but I could do it manually

2. Insert data from specific fields into specific spots on the drawings.

3. Each drawing would have the same fields but different data corresponding to an account number. The account number is included in the name of the drawing and is a field in the database.

 

Any idea on how to get started? I have Map3D as well, which I was told would help work better than regular AutoCAD on this, but I also have regular AutoCAD. I can't even figure out how to make the database connection work.

Link to comment
Share on other sites

Hi,

can you attach an example, preferably with before and after showing as much detail as you can of the requirements.

Link to comment
Share on other sites

cor, two cad people from Norwalk, CT. I am sure you meet the other one soon...

 

Your problem is not easy but not impossible either.

 

We do something similar with our loop drawings where we take the design saved as a DXF (a text file) which contains the name of a database field. I have written a VB program that opens the DXF file and fills the data retrieved from a database and creates a new DXF file. I cannot give you this program but that is one possible approach if you have an in house programmer. AutoCAD is only used to create the initial DXF and to convert the final DXF into a DWG.

 

I think something similar can be written in VB.Net and the clever people here can probably do it in LISP.

 

As for erasing the old data, if you know the area that is common to all drawings it is ideal for a script. A script is a text file that contains a list of commands as if typed at the keyboard. Create a PLAIN text document (notepad is ideal) and save it with a SCR extension. It can be run using the SCRIPT command or by dragging the file in to the drawing area. It would look something like....

 

ERASE
10,100
500,200
(this is a blank line, don't type anything)

This will erase everything contained within a rectangle from 10,100 to 500,200. Obviously these points will need adjusting for your drawing. The extra blank line is to end selection and commands must be typed in full. And test it first on copies, not originals until you are happy.

 

As steve says, a file and fuller description would help a lot.

Link to comment
Share on other sites

Hi,

can you attach an example, preferably with before and after showing as much detail as you can of the requirements.

 

Here's a file that is edited to produce each of the hydrant cards. I have like 800 of these and I have a very similar one for water services. They are all DWGs.

 

I think all the text is just text, not mtext. These were all converted over from an ancient version of Microstation and I've done a little scripting on them to try to standardize the drawings as far as names, layers, etc.

 

I'd like to use that ERASE code provided, but I'm somewhat concerned that there might be a few drawings that have the central line moved up to accomodate a larger drawing portion, and I don't want to erase any of that. I guess I could just run it with conservative numbers and if I find extra text in there, I could delete it manually later.

 

As far as the data insertion, the top portion basically shows the places where I want the data, and the field names as well. Data for regular water services will come from a single table, while for the hydrants, one portion comes from a table and one portion would come from a related table to that table (hydrant flushing test results for that hydrant).

 

I want to connect to an ArcSDE database (I'm using ArcGIS 10.1 SP1), and even that fails epicly. I'm trying to figure out the work around for that, but that's failing as well. It seems like stuff like this should be so common that a megacorporation like AutoDesk would have it figured out, especially when they claim to have a GIS product like Map3D.

 

I thought that Map3D could do stuff like this easily... that's what a guy from AutoDesk told me, so I bought the software. Was I hoodwinked? I know very little VB, probably not enough to get this to run.

Blank.dwg

Link to comment
Share on other sites

Ok slightly different approach than Dave's above, Presumably all the drawings are based on the same layer system so attached is a script that moves all the unwanted objects from layer "0" onto a new layer labelled "temp",it then turns that layer off, it also turns off the layer "level 1" with the unwanted text, this means you won't be deleting the wrong things. It leaves the border alone you could change the script to turn that off as well. If it doesn't do anything unexpected then you could also later delete the unwanted layers (but check everything first on a copy of the drawings).

If you can get your database into Excel then we could also figure out a script to enter the new text, but that does require knowing the exact position to place text in the drawing, and having the source laid out in a logical fashion.

And if your feeling really adventurous (lazy) then a batch file could run this script on all 11,000 files, but that is beyond the scope of a quick fix.

Hydrants.scr

Link to comment
Share on other sites

OH YEAH been there done that, then did it again, I once ran a script for 2 days solid on 10,000 files only to find nothing had happened, I always copy all files into a backup folder, and preferably onto a second hard drive, then run tests on small samples.:huh:

Link to comment
Share on other sites

the trouble is you can almost guarantee that the sample set will be the only 10% that actually work correctly.

 

not really relevant here but I have also been caught out by the answers to questions being different if you step through the commands on screen to if you run a script!

 

If you try the -PLOT command, it gets to the end and asks "discard changes" or "save changes" depending on your method. That took a long time to find.

Link to comment
Share on other sites

Would it not also be possible to block the rest of the sheet with a base point at the top left, inserting the text relative to that basepoint? This would give a conssistent basepoint from sheet to sheet.

Link to comment
Share on other sites

I'd like to be able to get rid of the text based on the layer, but I cannot, because the actual content in the layers is not standardized. The drawings came from Microstation, which had layers that were just a number. The original intent was to put certain content in certain layers, and the consultants who did the original digitizing from paper records did this, however, the editors did not, putting text and drawing elements in whatever layer happened to be open at the time. Thus, while there is a layer for the "top text," I might also have other drawing bits in that layer. Conversely, the top text in a particular drawing might be in another layer instead, such as "hydrant," or something.

 

So I set it up to fix it over time, but because the standard was not adhered to, I'd find it hard to fix it through layer selection. I think I'm better off doing the erase by location since the border and size of each drawing is fixed.

Link to comment
Share on other sites

Yes, I could export any of the data to Excel, but what I really want to do is to live link the data so that whenever the drawing is opened, it goes to the database and populates the fields based on the most current version of that data, so I'd never have to update it again.

 

If I have to do something like the Excel method, I suppose a script that I could run on all the drawings to update them would work, and I could run this every so often, but it's not ideal.

Link to comment
Share on other sites

ah, if the information is available in an EXCEL file look into the datalink. You can set up a two way link which allows either side to update the information. Obvioulsy there are controls to ensure they play nicely but it is possible.

Link to comment
Share on other sites

OK, I could probably write a Python script to have ArcGIS export an Excel file and overwrite the existing one, set it to do this every night or something. And use that link command.

 

But editing the drawing would edit the Excel file, which would be temporary. That's OK, I'd just do all the editing in the database anyway. But is it possible to eliminate the Excel middleman and connect directly to a SQL Server database instead, use that data directly?

 

I've been trying to set up a connection in Map3D, and there is an option for ArcSDE (which I'm using that connects to SQL Server), but people have driver problems when using 64-bit. It seems as though it's possible to resolve the issues, but I haven't yet.

Link to comment
Share on other sites

Shame about the layers, but it would be feasible to combine the two ideas and use Dave's selection method and move all the items to a new layer and turn it off, at least for an initial run just to make sure you are not deleting drawing material. Have you tried the forums from arcGis it does look as though there is specialist software for linking directly to Autocad, and it would appear strange to try and figure out all sorts of workarounds to get everything into excel, if it can be done directly.

Link to comment
Share on other sites

I haven't checked the Esri forums yet because what I want to do is edit the drawings. Where it's actually coming from shouldn't matter, whether it's Oracle, SQL Server, etc. I figured I'd start here, but maybe there is some discussion there.

Link to comment
Share on other sites

It IS possible to integrate an AutoCAD drawing to an access database as I've seen it done. We decided to use the database to populate a set of drawings as a snap shot in time and only create new drawings when a new database was issued. This was mainly as we didn't need "live" data but it was also more complicated than we wanted.

 

One of the programming gurus (Lee Mac springs to mind) may have some alternative ideas. PM him and ask if he will look at this thread to suggest alternative solutions to the database connection.

Link to comment
Share on other sites

I wasn't trying to get rid of you :), this is without doubt the best place to ask about editing the drawings, but I took a quick look on the Esri site and they have a free application for directly connecting their database to Autocad, I just figured someone on the forums there maybe able to help as well, that way you get the best of both worlds.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...