Jump to content
  • 0

Worksheet Question - How to get 'Location' is within 'Area 1' is 'True' ?


yasin2ray

Question

I hoping to set up this worksheet with columns that indicate location of Site Features instead of using row criteria to group Site Features by location. Currently, I can set the Location in each database row to select criteria that is "Location is within Outfall Area #1" or "Location is within Outfall Area #2". But I'd rather create more of a table with Xs in the cell indicating if a Site Feature is within Outfall Area #1 or 2, or both 1 and 2 (an X in each column).

 

What do I need to set in the column cell so that it will populate the cells below with an X (for true/yes) or no X (for false/no)?

 

THANKS! This will help me with so many worksheets in the future.

Anna

ps. Don't worry about what an outfall/outfall area is if you don't know. Just think of this as an area. Having said that, if you are employed by Vectorworks, please learn about outfall areas, drainage areas, watersheds, etc and add knowledge/capabilities to Vectorworks Landmark!!!!!!!!!! Thank you.

Screenshot-Worksheet_how to get_Location is within Outfall 1 is True.jpg

Link to comment

23 answers to this question

Recommended Posts

  • 0

This is currently not possible with worksheets.

 

As you discovered, you can set a criteria to show all the items in a location, but you can't ask set a database call to give you the location for any given item.

 

I suspect there is something fundamental going on.  If you make a database with only one location as a criterion, the dialog box doesn't indicate how many items meet the criterion before you hit OK, like it would do if you just asked for objects on a layer or objects of a certain type.  So maybe it doesn't even know until the worksheet runs?

 

