Subscribe to this thread
Home - General / All posts - ViewBots in M9
dchall8
544 post(s)
#11-Feb-19 23:21

A couple of my people asked if I could label the soils parcels in our office database system so they could type the values into a spreadsheet and calculate the potential livestock stocking rate.

Quick background: each type of soil from rubble to loamy clay can grow a certain amount of forage per acre. We have a soil map with those numbers, and we have acreage, and we know it takes 10,000 pounds of production to support 1 Animal Unit. 1 AU is defined elsewhere beyond the scope of this. By running the math we can determine how many AU a rancher should have on his property to justify a special appraisal. If he does not have that many AU, then he's not serious and will not get the special appraisal.

I decided that process was too mechanical and fraught with peril, so I included the calculations in M8 to use with ViewBots. It looks great in M8 and gives them a picture they can save, but they don't have M8 and aren't likely to get it. M9 Viewer would be perfect but no ViewBots yet.

This image shows all the soil types, production values, and AU potential for each type of soil in a 168-acre parcel. The ViewBot sums the values in the selected properties.

It turns out that taking this out of Manifold to our office GIS (emasculated ESRI), the process goes back to a manual one where they have to do the addition even though the calculations are done. I would like to hook the users up with M9 Viewer, but there is still no ViewBot ability. I don't think it's kosher to ask when something is going to happen in a beta development, but I'm walking the line between teaching them one way and then changing it or holding off teaching them how to do it while waiting for the really cool way to come about.

Is this something I should take up with the ESRI support folks, or is it possible that ViewBots are in the near future in M9? This is not urgent, but it has high level attention.

Attachments:
AU calculation in ViewBots.jpg

lionel

557 post(s)
#12-Feb-19 00:32

from manifold 8 documentation

ViewBot Operators

View - Panes - ViewBots

ViewBots


join image

"Because my dad promised me" ( interstellar ) but blackhole don't exist

best hardware with no ads focus on quality features price like manifold see xiaomi

Dimitri


5,249 post(s)
#12-Feb-19 08:28

Pretty much anything you can do in a Viewbot you can do in a query in 9, and you can also do a whole lot more. Keep in mind you can create tables and drawings from queries, which of course also means you can create labels from queries. See the Example: Create a Drawing from a Query topic.

So you could have a map where depending on what they enter into a table or what they select the resulting drawings change, and even where the resulting label layers change, for example, to print out as a label in the map window the result of some computed field expression. Can't do that in 8, where they have to look at the viewbot readout to see a result. The worst case is that after changing something they might have to click Refresh to have the display update. Create the project in 9 and all of that will work in Viewer too.

dchall8
544 post(s)
#13-Feb-19 17:34

As I show my people what I can do, they are evolving what they want. They really like the idea of a spreadsheet "to show the calculations." Never mind that the calculations don't show. They were skeptical of the calculations when I showed it to them in Manifold. Weird. So I did the calculations in M8 and copied the table to Excel. They seem happier with Excel. Here's what that looks like...

Excel works fine to filter down to the ID number level and use the SUBTOTAL function in row 1 to get the calculated numbers they need. They plan to screenshot that as I did and create a PDF file linked to the appropriate account in our office database. They also want an image of the parcels from the map. Google Earth does not lend itself to this imagery. They like the way the colors show through in M8, but they do not like the way the colors are entirely pinked out in M9 Viewer (ref images in this forum topic).

So you could have a map where depending on what they enter into a table or what they select the resulting drawings change, and even where the resulting label layers change, for example, to print out as a label in the map window the result of some computed field expression. Can't do that in 8, where they have to look at the viewbot readout to see a result.

Although they were skeptical of the computations at first, they did like seeing the results combined into the M8 Viewbots pane.

Attachments:
Spreadsheet showing Ag information.jpg

dchall8
544 post(s)
#14-Feb-19 22:46

I've taken this a little bit further, but I'm not where I want to be. What I want is totals and what I've settled for so far is subtotals in the table where the highest subtotal is also the total. But even the subtotal field is not populated the way I expected. Here's the image of the table...

Here's the Query

-- $manifold$

--

-- Auto-generated

-- Transform - Add - Update Field

--

PRAGMA ('progress.percentnext' = '100');

