Make use of US, CA and MX geographical data (requested by OCNA)

[andrixnet]

OCNA serves North America, that is USA, Canada and Mexico.

Geographical data on these is split across several tables.

IMHO there are two ways of handling it:

1. import OCNA specific code into OCPL-current and activate it only for OCNA node
2. process geographical data and convert it into nuts_codes and nuts_layer compatible data, then use existing code to handle it.
3. a combination of the above, given that US data may have more usable database fields and offer more features then just nuts data.

I have compiled and attached what I believe is the relevant database contents for this geographical data.

ca_province.zip geographical_definitions.zip mx_states.zip us_state_c.zip

[kojoty]

I think we need to consider if the best option is not to use just external service which provide Geo data for the whole world.

[andrixnet]

During migration I have discovered the following:

• Canada and Mexico geographical data converts easily into nuts_codes and nuts_layer as layer 2 (same as used by OCPL code).
• USA data is given at county level only. Since MariaDB ST_UNION supports only 2 geometry parameters, I cannot aggregate. However I found this: https://dba.stackexchange.com/questions/165606/mysql-equivalent-of-a-spatial-st-union-aggregate-function and I am trying to implement it.

[andrixnet]

OCUS now has Canada and Mexico state level (layer 2) shapes and can be properly selected in newcache page. No country level data (yet).

Not yet able to unify the shapes from existing data to obtain USA states. See previous comment. @harrieklomp can you help?

[kojoty]

There is no need to use sql UNION - we can just import new US states + US counties geo data into nuts.. - WIP.