Enhancement: Ability to upload .csv onto change queue and auto accept

[Deb-Jones]

As a user of AIMS I want the ability to simply add multiple items to the change queue from a correctly formatted .csv and have them automatically accepted

Users will need to add or modify addresses. For modify, any parameter of the address could be modified (e.g. position type). The developer will need to advise the required format of the .csv.

The purpose of this enhancement is to facilitate data quality improvement work.

Note, this is similar to https://github.com/linz/QGIS-AIMS-Plugin/issues/207 except that I need the items to be automatically accepted rather than the user accepting each one individually.

The reason for this is that I have been made aware of a bulk load of 65,000 addresses. There may be more large bulk loads.

I still need https://github.com/linz/QGIS-AIMS-Plugin/issues/207.

[Deb-Jones]

I am currently able to accept 10 addresses per minute. To accept 65,000 addresses would take 108 hours.

[SPlanzer]

Concerning those to be modifying - Will the AIMS feature Id of the address to be modified be supplied in the .csv?

[Deb-Jones]

Tell me what the .csv needs. Would it be more practical to have two tools? One for adds only, one for modifys?

[SPlanzer]

One tool would be desirable as long as the .csv is explicit as to the actions of each record.

There will need to be a sit down to understand the requirements. currently I am unsure of my availability for this piece of work and as mentioned in #207 I will follow this up Monday.

[Deb-Jones]

The requirement is to have a method to load changes onto the queue and either accept them or leave them on the queue.

This method can be accessed by the users using execute via command line.

Users will be limited to senior addressing analysts, i.e. myself and @jason-bedford

The input file will be a .csv file. I will create an example .csv for @SPlanzer to review before development commences. It must include;

• Action (add/mod)
• New Status (proposed, current, historic)
• Address ID
• Every field (new values populated for edits/adds, existing values populated for fields that aren't changing)
• Accept ('t' = accept, 'f' = leave on queue)

If a change fails API validation, it will remain on the queue.

@SPlanzer estimates effort at: 1 week full time to develop 1 week 35% of time to support testing and deploy

[imincik]

@SPlanzer , @Deb-Jones ,

thanks, just let you know that I've seen your last update in proposal.

[SPlanzer]

Hi @deb I will start this this week.

As a first step we need an example csv file so that we can ensure we both agree on the files format that will be read and processed.

If you have one please share it else I will create and share one as part of project initiation.

[SPlanzer]

The beginning of a test .csv can be created with the below SQL.

@Deb-Jones, please see attached an example of a csv I will use for development. Please review this to ensure this is inline with what you are able / expect to provide to a cmd line tool.

note this very simple and only attempts a few simple new, retire and update cases. As development progreses I will create more complex examples.

SELECT
      'UPDATE' as action, --one of ('RETIRE', 'NEW', 'UPDATE')
       true as auto_accept, -- Boolean
       addressid, 
       addressableobjecttype,
       ST_X(addressableobjectaddressposition) as x, --(EDITED) 
       ST_Y(addressableobjectaddressposition) as y, --(EDITED) 
       addresstype,
       unittype, 
       unitvalue, 
       leveltype,
       levelvalue,
       addressnumberprefix,
       addressnumber,
       'xx' as addressnumbersuffix, 
       addressnumberhigh, 
       roadprefix, 
       roadname,        
       roadtypename, 
       roadsuffix, 
       waterroutename, 
       watername, 
       addressableobjectpositiontype,
       'this is a test' as comment
  FROM 
        aims_stage.address_publish
  WHERE 
        latest = true
  OFFSET 1000
  LIMIT 1000;

example.csv.txt *Note: GitHub doesn't support attaching .csv. It may be simpler to open in spreadsheet tools if you drop the ".txt"

EDIT: coordinates now to be supplied as x and y in two columns. This should be in relation to espg:4167

[SPlanzer]

The repository for development is to be the address data quality repo

please track any issues here

[SPlanzer]

Also note all references to DEL are now 'retire' to reduce any confusion by having the semantics reflect the system's actions.

therefore actions in the spreadsheet shall be one of:

• new
• retire
• update

[SPlanzer]

@Deb-Jones I will be ready to test this tomorrow.

First I will need to do some ground work to get you access to the AIMS installation on the LI-experimental server. It would be good to have some of your time the second half of this week to run the solution by you and get some testing done

[SPlanzer]

This is with @Deb-Jones for testing.

I am closing the dev ticket. Enhancements and bugs to be tracked against code repo

[imincik]

Thanks @SPlanzer .