UPDATE (

  SELECT [mfd_id],

    [Subtotal],

    [AU] + [AU] AS [n_Subtotal]

  FROM CALL Selection([Soils Table]TRUE)

  THREADS SystemCpuCount()

SET [Subtotal] = [n_Subtotal];

The records are filtered by owner name and then sorted on PROP_ID first and AU second. Records are selected by the PROP_ID number. Then the query is run to add the value in AU to the previous sum of of AU values. Looking at the subtotal field as it is populated by the query, the numbers do not add up the way I expected. What I expected was to see the AU from each record added to the sum of all the previous records. A quick inspection shows that isn't the case. I don't see a way to sort the records that would make the list in the order shown in the subtotal field.

Also the query that Manifold writes performs the update on the records. How do I modify the query so as to not update and have those values disappear when different records are selected? I still like the way Viewbots work in M8, so I'm trying to replicate something like that.

And one last question, is it possible to rearrange the fields from left to right? I thought that feature would be in the Schema, but that supposedly only works for new fields...although I did not find it to work for new fields either.

Attachments:
M9 Subtotal Table.jpg

tjhb

8,516 post(s)
#14-Feb-19 23:40

Then the query is run to add the value in AU to the previous sum of of AU values.

Well, your query does not do that. There is no concept of "previous sum" in the query.

Consider the line

[AU] + [AU] AS [n_Subtotal]

What is the value of [AU] + [AU]? It is twice the value of [AU], for the current record. Exactly the same as 2 * [AU]. There is no reference to any other record.

But it is more complicated than adding a join to the previous record, as you could do in Manifold 8. In 9, records are not ordered. Their content is not evaluated (or updated) in any defined order, except by accident. Assume that they are accessed and updated randomly.

For Manifold 9, a running total needs a COLLECT construct. [Correction: "needs" is too strong. It can be done without that.]

If you post example data, I or someone else can show how to do it for Manifold 9.

tjhb

8,516 post(s)
#15-Feb-19 00:02

More succinctly:

Because there is no inherent order between records in Manifold 9, a running total needs to reference all preceding records, not just one.

(This is actually standard. The Manifold 8 approach worked in practice, but not in SQL theory.)

dchall8
544 post(s)
#15-Feb-19 16:47

Very helpful and motivating, thanks. That certainly explains why the numbers weren't coming out right.

After going back to the drawing board, I think I have exactly what I wanted with a Viewbot(ish) looking panes. By detaching all the elements and rearranging, I can make images like this.

The two queries are as follows...

SELECT

  [PROP_ID],

  RoundDecs(sum([Acreage]),2) as Acres, RoundDecs(sum([Acreage]*[Dry-Weight Production Lbs per Acre]),0) as DryPounds, RoundDecs(sum([AU]),2) as AnimalUnits

 

  FROM CALL Selection([Soils Table]TRUE)

GROUP BY [PROP_ID];

--and to make a grand total row

SELECT

  [file_as_name] as OwnerName,

  RoundDecs(sum([Acreage]),2) as Acres, RoundDecs(sum([Acreage]*[Dry-Weight Production Lbs per Acre]),0) as DryPounds, RoundDecs(sum([AU]),2) as AnimalUnits

 

  FROM CALL Selection([Soils Table]TRUE)

GROUP BY [file_as_name] ;

Is there a way to make the total values appear at the bottom of the subtotal tables all in one query?

Attachments:
M9 Subtotal and Total Table.jpg

KlausDE

6,282 post(s)
#15-Feb-19 17:13

Yes. I insert results into a table and insert the total in a next step. As I often analyse selected regions I can use the query repeatedly and export results for a propperly formated Excel sheet. If you link the export in a second and fixed Excel sheet you don't need to repeatedly set formats, number of decimals, frames, multilined headings again and again.

dchall8
544 post(s)
#15-Feb-19 19:28

That's good news. Can you please elaborate on how to make the total into a next step? Simply putting a space between one select and the other is not the answer.

Regarding Excel: There are 64,000 records in the Soils Table. I have tried running filters in Excel and it doesn't like that many records. It is possible to use, but I'm not the one using it. If my boss sees "Not all rows are showing" in red text, she's going to fold her arms and back away. Manifold doesn't care about 64,000 records. That's why I brought the entire db into Manifold. Plus I didn't want the appraisers to be able to break the file. Excel formulas are easy to break.

This is the perfect applet for Manifold Viewer. Plus it gets my people using Manifold Viewer, which is what I would like them to use on a daily basis instead of Google Earth. Ten minutes ago the ag appraiser was afraid of it. Now she's giddy with excitement over the prospect of never having to hand write the old calculations with the associated scanning scratch notes to jpg. Plus she has the bonus of a map of the property being analyzed.

tjhb

8,516 post(s)
#15-Feb-19 20:48

You can UNION the two results together.

--SQL9

SELECT

    [file_as_name] AS OwnerName,

    [PROP_ID],

    RoundDecs(SUM([Acreage]), 2) AS Acres, 

    RoundDecs(SUM([Acreage] 

        * [Dry-Weight Production Lbs per Acre]), 0) AS DryPounds,

    RoundDecs(SUM([AU]), 2) AS AnimalUnits

FROM CALL Selection([Soils Table], TRUE)

GROUP BY [PROP_ID]

UNION ALL

SELECT

    [file_as_name]-- OwnerName

    CAST(NULL AS INT32), -- PROP_ID (NB match data type)

    RoundDecs(SUM([Acreage]), 2), -- Acres 

    RoundDecs(SUM([Acreage] 

        * [Dry-Weight Production Lbs per Acre]), 0), -- DryPounds

    RoundDecs(SUM([AU]), 2) -- AnimalUnits

FROM CALL Selection([Soils Table], TRUE)

GROUP BY [file_as_name]

;

For UNION to work, the fields in the tables must match in number and in data type. That is the reason for including [file_as_name] above (you could substitute an empty string), and for including a NULL PROP_ID value below (you should check the required data type). Alternatively, you could combine these into one field, if you don't mind returning PROP_ID as a string.

Field aliases should only appear in the first table. They are ignored in subsequent tables, even if you make an error, so it is better to omit them (possibly with a comment to help readability).

Generally use UNION ALL rather than just UNION, unless you need to exclude duplicates. (It is faster not to check for duplicate records, just return everything.)

KlausDE

6,282 post(s)
#15-Feb-19 22:37

Here is another solution that outsources the final formating to Excel. It may be easier to copy results into documents this way.

Open the project.map in manifold32. There is a map to manually select what you need and a query [query] that stores results in a [Soils Group Table].

There is a little inconvenience in 9.0.168.8 in that the result table seems to be empty when you run the query from project pane or if you comment out the last SELECT statement in the query.

Yet everything is there as you can see if you just copy/paste the result table. It's only a missing refresh of the table window and I guess just the current state of development.

So simply export the [Soils Group Table] using the default name'Soils Group Table.xls'

Now you can open this file in Excel and additionally open 'Soils Group final.xlsx'. You will find the export in the a formating once and for ever prepared in this file and ready for delivery.

There is not much use in cutting off decimal places in manifold as long as you want numeric values to be numeric values. In the example I have used FLOAT32 data type because it is so sensitive but FLOAT64 is not much better for human readability. That's why I leave this to Excel completely.

Aliases are not necessary here. The serie of fields in the INSERT handels this.

A final note: the query starts dropping the previous result so that it can be recreated. If there is no result table than the query stops with a not very verbose error.

Attachments:
Soils Group final.xlsx
Soil_Totals_and_Subtotals.mxb

tjhb

8,516 post(s)
#15-Feb-19 22:50

There is a little inconvenience in 9.0.168.8 in that the result table seems to be empty when you run the query from project pane or if you comment out the last SELECT statement in the query.

Dan and I have reported this Klaus. (I've just forwarded you the report.)

It applies to any table or drawing that it is open when a query is used to drop then and recreate it. The window becomes orphaned, and does not find its new parent until it is closed then reopened.

(It applies whether the query is run from the Project pane or its own window. I don't think the last statement in your query is material, but could be wrong.)

KlausDE

6,282 post(s)
#15-Feb-19 22:50

BTW you can see the System Data folder pinned as the first folder in alphabethic order. So it will not get lost in all the folders that you create.

This is done simply by prepending a non-printable character in the german UI file.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.