#exist-db

That's an awful lot of release notes to read through but it needs to be done, even though its hard to know how the changes will affect my code.

As a first step I need to get things working without being tempted to utilise new XQuery features (I haven't used maps and arrays ) [see https://www.w3.org/TR/xquery-30/] and new modules (spatial index)

Very tempting to clear out old stuff whilst I do this but of course would be better done beforehand.

Need to be sure which scripts are being invoked from Apache

Getting one website running - Bristol Sailing Association, loading from an unzipped backup of the 3.0.1 database + others

Usual problem of testing applications which use my approach a REST-style - most have a config set up to allow path prefix to be changed from /Tree/ to ?_path= For this to work care needed on where script is located in the directory tree .... and Ive been inconsistent about how I implement this scheme

lots of permissions to change since these are not included in the backup - no function for this in the eXide UI - need to write a small tool to set permissions and do reindexes - about time but why is it necessary - ask dannes

missing the old dashboard application - problems in using the eXide db:Manager can't get below the db level - Cant get client.sh to work - problem with X11 ; using webdav instead is working

math module import has changed to

import module namespace math ="http://www.w3.org/2005/xpath-functions/math"; (actually no longer needs to be declared at all)

math:power () changed to math:pow()

math:abs to fn:abs()

math:floor() to fn:floor()

math:ceil() to fn:ceiling()

? are the arguments to atan2 ($y,$x) the same as in the old Math module -yes

math:radians() and math:degrees() have gone - no replacement (I put them in geo but should I have a standalone module for these bits - some in tp, some in wfn ?

fn:round() takes a precision argument so can replace fn:round-half-to-even()

xsl:format-date() to fn:format-date() but the picture format is quite different see https://www.w3.org/TR/xpath-functions-31/

eg "DD MMM - HH:mm" becomes "[D01] [MN,*-3] - [H01]:[m01]"

httpclient module has gone - use http://expath.org/ns/http-client instead - API is here http://expath.org/modules/http-client/ httpclient offered different calls for different services eg head() which have to be done explicitly in expath version - could make a front with the same api but prob. not worth it - at least write a script to template and test the calls

The basic get call converts to :

import module namespace http ="http://expath.org/ns/http-client"; declare namespace html ="http://www.w3.org/1999/xhtml";

let $doc := http:send-request(

<http:request href="{$pageurl}" method="get"> </http:request> )

let $title := $doc[2]//html:body/html:h1[1]

util:catch() has gone - use XQuery3.0 construct try {} catch * {}

Exist now catches declarations of functions with the same name and arity (previously the last declaration was used)

util:serialize() has gone - use fn:serialize but the parameters element must be either an output:serialization-parameters element or a map

see the Altova documentation

eg to serialize to html

fn:serialize($description,map{"method":"xhtml","media-type":"text/html"}

SItes tested

BSA - needs more work on httpclient replacement

Morley sq - my REST Scheme but implemented on level deeper so hack to replace root with ?_path= works but paths to resources like javascript and css dont work

slow without indexes - now added but the eXide db:Manager doesn't do reindexing - have to use the client or write my own script

aremiti - working all except Tracks which hasnt worked for a while since it uses an obsolete API to google maps

berger - working through the faults -

medTrees - the Guide and Book fine out-of-the box - tools need testing because these use scraping

logger - dashboard working

Wikibook - this a set of comparisons of XQuery solutions to problems. It uses my REST scheme so can be run locally by configuring path. tests so far indicate the general architecture is working fine (very fast on the Dell). Some individual task solutions fail (but nicely since they are execute with try {} . This means that a new script which executes all tests and logs those that fail is possible.

util:parse has gone - use util:parse-html() which tidies HTML too

after a day's work, progress seems quite good - the lack of a unit test framework for this code is a problem when migrating code but the most time-consuming stuff is editing the code to update - not sure how to take the main trees application forward - good to do this while I'm away , just little by little because the server version wont be being updated at the same time - must ensure they stay in sync.

Sept 23

Resumed this work but with version 6.2.0 now

Version installed on the old thinkpad - tried to install on Marks' Asus but problems encountered which have yet to be resolved.

Jan 24

Windows replaced with Ubuntu and version 6.2.0 loaded and running - will be the test machine

full aremiti bitfolk database as of 2024-01-06 unzipped onto the ASUS

Oct 24

ported the newly consolidated knots and polyhedra applications (bar the STL files) - no new issues

ported medtrees

ported nabg - reverted to a query string

The dashboard has changed significantly

no shutdown - has to be done in admin client or on command line

no way to change permissions - the functions are no longer in xmldb module - can only be done in the admin client use manage in eXide

porting BSA3

replacement needed for httpclient:post-form()

In most cases, inclusion of these resources is done in the main script rather than a lib/module

BSA3 ported

porting morleysq

only problems running without apache front end

same problem with paths to resource

problem with paths to images

Problem testing applications which use rest urls - consistent use of $root is the key - so it can be changed from eg "MS/" to "?_path="

This works in urls but I haven't found a way of making it work in forms

using a setting $local:rest allows the $root to be selected and conditional code to be generated where needed

also when using URL rewriting, the target directory is one above the app directory to allow the same path to be used with other scripts in the same directory. Without this , the script is running in the application directory so all the paths to resources are one level different - need to sort this out - perhaps base should just be to the app directory and the root then includes the app directory OK that works

MorleySq now working but exist was thrashing and had to be killed

logging disabled

home - landing page

terrain - needs the maths in geo to be updated as does theroad

2025-01

geo module updated

new work scapping json from a website for BNG -test for geo and JSON handling

2025-10

Natural England published the latest version of the Biodiversity Metric - BNG 4.0 in April 2023. It remains under continuing review. BNG 4.0, possibly amended, is likely to become mandatory when the remaining parts of the Environment Act 2021 come into force towards the end of the year. It is a very comprehensive suite of documents with supporting tools designed to aid in the calculation of biodiversity net gain.

A full baseline habitat analysis for a proposed development together with the proposed habitat changes requires the gathering, organisation and processing of a large amount of data. A macro-enabled Excel spreadsheet has been provided in the package, which is expected to be used widely, if not exclusively, for nearly all BNG calculations. Spreadsheets are, however, inherently difficult to comprehend, check and test.

This project is an attempt to provide an open source online tool to perform the same analysis which is comprehensible, checkable against the BNG guidance and testable.

Spreadsheets

Wonderfully useful though spreadsheets are, they do have some major downsides.

Unreadable Formula

Computation is defined using expressions which reference cells or ranges of cells by sheet name and cell address. There are mechanisms for giving cells meaningful names, but these are rarely used and not used in the BNG spreadsheet. This renders the spreadsheet very difficult to read and check without extensive navigation around multiple sheets and sub-tables embedded within sheets. Even then the meaning of the cells has guessed from column and row labels which themselves are mere descriptions rather than having any semantic value.

Macros and hidden columns further limit comprehension.

Lack of a coherent model

Because data and computation are interleaved within a spreadsheet, there is no separation of data from its processing and hence no guarantee that the same kind of data (all the rows in a table) will be computed in the same way.

Lack of a test strategy

Software engineering has developed strategies for enabling repeated testing of software so that the whole system can be re-tested after a minor change has been made which may have unforeseen consequences. Test-driven development goes further and requires test data and the results expected from that data to be created before development so that the behavior can be developed and tested incrementally. Although there are specialist tools such as Rapise which can assist with spreadsheet testing they are expensive.

Readability and testing are important because industry experience shows that an overwhelming number of operational spreadsheets have faults which, in some cases, has led to disastrous consequences for their users. Clearly, a tool which supports legislation should be provably correct.

As the computer scientist Tony Hoare said:

“There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. The first method is far more difficult.”

In this project we strive for simplicity.

A conceptual data model

A Entity-Attribute-Relationship (EAR) conceptual model captures the structure of the data in an information system. EAR models are typically used with Relational Databases which are composed of multiple inter-dependent tables.

Tables (such as the Habitat table in BNG 4.0 which list the 132) are conceptualized as Entities which define the common properties of every row in the table, i.e., every instance of the entity. The main part of this definition is the list of fields in the record which can be thought conceptually as attributes of the entity. Each attribute describes the type of values which can be held in the field, the permitted and default values and whether it is mandatory or optional. Relationships describe how one entity is related to another, typically by common attributes.

An executable conceptual model

An executable model is an extension of a data model which in addition defines the rules for computing attributes which are not raw data. In the software engineering approach, Model-Driven Development, such models can be used to generate the compiled software. For this project, the rules are interpreted using software written in XQuery running on the open source eXist-db XML database. As the user enters data for an instance of an entity (a row of a table), the software will evaluate the rules in the model in an appropriate order and compute the values of all the computed attributes, rather as a spreadsheet does. Here however the rules are expressed in readable formula which use the names of other attributes in the same entity and of those in related tables.

Formula conventions

The formulae are expressed in the XQuery language, although most need only the XPath subset. Two shortcuts are used:

$self/fieldname is a reference to a field in the record being processed.

table(‘tablename’) is a reference to the rows of a named table which may be a base reference table such as Habitat or a project-specific table such as Habitat_Baseline.

XPath expressions allow the rows to be filtered with a condition in [ ] and fields accessed by /fieldname

For example, the rule to compute the list of Habitats in a Broad Habitat is:

table('Habitat')[Broad_Habitat=$self/Broad_Habitat]/Habitat

which means:

Take all the rows of the Habitat table where the Broad_Habitat is the same as this rows Broad_Habitat and return the list of Habitat values in those rows.

To retrieve the Distinctiveness Score of the selected Habitat from the Habitat table:

table('Habitat')[Habitat=$self/Habitat]/Distinctiveness_Score

The language provides a range of common functions. This is the formula used in the overall Project Summary to sum all the On-site Baseline Habitat units:

sum(table('Habitat_Baseline')[On-off-site='On-site']/Total_habitat_units)

Here is the full model for the Habitat_Baseline.

Mapping the Excel Spreadsheet to the Conceptual Model

The reference tables were extracted from the tables in the BNG 4.0 calculator tool and exported as CSV, then loaded into the eXist database using column headings as attribute names. Some names were changed to better reflect their role. The key Habitat table was restructured to have two key columns: Broad Habitat and Habitat.

Three tables were multidimensional, with row headings one dimension and one or two levels of column headings for the other dimensions.

Habitat_Condition: Spreadsheet Table G-8 Condition lookup, Technical Annex 2 Data Table 2 Habitat condition score matrix.

Habitat_Creation_Year: Spreadsheet Table G4 Temporal Multipliers, Technical Annex Data 2 Table 3: Habitat creation matrix.

Habitat Enhancement Year: Spreadsheet Table G-5 Enhancement Temporal, Technical Annex 2 Data Table 3: Habitat enhancement matrix 

The data in these tables are sparse, so multiple cells are marked as 'Not Possible'. For computational purposes it is preferable to convert these to simple tables with multiple keys and with 'Not Possible' combinations removed to be dealt separately with when the data is missing.

The tables which contain project-specific data are based on the same tables in the Spreadsheet. For simplicity I combined each pair of On-site and Off-site tables into a single table. This ensures that the only difference is the application of a spatial multiplier for the Off-site entries.

Comparison with the Natural England Spreadsheet

So far, the model is limited to the Area Habitat tables, both on-site and off-site as well as support for tree surveys. Testing has been so far limited to defining a number of real-world cases in both the Natural England Spreadsheet and in the XQuery on-line system and comparing the results.

This is the set of projects so far implemented. All are viewable but a pin number is required to edit all but the Test Project.

Given the complexity of some of the formula, we have identified very few issues:

The computation in the Spreadsheet for Enhancement Habitat Units is at odds with the formula given in Technical Annex 2 BOX TS 7-1. The Spreadsheet formula only uses the Post-Intervention Area, whereas Equation 3 refers to the Pre-Intervention Area as well.

The Off-site Enhancement calculation makes no use of the Spatial Risk multiplier although this is present in the sheet.

Rounding in the Spreadsheet is sometimes incorrect which creates small differences in totals to those in the online version where values are computed throughout to double precision and rounded on display.

Technical Annex 2 Table 2 Habitat Condition Score Matrix and hence Spreadsheet table G8 Condition look-up allows the Bramble Scrub Habitat to have both 'Condition Assessment N/A' and 'Fairly Poor'.

Further work

The project is at the prototype stage and parts of the BNG model are yet to be implemented, in particular the linear habitats of hedges and waterways and some overall validity checks. Automated testing is feasible but has not yet been implemented. It would be interesting to know if Natural England have test data which they could share.

We hope this project will be a useful contribution to the establishment of BNG4.0. A repository for the project is being created on GitHub.

Oct '23

Version 2 of the platform under development.

Where does each sentence in this post start and end? Given some schooling and well punctuated text, our brains handle this task pretty easily, but it turns out that telling a computer how to split a text into sentences is a bit tricky. In modern English we have a general rule: sentences begin with a capitalized word and end with a period. But there are plenty of exceptions to account for in writing a program to isolate sentences: other words in the sentence might be capitalized, and abbreviations can contain and end with those periods, whether they’re at the end of a sentence or not.

For some time, I’ve wondered about how to find the start and end of sentences, but couldn’t ever devise an approach that worked. Then, recently, inspired by my friend Josh’s comment during a course we were taking on XQuery ("Could we use XQuery to pull out all topic sentences in a manuscript to help ensure the narrative flows logically and smoothly?"), I decided to return to the challenge. After some research I found a hint in this post on stackoverflow, which unlocked a core insight: if you look at the individual words in the text, one at a time and in order, you can look for signs of a sentence break, and then apply logic against surrounding words and account for known exceptions to the rule, such as abbreviations or stock phrases. Proceeding this way through a text, you can isolate each sentence.

So, should you ever have the need for splitting text into sentences—perhaps for looking at all topic sentences in a chapter, or for counting sentences in a paragraph—check out https://gist.github.com/joewiz/5889711. It’s a pair of XQuery functions for analyzing a chunk of text and identifying the sentences within. It’s a naive approach (see my notes at the top of that page), but it does a pretty good job with newspaper articles and other edited English prose.

It takes text like this paragraph (from FRUS):

154613. You should arrange to deliver following note to North Vietnamese Embassy. If in your opinion it can be done without creating an issue, we would prefer that you ask North Vietnamese Charge to come to your Embassy to receive note. “The U.S. Government agrees with the statement of the Government of the DRV, in its note of April 27, that it is necessary for Hanoi and Washington to engage in conversations promptly. The U.S. Government notes that the DRV has now agreed that representatives of the two countries should hold private discussions for the sole purpose of agreeing on a location and date. The U.S. Government notes that the DRV did not respond to its suggestion of April 23 that we meet for this limited purpose in a ‘capital not previously considered by either side.’ The U.S. Government suggested the DRV might wish to indicate three appropriate locations suitable for this limited purpose. The U.S. Government does not consider that the suggestion of Warsaw is responsive or acceptable. The U.S. Government is prepared for these limited discussions on April 30 or several days thereafter. The U.S. Government would welcome the prompt response of the DRV to this suggestion.”

and returns this:

154613.

You should arrange to deliver following note to North Vietnamese Embassy.

If in your opinion it can be done without creating an issue, we would prefer that you ask North Vietnamese Charge to come to your Embassy to receive note.

“The U.S. Government agrees with the statement of the Government of the DRV, in its note of April 27, that it is necessary for Hanoi and Washington to engage in conversations promptly.

The U.S. Government notes that the DRV has now agreed that representatives of the two countries should hold private discussions for the sole purpose of agreeing on a location and date.

The U.S. Government notes that the DRV did not respond to its suggestion of April 23 that we meet for this limited purpose in a ‘capital not previously considered by either side.’

The U.S. Government suggested the DRV might wish to indicate three appropriate locations suitable for this limited purpose.

The U.S. Government does not consider that the suggestion of Warsaw is responsive or acceptable.

The U.S. Government is prepared for these limited discussions on April 30 or several days thereafter.

The U.S. Government would welcome the prompt response of the DRV to this suggestion.”

I chose this text because of the many capitalized words and abbreviations throughout and the variations in punctuation. I also tested against several New York Times and Boston Globe articles, a tricky portion from Moby Dick that threw off some other utilities, and some made up sentences with edge cases.

If you want to give it a try with your own text, you can copy the entire gist and paste it into eXide, the XQuery sandbox for eXist-db; click “Run" to see the results. (Should work with any XQuery implementation though.)