Jump to content
Developer Wiki and Function Reference Links ×

Site Plan & Excel spreadsheet integration


Recommended Posts

Hello,

I have a problem that needs a solution. I am working on an

archaeological site that has about 7800 features ranging from pits to

stakeholes. I've digitized the plans into VW on a Mac and have color

coded the features to indicate function. I also have spreadsheets

that list what was found within the features. I need to get that info

into the plan such that I can do selections of different artifact

types, such as Morrow Mountain type projectile points, chevron

surface treatment pottery, etc. I have created object info boxes that

allow me to enter manually the info. However, the time to enter all

of the info would consume the entire budget. I would hate to have to do Excel searches, then fiddle with selecting objects in VW and color coding them the hard way. Is there a way to get

the info from the spreadsheet to the site plan in the data fields in the object info palette in such a way that I

can do these searches?

I have no Vectorscript skills at all.

Lyle Browning

Link to comment

Assuming objects have names.

Integration? Well, you do have to export as a text file.

And now just out of curiousity: why do you use Excel, not a database? You'd be much better off with FileMaker Pro. Then you could even have integration: FMP could "run" VW and update your VW data without any export. Do the colour coding and what have you.

You could also store the data of every artifact on every dig of your entire career in one database that would, with one click, open the relevant VW file and show it to you.

Link to comment

The objects have numbers assigned, such as Feature 1, Feature 2, etc. I also have the files in FM Pro. But I have no idea how to "run" VW from it. I'm afraid I have no idea how to implement what you describe and would love to know how to make it happen.

In effect, what I'd like to have is the ability to search a database and have VW display those items so that I could either copy them or color code them to extract meaningful interpretation from their spatial distribution.

Link to comment

I'm not sure how to 'run' VW from an outside data base, but what you asked for in your original post can be done. It would be anywhere from difficult to impossible depending on the structure of your spreadsheet, and the structure of your drawing. I think this is what Petri is talking about regarding names.

The script would need to read a line of the spreadsheet and know which object to assign that info to. So if you have given each of the features a name that exists in that row, you're most of the way there.If there is co-ordinate info the row, and it correlates to the 'feature's' position in the drawing, that may work as well. If you created the objects in EXACTLY the same order they are listed in the spreadsheet, that could also be used.

Some other things about your task that aren't totally clear to me. Is there more than one 'artifact' in each 'feature'? Are these represented as objects in the drawing? Will each 'artifact' have a record attached, or is there one record attached to the feature with a field for each artifact. How many columns are in the spreadsheet? Will every cell need to be read into a record?

Once you have the record info properly placed, you could do your searching within VW. For example, you could have the script assign classes to the artifacts and turn them on and off using simple class visibilities. You could use the built in custom selection command to create scripts to select objects that have record fields that match whatever criteria ("Select all objects whose record field Type is 'Chevron Surface'") and then do whatever.

Just some ideas, some of which you've probably considered. In any case, this script would be specific to your task and how you have set things up to this point. I'm sure you're not too pumped about redrawing 7,800 polygons!

If you have no knowledge of V-Script, or no experience with another language, I would venture to say that it could take you almost as long to get a working tool as it wouldl to manually enter all the info. But once you have learned how to build this tool you will be able build many others.

Does your budget allow for hiring a programmer?

In all sincerity,

Charles

Link to comment
The objects have numbers assigned, such as Feature 1, Feature 2, etc.

And these are actual "object names"? (Data pane of Object Info.)

I also have the files in FM Pro. But I have no idea how to "run" VW from it. I'm afraid I have no idea how to implement what you describe and would love to know how to make it happen.

In FMP, you need to have a "calculated field", which "writes" a VectorScript based on data contained in the record(s). The script is then sent to VW via an AppleEvent.

Link to comment

The script would need to read a line of the spreadsheet and know which object to assign that info to. So if you have given each of the features a name that exists in that row, you're most of the way there.

Each feature has a number/name

If there is co-ordinate info the row, and it correlates to the 'feature's' position in the drawing, that may work as well. If you created the objects in EXACTLY the same order they are listed in the spreadsheet, that could also be used.

I can obtain coords from the centers of each of the feature. the order is not exactly the same as the creation sequence

Some other things about your task that aren't totally clear to me. Is there more than one 'artifact' in each 'feature'?