I think your solution is going to involve either a very clever use of the data manager (which I can't quite visualize yet) or one or more simple little scripts.  Or both :-).

Link to comment
  • 0

Here is the worksheet script you need. Copy and Paste this into a new Vectorscript named IsLoc and run it using a formula of:

 

=RunScript('IsLoc', 'Name Of Your Area')

 

If this is something you like, I can help you turn this into a text file and store it in the right place (User folder or workgroup folder) so it is accessible in all of your VW files without having the script in each one. The downside to this is if you send the file to another VW user who does not have the script it will not calculate properly.

 

Procedure IsLoc;

VAR	Result:	Real;
	NameOfLoc, CriteriaToEvaluate: String;
	WSSubrowObjHand: Handle;

BEGIN
	WSSubrowObjHand:=WSScript_GetObject;
	NameOfLoc:=WSScript_GetPrmStr(0);
	CriteriaToEvaluate:=Concat('((LOC=',NameOfLoc,'))');
	Result:=Eval(WSSubrowObjHand, CriteriaToEvaluate);
	If Result=1 Then
		WSScript_SetResStr('X')
	ELSE
		WSScript_SetResStr('');

End;

Run(IsLoc);

 

And Michael, you know the rule.  If you say you can do it with a script you need to put up at least some pseudocode to make it look like you know what you are talking about. 😉

Link to comment
  • 0
18 hours ago, Pat Stanford said:

Here is the worksheet script you need. Copy and Paste this into a new Vectorscript named IsLoc and run it using a formula of:

 

=RunScript('IsLoc', 'Name Of Your Area')

 

If this is something you like, I can help you turn this into a text file and store it in the right place (User folder or workgroup folder) so it is accessible in all of your VW files without having the script in each one. The downside to this is if you send the file to another VW user who does not have the script it will not calculate properly.

 

Procedure IsLoc;

VAR	Result:	Real;
	NameOfLoc, CriteriaToEvaluate: String;
	WSSubrowObjHand: Handle;

BEGIN
	WSSubrowObjHand:=WSScript_GetObject;
	NameOfLoc:=WSScript_GetPrmStr(0);
	CriteriaToEvaluate:=Concat('((LOC=',NameOfLoc,'))');
	Result:=Eval(WSSubrowObjHand, CriteriaToEvaluate);
	If Result=1 Then
		WSScript_SetResStr('X')
	ELSE
		WSScript_SetResStr('');

End;

Run(IsLoc);

 

And Michael, you know the rule.  If you say you can do it with a script you need to put up at least some pseudocode to make it look like you know what you are talking about. 😉

Pat,

Are you saying I need both of these things together to make it work? I would add the formula to the cell and also run the script? What's the process/steps for that? I've never used a script in tandem with a worksheet.

 

Here is an image of my updated worksheet where I'm trying to get the Xs to work using the IF statement. Can I have more than one in a cell?

If 1 it shows an X, if 2 it shows a dash.

I'm still trying to figure out what to do when something is within both Area 1 and Area 2.

THANKS! I'll start trying your function and vectorscript right now.

ps. I'd love to know how to save the script as a resource in our Workgroup folder accessible through the Resource Manager, if that's what you meant.

Screenshot-Worksheet_how to get_Location is within Area=X.jpg

Link to comment
  • 0

What I sent is a Worksheet Script.  It allows the full functionality of a script, but will only run from inside a worksheet. You create the script and store it in a file. You then use the a formula in the Database Header Row of  =RunScript('Your Script Name') [and possibly additional parameters].  Every time you recalculate the Worksheet the script will run automatically for each object that meets the Database Criteria. So you don't have to manually run the script.

 

As to your other functions, Stormwater.Outfall does not appear to be a LOC but a record.field combination.

 

You can use nested IF statements, but your bigger problem is that you are doing an exact comparison.  The equals "=" means does the record.field combination contain exactly the value provided.  It does not work in your case because you are trying to compare values that can have multiple values.

 

For VW2022 a new function was added that will allow you to find a substring that should do what you want.

 

Try a formula of:

 

=IF(FindPattern('1', 'Stormwater'.'Outfall')>=0, 'X', '')

 

FindPattern returns the location of the substring in the string in the second parameter. It is "Zero Bases" meaning the first location is 0. If the substring is not found it return -1. So by checking if the location of the substring is greater than or equal to zero (>=0) you can tell if the substring exists.  If the substring is contained in the second parameter then it returns the value 'X'.  If it is not contained then it returns an empty string '' (two single quotes next to each other).

 

The above will work fine as long as there is only one instance of the character to find in the field.  If there are multiple uses (such as 1, 11, 199 which would all match above) then the "Pattern" (in this case the single character to find) will need to get more complicated to make sure you only match exactly what you are looking for. But that become a class in rational expressions and GREP. Which I am happy to help with when you need it. Or you can find huge amounts of information on the web.

 

I will post later about storing Worksheet Scripts in the user/workgroup folder.

Link to comment
  • 0
18 hours ago, Pat Stanford said:

Here is the worksheet script you need. Copy and Paste this into a new Vectorscript named IsLoc and run it using a formula of:

 

=RunScript('IsLoc', 'Name Of Your Area')

 

If this is something you like, I can help you turn this into a text file and store it in the right place (User folder or workgroup folder) so it is accessible in all of your VW files without having the script in each one. The downside to this is if you send the file to another VW user who does not have the script it will not calculate properly.

 

Procedure IsLoc;

VAR	Result:	Real;
	NameOfLoc, CriteriaToEvaluate: String;
	WSSubrowObjHand: Handle;

BEGIN
	WSSubrowObjHand:=WSScript_GetObject;
	NameOfLoc:=WSScript_GetPrmStr(0);
	CriteriaToEvaluate:=Concat('((LOC=',NameOfLoc,'))');
	Result:=Eval(WSSubrowObjHand, CriteriaToEvaluate);
	If Result=1 Then
		WSScript_SetResStr('X')
	ELSE
		WSScript_SetResStr('');

End;

Run(IsLoc);

 

And Michael, you know the rule.  If you say you can do it with a script you need to put up at least some pseudocode to make it look like you know what you are talking about. 😉

Pat,

I was at first getting this error message, see attachment "IsLoc error message", before I realized I was using the wrong name for my area.

Then I corrected the name and now I'm getting the error message shown in "Screenshot_IsLoc error message when using correct name.jpg".

(by the way, I changed all the places you had NameofLoc to the name of one of my locations. I used "Outfall Area 1 query")

Screenshot_IsLoc error message.jpg

Screenshot_IsLoc error message when using correct name.jpg

Edited by yasin2ray
forgot to attach second image
Link to comment
  • 0
1 hour ago, Pat Stanford said:

Go back to the original script I posted.

 

The name of the location you want to use is not changed in the script. It is a parameter that you pass to the script and is included as part of the worksheet function.

 

=RunScript('IsLoc', 'Put the Name of the Loc HERE Enclosed In Single Quotes')

 

😉

Pat,

The name of my area is the object/IFC name of a polygon. Is that okay? I re-copied and pasted your script and double checked everything, and nothing is populating. The cells are blank. See attachment. What am I missing?

Anna

Screenshot_IsLoc not populating_blank.jpg

Link to comment
  • 0

Based on what I can see from your screenshot, it looks like everything is correct.

 

Are the objects that are showing up as subrows in the database actually WITHIN the polygon named 'Outfall Area 1 query'?

 

Let's make sure the script is working properly.

 

Create a new layer.

Draw a polygon on that layer and name it in the OIP to be Fred.

Draw some other objects that are completely inside, completely outside, and partially within the polygon.

Create a new worksheet. Create a database row and give it a criteria of Layer is whatever your new layer name is.

 

Put a formula in column A of the database header row of =ObjectTypeName

Put a formula in column B of the database header row of =RunScript(IsLoc, 'Fred')

 

When the worksheet recalculates do the objects inside of Fred show and X in column B?

Link to comment
  • 0
23 hours ago, Pat Stanford said:

Based on what I can see from your screenshot, it looks like everything is correct.

 

Are the objects that are showing up as subrows in the database actually WITHIN the polygon named 'Outfall Area 1 query'?

 

Let's make sure the script is working properly.

 

Create a new layer.

Draw a polygon on that layer and name it in the OIP to be Fred.

Draw some other objects that are completely inside, completely outside, and partially within the polygon.

Create a new worksheet. Create a database row and give it a criteria of Layer is whatever your new layer name is.

 

Put a formula in column A of the database header row of =ObjectTypeName

Put a formula in column B of the database header row of =RunScript(IsLoc, 'Fred')

 

When the worksheet recalculates do the objects inside of Fred show and X in column B?

Pat,

Looks like it's working, see attachment.

What is your script expecting? Perhaps the way it is set up does not work with the way I have things set up.

Anna

Script-Test_Fred_working.jpg

Link to comment
  • 0
On 7/15/2022 at 4:20 PM, Pat Stanford said:

The script is expecting you to pass it the name of the LOC. It will then check the object in that subrow of the database. If the object is contained by the LOC object it will return an X. If it is not contained by the LOC object it will return an empty string.

Pat,

It's still not working in my project file. Any idea why?

The database row is set to select criteria from a record format: =DATABASE(NOTINREFDLVP & NOTINDLVP & ('Stormwater'.'Site Features'<>' - '))

Anna

Link to comment
  • 0
25 minutes ago, Pat Stanford said:

What type of objects are being returned in the database subrows? Do any of those objects actually "exist" in such a way that they are definitely inside the named poly you have defined as the LOC?

Pat,

Many objects are fully in Area 1 or Area 2. Some do cross both.

See attachment. I added an orange arrow pointing to the pink line. The pink line is actually two complete polygons and at this point zoomed in, you can only see the line where they are adjacent. Area 1 is on the left, 2 on the right.

Example, the two dark pink rectangles in the upper right are dumpsters. Those should easily be captured by Area 2.

Anna

worksheet script issue_demonstrate-objects fully in Area 1 or Area 2.jpg

Link to comment
  • 0

It is not going to work well the way you are using the polygons to define the areas.  I believe that in order for for an object to be recognizes as being WITHIN a LOC, that at least one vertex (likely more) of the object have to be within the area.

 

If you modify your polygons so that instead of being a line, they are instead the area to the left and right of your "line" then you should get what you want. 

 

I don't know of any way (without a lot of scripting and very slow execution time) to return if an object is left or right of a polygon.

Link to comment
  • 0
13 minutes ago, Pat Stanford said:

It is not going to work well the way you are using the polygons to define the areas.  I believe that in order for for an object to be recognizes as being WITHIN a LOC, that at least one vertex (likely more) of the object have to be within the area.

 

If you modify your polygons so that instead of being a line, they are instead the area to the left and right of your "line" then you should get what you want. 

 

I don't know of any way (without a lot of scripting and very slow execution time) to return if an object is left or right of a polygon.

I guess I did not explain that well. They are two large, closed, polygons. See attachment of zoomed out view. I selected the two polygons so they are highlighted. They share a common boundary, which was the "line" you could see in the previous screenshot.

 

worksheet script issue_demonstrate-two full closed polygons.jpg

Link to comment
  • 0
32 minutes ago, Pat Stanford said:

If you would like to DM me the file I would be happy to take a look.  It appears that you have everything set up properly.

 

You saw the script worked in your test file. No reason I can think of it should not work in your real file.

I just sent you a Message and included everything I think you'll need. Thanks so much, this feature/capability is SO needed!

Link to comment
  • 0

Programming error. Or maybe I will call it a documentation error.

 

You used a long name for your LOCs with spaces in the name. When I tested I only used a short name of a single word.  The original version of the script did not consider this and did not enclose the LOC name inside of single quotes to make it work with spaces in the name.

 

The revised version below does.  Just replace the code of the script with a copy from this post and I believe it will work properly.

 

Sorry about that.  🙈

 

Procedure IsLoc;
{July 18, 2022}
{©2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}

VAR	Result:	Real;
	NameOfLoc, CriteriaToEvaluate: String;
	WSSubrowObjHand: Handle;

BEGIN
	WSSubrowObjHand:=WSScript_GetObject;
	NameOfLoc:=WSScript_GetPrmStr(0);
	CriteriaToEvaluate:=Concat('((LOC=',CHR(39),NameOfLoc,CHR(39),'))');
	Result:=Eval(WSSubrowObjHand, CriteriaToEvaluate);
	If Result=1 Then
		WSScript_SetResStr('X')
	ELSE
		WSScript_SetResStr('');

End;

Run(IsLoc);

 

Link to comment
  • 0
21 hours ago, Pat Stanford said:

Programming error. Or maybe I will call it a documentation error.

 

You used a long name for your LOCs with spaces in the name. When I tested I only used a short name of a single word.  The original version of the script did not consider this and did not enclose the LOC name inside of single quotes to make it work with spaces in the name.

 

The revised version below does.  Just replace the code of the script with a copy from this post and I believe it will work properly.

 

Sorry about that.  🙈

 

Procedure IsLoc;
{July 18, 2022}
{©2022 Patrick Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

{No Warranty Expressed of Implied. Use at your own risk.}

VAR	Result:	Real;
	NameOfLoc, CriteriaToEvaluate: String;
	WSSubrowObjHand: Handle;

BEGIN
	WSSubrowObjHand:=WSScript_GetObject;
	NameOfLoc:=WSScript_GetPrmStr(0);
	CriteriaToEvaluate:=Concat('((LOC=',CHR(39),NameOfLoc,CHR(39),'))');
	Result:=Eval(WSSubrowObjHand, CriteriaToEvaluate);
	If Result=1 Then
		WSScript_SetResStr('X')
	ELSE
		WSScript_SetResStr('');

End;

Run(IsLoc);

 

Aha, I thought it would be something like that.

It is working and IT. IS. FABULOUS!

I'm going to write up an explanation and create a Wish List post for this feature. This alone, this simple thing, is important for landscape users.

I have one more thing to ask if you can do. An improvement on this. For a polygon that spans two query areas, do you know of a way to output the square footage of the partial polygon per area?

For example, a 250 square foot polygon spans Area 1 and Area 2. Instead of outputting an X in each query column for present/not present, the square footage of each would be displayed. Say, 200sf in Area 1 and 50 sf in Area 2.

Is this possible, by script or other method??

Anna

Link to comment
  • 0

Your request for the area within a LOC is probably scriptable, but it is not going to be easy.  Let me think about it and see if I can come up with a reasonable to to do it.  Off the top of my head I can't even think of an easy way to find objects that are only partially in a LOC, except for a case like yours where your have two LOCS that have a coincident edge and you could look for objects that claim to be in both LOCs.  But I know I could come up with a case where the LOC shape is such that even though an object appears to be in both LOCs, VW will not show them as such.

 

Link to comment
  • 0
On 7/19/2022 at 12:41 PM, Pat Stanford said:

Your request for the area within a LOC is probably scriptable, but it is not going to be easy.  Let me think about it and see if I can come up with a reasonable to to do it.  Off the top of my head I can't even think of an easy way to find objects that are only partially in a LOC, except for a case like yours where your have two LOCS that have a coincident edge and you could look for objects that claim to be in both LOCs.  But I know I could come up with a case where the LOC shape is such that even though an object appears to be in both LOCs, VW will not show them as such.

 

Pat,

I'm not sure what you mean in your last sentence. I'll try to clarify what I'm looking for just in case I wasn't clear. It would be the "same" as what is working now, except instead of outputting an X for 'present', it would output the area quantity. This is the square footage of the part of the object that falls within the Query Area selected for that column. Let me know if that helps or changes your take on it.

Thanks again!

Anna

Link to comment
  • 0

Hi Anna,

 

Unfortunately, your explanation is exactly what I thought you were asking for. 😞

 

As I said, there is no easy way to do this, especially with different object types that react differently. Lines are different that Polygons are different than walls, etc.

 

My current thought is that I would have to duplicate the objects, Clip them with a line delineating the break between LOCs, get the area of the resulting object, delete the resulting object and repeat for every object in the drawing.

 

I don't have time or interest to try and make that work in a semi-bulletproof fashion right now.  Sorry.

 

 

The last line of my post was intended to describe this situation:

image.png.cce0eed1128c3efabcd1fc024de8c5bc.png

 

Since LOC uses the center of the object to determine where an object is, the Blue Rectangle if ONLY in the LOC of the circle on the left. The Red Rectangle is ONLY in the LOC of the white rectangle with the cutout for the curve of the circle on the right.

 

I don't know of a way around that using simple methods.

 

Be careful in how you use the script if you really need to identify objects in two (or more) LOCs.

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
Answer this question...

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