X-Queries with building only in Excel template panel area

Len Geisler

I want to create an Excel template panel with specific functionalities. I want to Query, analyse and update buildings (which does not include roads etc. i.e. "panden") within specific areas or neighborhoods and only those areas/neighborhoods. This has to be done using an Excel template panel. Doing the analyses in a non-template Panel would exceed the 60k query record limit for larger projects and thus only update part of the buildings. Therefore splitting them up per, for example neighborhoods, would allow the analyses to be split over multiple Excels, therefore never hitting the limit and still work in larger projects.

As an example of the required query. I want all the names of the buildings with a BAG_ID within a neighborhood. I create a template excel panel, add these to all neighborhoods. Within the Excel there is the named value with an expected Query like this:

Here the ID refers to the Panel ID (and BAG_ID is the attribute to be recognized with the XA query). What this however gives back is always the name of the Panel. Not the name of the building. The same applies if I reverse the statement (first NEIGHBORHOOD then BUILDING). This also results for the actual more detailed queries which all return 0.

I see that the preview has the XC-statement option. Which allows for the centerpoint "within" selection.

This in theory does exactly what I need but however no longer allows the XA-statement to only filter on the BAG_ID and thus only the buildings (houses etc.). This query will also include all roads, trees, gardens etc.. Creating update statements on this again creates a large amount of undesired updates. The filter on attribute, thus excluding all roads, gardens, trees etc., is essential for proper functioning. Statements like these (which is not allowed) would be logical:

An Excel IF formula with ATTRIBUTEISBAG_ID is not ideal because you'll still update values back to buildings where you don't want the attribute to exist. Like a road.

The "name" and only buildings (BAG_ID) example is to make it easy to understand. The actual use cases requires statements more like this (and even more complex versions) and also other filters like OBJECT_FLOW_ID:

or

Can this be done and if so what statements would I need?

RudolfNL

Hey Len,

at this time it is not possible to combine the functionalities of the XA and the XC query. I've taken a bit to think about other ways to construct this mechanism using the tools currently available, but I think that without significant pre-processing to force a desired selection, the most feasible option is to use an XC statement and retrieve all constructions.

One quick test on (for example) Wageningen seems to indicate that any given neighborhood will only realistically amount to a few thousand constructions. The highest I spotted was about 4200 constructions for any given neighborhood. For each construction, make a check whether there is a known BAG-ID for those constructions, and then for each constructions either perform some calculations leading to a non-zero output (if they have a BAG-ID), or just output a 0.

At very very large scales of calculations or projects, performance limits might come into play, but at this time I would recommend creating a sheet which at the very least retrieves all the data you desire, add that to a template excel, and apply it in a project with half-a-dozen neighbors or so. This will provide a much more concrete insight into the performance implications.

Len Geisler

Hi Rudolf,

Thank you for the quick response. I expected this to be the case. I'm going to try the "all building" option. Not ideal but indeed should work. I think it might be a solid addition to the query options. If I got time I'll put it in the idea push. There are a lot of applications where a filter on both location and attribute are use full.

Just to be sure to limit the UPDATE-queries. Is there a value I can give an Excel-cell so that it does not send an update value back to Tygron using the UPDATE statement. I can send back a "0" but then all constructions I do not want to address will still have the attribute with the stored value of "0". Rather then not having the attribute at all.

There of course are a lot of options to do this through the API but all those options would involve transferring large amounts of data via the internet and then doing spatial analyses on that transferred data (the "within neighborhood step") and then customizing all the queries in one "batch API-call" which is now possible and sending all of the data back. This however is likely multitudes slower then everything in Tygron using the Excel panels directly.

The reason I'm exploring this is specifically for the performance. I'm currently working on a very (!) large project where the API route is not really ideal and the servers we use for this API-route are used beyond the desired duration and resources. Thus having to fall back on API-tools that run locally and take a long time. Almost all due to the large data transfer required.

RudolfNL

Hi Len,

at this time an UPDATE statement will always require a value. Providing a non-numeric value or leaving it empty (which is also a non-numeric empty string) is invalid and will cause an error. 0 is the safest value to return, especially because a 0 and a non-existent attribute behave identically (and/or can be treated as such) in most cases.

Specifying the computation bottleneck is the "within neighborhood" clause, a hybrid solution could also be considered. Create an excel with as its only query UPDATE-BUILDING-NEIGHORHOOD-ID-WHERE-NEIGHBORHOOD-IS-ID. This way, each construction will have an attribute indicating its neighborhood, and your script needn't perform topological calculations anymore, just check the attribute. You could further expand on that by using an XC query instead, which also takes into account BAG-ID and/or other attributes which indicate relevance to you. (And then use -1 to indicate a construction is not relevant, as the ID 0 will belong to a neighborhood.) From there on, your external script will be able to simply read that attribute and calculate if that attribute is a relevant value to you.

Another path which may be interesting to consider, depending on the exact details of your desired calculations, might be some form of sheet-precompilation on your end. Rather than lobbing an X query of any flavor blindly into the Tygron Platform, it might be possible for an external tool to compile an excel sheet custom-tailored to what can be found in the project. This would allow you to skip any buildings which do not have a BAG_ID, or fail to meet some other requirement and create subdivisions based on quantity rather than geographical location, so that it's easier to avoid both UPDATE statement collisions, and the UPDATE query limit.

I believe these are good threads to explore further, and am looking forward to hearing any results you would like to share. We are always interested to hear the practical cases which push our Platform's limits.