Yes, there are multiple artifacts per feature. But the way I've set up the records, it's presence/absence that I'm after rather than numbers.

Are these represented as objects in the drawing?

No.

Will each 'artifact' have a record attached, or is there one record attached to the feature with a field for each artifact.

One record is attached to the feature with fields for artifact types.

How many columns are in the spreadsheet?

Less than 10

Will every cell need to be read into a record?

No, there are multiple blank cells.

Once you have the record info properly placed, you could do your searching within VW. For example, you could have the script assign classes to the artifacts and turn them on and off using simple class visibilities. You could use the built in custom selection command to create scripts to select objects that have record fields that match whatever criteria ("Select all objects whose record field Type is 'Chevron Surface'") and then do whatever.

That's the game plan and I'm in the middle with no idea of how to make it so.

Does your budget allow for hiring a programmer?

Not really. This project evolved as the investigation proceeded and the information contained therein came into focus after looking through the artifacts themselves and after digitizing the "flat" map to try to get some meaning out of the thousands of little polygons.

Lyle Browning

Link to comment
I can obtain coords from the centers of each of the feature. the order is not exactly the same as the creation sequence

With the FileMaker Pro -approach, things like coordinates could be read from/calculated in the VW file and stored in the database. This might include z-level. So it's a two-way system.

If I were you, I'd go for FMP instead of the (convoluted and error-prone) text file. Scripting one or the other is by and large as easy.

Link to comment

Lyle

How many artifact Types are there in total?

My guess is there's more than one artifact type associated with each feature....is that correct? In this case I would think class visibiliy would be fairly useless, since each feature could only have one class/type.

Could you have layers for each type and turn polygons off and on with saved views? A script could quite easily duplicate and move polys to their appropriate layers. This way a feature could have more than one type associated.

If all you finally wish to achieve is the ability to turn polygons on and off according to their type, this might be worth considering. For one thing it would eliminate the need for records. I don't know if you want them in the drawing for some other purpose though.

Sorry to be asking so many questions, but they are all pertinent to the task. The first step is to understand precisely what you are trying to achieve. Do you wish to do more with the VW file than simply make various polygons appear and disappear according to their type?

Charles

Link to comment

Back to the records. My understanding so far is that we have a drawing with 7,800 polygons in it, each with one record already attached and a name showing in the data pane. I'm wondering about that record format. It sounds like you have some number of fields named by type. These are boolean fields then?

If I'm right so far, the script looks at a polygon's name, finds it's name in the database, looks to see what artifact types are associated with that name and ticks off the appropriate fields in the polygon's record. If that's all we're doing this should be fairly straightforward.

Petri: Have you ever built an array with 7,800 locations? I've never worked with one that big and don't know the implications.

Charles

Link to comment

OK then...

Lyle: If you would like some help, email the drawing and excel file to chacroATshaw.ca.If you're working in VW12 you'll need to export it as a V11.

I have a bit of spare time and the weather report for the next few days isn't too enticing. Maybe I can do something for humanity and learn a thing or two as well.

Charles

Link to comment

Charles

I am not sure why would you need an array unless you want to read all the excel file at once. What I have done before is export the excel file as csv (comma separated value), open the file in vectorscript, read one line at a time, do whatever needs to be done with that record, and when done with all records, close the file. This would avoid any memory limitations inherent with arrays and would be able to handle any number of records. Granted, it may be slower than other methods but on the other hand, the script will not crash as it has happened to me by using arrays and in particular, dynamic arrays.

Miguel

Link to comment

Thanks Miguel. The array just happens to be the way I've done it before and so I already have some of those routines. In this case there's no sorting, so you're right, it isn't actually needed. And you touch on my concern about size. It sounds like this would be 7,800 x 10. I've never been in that territory. If he sends me the stuff this may be one of the things I'll learn about!

Link to comment
  • 1 month later...

If you have no knowledge of V-Script, or no experience with another language, I would venture to say that it could take you almost as long to get a working tool as it wouldl to manually enter all the info. But once you have learned how to build this tool you will be able build many others.

Does your budget allow for hiring a programmer?

I'm looking for a VectorScript programmer to create a simple plug-in for drawing a certain type of polyline for me. Are you available for hire for such a task? If not can you refer someone?

Link to comment

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
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...