topic Attach additional database (DBF) file to existing shape file data. in AutoCAD Map 3D Forum

Attach additional database (DBF) file to existing shape file data.

rl_jackson — Wed, 10 May 2023 20:36:34 GMT

So I have some county records in a drawing with all the associated data from a shape file imported and attached to the polygons. The county has additional data that I would like to connect to this dataset as well. Below is an brief description of the data in a word document provided which states for me to use the FOLIO to join/relate the data to the parcel shape file. How do I go about doing this.

Re: Attach additional database (DBF) file to existing shape file data.

ChicagoLooper — Wed, 10 May 2023 21:36:10 GMT

Hi @rl_jackson

I wouldn't know without looking at the shapefile's dbf and the Word doc.

But if I were to guess, I'd say there's a field in the county's parcel shapefile named FOLIO. Then, in the Word file with the additional data, there's a field that's named FOLIO too.

The data named folio is the KEY that ties the data together, such that Folio 1001 in the county's shapefile is directly associated with Folio 1001 in the Word doc. Folio is a 'unique identifier' and ensures the new/additional Word data is properly linked to the correct shapefile parcel.

If this guess is correct, I don't know how you can link a Word doc to an existing shapefile. I know AutoCAD has an Object Database Connection (ODBC) on the Data Connect palette but it only connects to Excel and MS Access, not Word. I've used ODBC but have always found downloading the driver, and more importantly installing it difficult, especially with so much security software. I always, without exception, submit an IT ticket. BTW, there are multiple ODBC drivers so you'll need to pick the right one or it won't work. (I'm sure YouTube has videos on AutoCAD Map3D and ODBC connections.)

If the county is alluding to the use of Word to make the KEY connection between existing data and a new Word doc, then it might be a method unique to ArcGIS and not possible with AutoCAD. You can also try contacting an Excel guru and ask whether they can move the Word data to Excel so it sits in tidy rows and columns.

I really wish I could help you (the relational data sounds really interesting) but I'm at a loss. Please keep me and the forum posted. Stay awesome and good luck.

Re: Attach additional database (DBF) file to existing shape file data.

rl_jackson — Thu, 11 May 2023 02:54:09 GMT

@ChicagoLooper

So I have to do this in two post due to data limits here. This is what I'm tyring to link to the SHP data

Re: Attach additional database (DBF) file to existing shape file data.

rl_jackson — Thu, 11 May 2023 02:59:18 GMT

So the shp file, which I've extracted some data from, but need the additional data to get a full property label is located here

Public Downloads - Hillsborough County Property Appraiser (hcpafl.org)

The Parcel file is to big to upload.

Re: Attach additional database (DBF) file to existing shape file data.

Pointdump — Thu, 11 May 2023 16:39:20 GMT

Hi Rick,
I'm following with interest, even though this is waaaaaay over my head.
I'm guessing you'll want to do a Join, but it's such a huge amount of data, Map 3D will probably choke.
Dave

Re: Attach additional database (DBF) file to existing shape file data.

Pointdump — Thu, 11 May 2023 20:19:23 GMT

Hey Rick,
I figured out how to do a Join in QGIS. Next I'll work on how to do same in Map 3D.
There were 17 fields in allsales and 47 in parcels. After the Join there are 63 fields.
WeTransfer link:
https://we.tl/t-uXts6n9ov4

Dave

Re: Attach additional database (DBF) file to existing shape file data.

ChicagoLooper — Thu, 11 May 2023 21:33:12 GMT

Hi @rl_jackson

Regarding the Word doc, I see the county's note in bold red '.....You must use a database program to process this file.' I believe they are alluding to MS Access. This is good because an ODBC links Access data to Map3D's data. You already have the shapefile so assuming you've got Access data too, then you're good. Well almost good.

You still need to (1) download the driver and (2) install the driver.

I opened the dbf using Excel (see image below).

Image-1

There are multiple rows with the same PIN #s (FOLIO #s too) that I've highlighted using colors, i.e., gray, yellow, blue, green and orange.

This means all yellow rows in the dbf will be linked to Folio 80000 in Access, all blue rows in the dbf will be linked to Folio 80100 in Access, the green row will be linked to Folio 90000 in Access, etc., etc.

Here's a VIDEO by George Hatch that illustrates a

Hi @rl_jackson

Regarding your data, I see the county's note in bold red '.....You must use a database program to process this file.' I believe they are alluding to MS Access. This is good because an ODBC links Access data OR Excel data to Map3D's data. Assuming you already have the data in Access you're good.

Now you need to (1) download the driver and (2) install the driver.

Next, I opened the dbf using Excel (see image below).

Image-1

There are multiple rows with the same PIN #s (FOLIO #s too) that I've highlighted using colors, i.e., gray, yellow, blue, green and orange.

Here's a VIDEO by George Hatch that illustrates a join. He uses an SDF (instead of a shapefile) named Areas.sdf. His Access file is named ODBC_Demo (Access 2007).

Image-2

His video begins with AutoCAD linework of an office floor plan. Next, he connects to his SDF data (the same floor plan as the Cad linework) which falls squarely on top of the existing linework. To get the 'Create a Join' window (see image-2) he right-clicks the SDF in the map Task Pane. The attribute named REC_ID is the key that effectively combines his SDF data with his Access data.

Notes:

I always put in a ticket so IT can install the ODBC driver. It's a BEAR to install. (Even IT has issues with it.) On top of that, you'll need to 'connect' to your Access file. This is why you need the ODBC driver in the first place--it allows Autodesk program to talk to Microsoft program so they can read each others data.
Your data is very large, ultra large. Is it possible to reduce your data geographically, say nine square blocks instead of an entire county? You can create 'tiles' with each tile containing 9-sq.blocks, then you can only use the area you need. Would that be feasible? Or reduce it just for a trial run?
I'm curious to know if the County's additional data (the Access file) has any coordinates associated with each FOLIO. For example, does it have the, x,y centroid of each parcel? If it does, you've just pulled a rabbit out of a hat b/c there might be a way to join the data without using an ODBC.

Re: Attach additional database (DBF) file to existing shape file data.

Pointdump — Thu, 11 May 2023 23:07:03 GMT

Hi @ChicagoLooper,
ODBC? I'm like Shoot. Me. Now.
In QGIS you just drag-n-drop the DBF file and you're off to the races.
Dave

Re: Attach additional database (DBF) file to existing shape file data.

TerryDotson — Thu, 11 May 2023 23:43:13 GMT

In QGIS you just drag-n-drop the DBF file and you're off to the races.

I agree, QGIS definitely deserves a place on every mappers PC. Of course, having GDAL for a foundation to build on didn't hurt. Highly recommended !!!

Side note about big files and DBF format, that file format is limited to 2GB.

INT_MAX

Maximum value for a variable of type int.

2147483647

Re: Attach additional database (DBF) file to existing shape file data.

ChicagoLooper — Fri, 12 May 2023 13:17:15 GMT

@Pointdump

Agree, ODBC in M3D is clunky. It's just good to know the Autodesk way.

I use Global Mapper, it's easier. Waaay easier cuz it's user friendly.

Another example is BULK COPY from the Map Task Pane. If you want to rename an attribute in M3D you gotta use Bulk Copy. In GM got to table view and right-click the attribute, then rename name it. Done, attribute has a new name.

Re: Attach additional database (DBF) file to existing shape file data.

ChicagoLooper — Sat, 13 May 2023 04:36:47 GMT

@rl_jackson

I performed a JOIN in Map3D using an ODBC. There was some setup involved and even though I can't say it was absolutely needed, I did it that way because that's the way I know how. BTW, I already have the ODBC driver installed.

To begin, I opened All_Sales.dbf using Excel and saved it as an xls.
Next, I made a new ODBC connection. Refer to the Instructions.pdf (attached).
I tried several times to create a join using the entire parcel.shp and All_Sales data but I always got the proverbial spinning blue wheel so I gave up and reduced it so there was only 4,672 records. For comparison, the original had 518,838 rows of data.

This is how the external file was added to the shapefile's data table using a JOIN. Bear in mind, I made a new ODBC connection beforehand.

Image-1Image-2Image-3Image-4Image-5Image-6Image-7Image-8Image-9

Re: Attach additional database (DBF) file to existing shape file data.

rl_jackson — Wed, 17 May 2023 03:07:43 GMT

@ChicagoLooper

This is over the top, I'm not a Mapster (use but don't abuse), I've got some added questions once I've got a chance to test this out, but I'm going on vacation tomorrow (hence my lack of response) and under the gun as usual. I'll revisit this later next week (by then the gun will be at the head). And get back with you about this valuable advice or the viability of me extracting what I need.

I'm only looking for a limited dataset of about 18 miles (route survey of roughly 3k parcels) and only really need 1 column of data from all_sales.dbf, unfortunately they didn't provide that in the shp file.

Cheers and many thanks for guidance. I owe you a beer (or two) at AU.