<commit_before>﻿======================
Resource Import/Export
======================

*Currently, all data import and export operations happen through the Arches command line interface.*

Uploading a CSV to Arches
-------------------------

To do a bulk import of resources to your Arches database, you must create a correctly formatted CSV (comma-separated values) file. We recommend MS Excel or Open Office for this task. More advanced users will likely find a custom scripting effort to be worthwhile.

To upload a shapefile see Uploading a Shapefile to Arches below.

All values containing commas must be quoted so the value is not misinterpreted by the importer as multiple csv columns. This includes strings, concept-lists, and multi-geoms.

The workflow for creating a CSV should be something like this:

#. Identify which Resource Model you are loading data into
#. Download the **mapping file** and **concepts file** for that resource model
#. Modify the mapping file to reference your CSV
#. Populate the CSV with your data
#. Import the CSV using the `import_business_data <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_ command.

CSV File Requirements
^^^^^^^^^^^^^^^^^^^^^^

Each row in the CSV can contain the attribute values of one and only one resource.

The first column in the CSV must be named ``ResourceID``. ResourceID is a user-generated unique ID for each individual resource. If ResourceID is a valid UUID, Arches will adopt it internally as the new resource's identifier. If ResourceID is not a valid UUID Arches will create a new UUID and use that as the resource's identifier. Subsequent columns can have any name.

ResourceIDs must be unique among all resources imported, not just within each csv, for this reason we suggest using UUIDs.

+-------------+---------------------+--------------------+--------------------+
| Resource ID | attribute 1         |  attribute 2       | attribute 3        |
+=============+=====================+====================+====================+
| 1           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+
| 2           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+
| 3           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+

*Simple CSV with three resources, each with three different attributes.*

Or, in a raw format (if you open the file in a text editor), the CSV should look like this::

    Resource ID,attribute 1,attribute 2,attribute 3
    1,attr. 1 value,attr. 2 value,attr. 3 value
    2,attr. 1 value,attr. 2 value,attr. 3 value
    3,attr. 1 value,attr. 2 value,attr. 3 value

Multiple lines may be used to add multiple attributes to a single resource. You must make sure these lines are contiguous, and every line must have a ResourceID. Other cells are optional.

+-------------+---------------------+--------------------------+--------------------+
| Resource ID | attribute 1         |  attribute 2             | attribute 3        |
+=============+=====================+==========================+====================+
| 1           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+
| 2           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+
| 2           |                     | attr. 2 additional value |                    |
+-------------+---------------------+--------------------------+--------------------+
| 3           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+

*CSV with three resources, one of which has two values for attribute 2.*

Depending on your Resource Model's graph structure, some attributes will be handled as "groups". For example, ``Name`` and ``Name Type`` attributes would be a group. Attributes that are grouped must be on the same row. However, a single row can have many different groups of attributes in it, but there may be only one of each group type per row. (e.g. you cannot have two names and two name types in one row).

+-------------+--------------------------+--------------------+---------------------------+
| Resource ID |  name                    | name_type          | description               |
+=============+==========================+====================+===========================+
| 1           | Yucca House              | Primary*           | "this house, built in..." |
+-------------+--------------------------+--------------------+---------------------------+
| 2           | Big House                | Primary*           | originally a small cabin  |
+-------------+--------------------------+--------------------+---------------------------+
| 2           | Old Main Building        | Historic*          |                           |
+-------------+--------------------------+--------------------+---------------------------+
| 3           | Writer's Cabin           | Primary*           | housed resident authors   |
+-------------+--------------------------+--------------------+---------------------------+

*CSV with three resources, one of which has two groups of* ``name`` *and* ``name_type`` *attributes. See below for more on the 'Primary' and 'Historic' labels.*

You must have values for any required nodes in your resource models.

The data_type attribute in the mapping file(see below) can help you determine what data type (string, number, conceptid, geometry, etc.) is valid for each column in your csv.

Some data types require specially formatted values:

* **Dates** All dates must be formatted as YYYY-MM-DD. Dates that are not four digits must be zero padded.
* **Geometry** All geometry must be formatted in `"well-known text" (WKT) <https://en.wikipedia.org/wiki/Well-known_text>`_, and that all coordinates stored as WGS 84 (EPSG:4326) decimal degrees. Multi geoms must be in quotes.
* **Rich Text** Rich text must be quoted html.
* **Concepts** See the Concepts_ section below.
* ``concept-list`` and ``domain-value-list`` Values must be represented as quoted, comma-separated strings in the source CSV file. Each value within the concept/domain list does not need to be in quotation marks.

    ``concept-list`` example::

        "3b4bddbb-0ca6-44d5-8622-ce9982412883,10ac2b9b-3425-4c3c-9b5c-983bb16f93d3,052e8714-3f19-4179-b1f9-dcbe45df673e"

Mapping File Requirements
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

All CSV files must be accompanied by a **mapping file**. This is a JSON-structured file that indicates which node in a Resource Model's graph each column in the CSV file should map to. The mapping file should contain the source column name populated in the ``file_field_name`` property for all nodes in a graph the user wishes to map to. The mapping file should be named exactly the same as the CSV file but with the extension '.mapping', and should be in the same directory as the CSV.

To create a mapping file for a Resource Model in your database, go to the Arches Designer landing page. Find the Resource Model into which you plan to load resources, and choose Export Mapping File from the Manage menu.

.. image:: images/create_mapping_file.gif

Unzip the download, and you'll find a ``.mapping`` file as well as a ``...concepts.json`` file (more on the latter below). The contents of the mapping file will look something like this::

    {
        "resource_model_id": "bbc5cee8-fa16-11e6-9e3e-026d961c88e6",
        "resource_model_name": "HER Buildings",
        "nodes": [
            {
                "arches_nodeid": "bbc5cf1f-fa16-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Name",
                "file_field_name": "",
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
            {
                "arches_nodeid": "d4896e3b-fa30-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Name Type",
                "file_field_name": "",
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
            ...
        ]
    }

The mapping file contains cursory information about the resource model (name and resource model id) and a listing of the nodes that comprise that resource model. Each node contains attributes to help you import your business data (not all attributes are used on import, some are there simply to assist you). The concept_export_value attribute is only present for nodes with datatypes of `concept`, 'concept-list', 'domain', and 'domain-list' - this attribute is not used for import. It is recommended that you not delete any attributes from the mapping file. If you do not wish to map to a specfic node simply leave the ``file_field_name`` attribute blank.

You will now need to enter the column name from your CSV into the ``file_field_name`` in appropriate node in the mapping file. For example, if your CSV has a column named "activity_type" and you want the values in this column to populate "Activity Type" nodes in Arches, you would add that name to the mapping file like so::

    {
        ...
            {
                "arches_nodeid": "bbc5cf1f-fa16-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Activity Type",
                "file_field_name": "activity_type", <-- place column name here
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
       ...
    }

To map more than one column to a single node, simply copy and paste that node within the mapping file.

.. _concepts:

Concept Values in CSVs, and the **concepts file**
`````````````````````````````````````````````````

Values in the CSV that represent concepts in the RDM (Reference Data Manager) must not use the label of that concept, but its actual valueid that represents that concept in the RDM. Thus, in the above example, 'Primary' and 'Historic', because these are actually concepts stored in the RDM, would actually need to be replaced by the valueid (a UUID) for their respective concepts.

To aid with the process, a **concepts file** is created every time you download a mapping file, which lists the concept valueids and corresponding labels for all of the Concept Collections associated with any of the Resource Model's nodes. For example::

    "Name Type": {
        "ecb20ae9-a457-4011-83bf-1c936e2d6b6a": "Historic",
        "81dd62d2-6701-4195-b74b-8057456bba4b": "Primary"
    },

Thus, using the concepts file, the example above should be changed to look like this:

+-------------+--------------------------+--------------------------------------+---------------------------+
| Resource ID |  name                    | name_type                            | description               |
+=============+==========================+======================================+===========================+
| 1           | Yucca House              | 81dd62d2-6701-4195-b74b-8057456bba4b | "this house, built in..." |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 2           | Big House                | 81dd62d2-6701-4195-b74b-8057456bba4b | originally a small cabin  |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 2           | Old Main Building        | ecb20ae9-a457-4011-83bf-1c936e2d6b6a |                           |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 3           | Writer's Cabin           | 81dd62d2-6701-4195-b74b-8057456bba4b | housed resident authors   |
+-------------+--------------------------+--------------------------------------+---------------------------+


Uploading a Shapefile to Arches
-------------------------------

Uploading a shapefile to Arches is very similar to uploading a csv file with a few exceptions. The same rules apply to rich text, concept data, grouped data, and contiguousness. And, like csv import, shapefile import requires a mapping file.

The shapefile must contain a field with a unique identifier for each resource named 'ResourceID'.

The shapefile must be in WGS 84 (EPSG:4326) decimal degrees.

The shapefile must consist of at least a .shp, .dbf, .shx, and .prj file. It may be zipped or unzipped.

Dates in a shapefile can be in Esri Shapefile date format, Arches will convert them to the appropriate date format.

In your mapping file, the node you wish to map the geometry to must have a ``file_field_name`` value of 'geom'.

To import a shapefile use the same command as csv import, simply point to your shapefile instead of your csv file. `import_business_data <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_

**Note:** More complex geometries may encounter a ``mapping_parser_exception`` error. This error occurs when a geometry is not valid in elasticsearch. To resolve this, first make sure your geometry is valid using ArcMap, QGIS, or PostGIS. Next, you can modify the precision of your geometry to 5 decimals or you can simplify your geometry using the QGIS simplify geometry geoprocessing tool, or the PostGIS st_snaptogrid function.

Exporting Arches Data
---------------------

Currently all export must happen throught the command line interface. Export commands are listed in the `Wiki documentation <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_.<commit_msg>re #1498, resolving merge conflicts<commit_after>﻿======================
Resource Import/Export
======================

*Currently, all data import and export operations happen through the Arches command line interface.*

Uploading a CSV to Arches
-------------------------

To do a bulk import of resources to your Arches database, you must create a correctly formatted CSV (comma-separated values) file. We recommend MS Excel or Open Office for this task. More advanced users will likely find a custom scripting effort to be worthwhile.

To upload a shapefile see Uploading a Shapefile to Arches below.

All values containing commas must be quoted so the value is not misinterpreted by the importer as multiple csv columns. This includes strings, concept-lists, and multi-geoms.

The workflow for creating a CSV should be something like this:

#. Identify which Resource Model you are loading data into
#. Download the **mapping file** and **concepts file** for that resource model
#. Modify the mapping file to reference your CSV
#. Populate the CSV with your data
#. Import the CSV using the `import_business_data <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_ command.

CSV File Requirements
^^^^^^^^^^^^^^^^^^^^^^

Each row in the CSV can contain the attribute values of one and only one resource.

The first column in the CSV must be named ``ResourceID``. ResourceID is a user-generated unique ID for each individual resource. If ResourceID is a valid UUID, Arches will adopt it internally as the new resource's identifier. If ResourceID is not a valid UUID Arches will create a new UUID and use that as the resource's identifier. Subsequent columns can have any name.

ResourceIDs must be unique among all resources imported, not just within each csv, for this reason we suggest using UUIDs.

+-------------+---------------------+--------------------+--------------------+
| Resource ID | attribute 1         |  attribute 2       | attribute 3        |
+=============+=====================+====================+====================+
| 1           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+
| 2           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+
| 3           | attr. 1 value       | attr. 2 value      | attr. 3 value      |
+-------------+---------------------+--------------------+--------------------+

*Simple CSV with three resources, each with three different attributes.*

Or, in a raw format (if you open the file in a text editor), the CSV should look like this::

    Resource ID,attribute 1,attribute 2,attribute 3
    1,attr. 1 value,attr. 2 value,attr. 3 value
    2,attr. 1 value,attr. 2 value,attr. 3 value
    3,attr. 1 value,attr. 2 value,attr. 3 value

Multiple lines may be used to add multiple attributes to a single resource. You must make sure these lines are contiguous, and every line must have a ResourceID. Other cells are optional.

+-------------+---------------------+--------------------------+--------------------+
| Resource ID | attribute 1         |  attribute 2             | attribute 3        |
+=============+=====================+==========================+====================+
| 1           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+
| 2           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+
| 2           |                     | attr. 2 additional value |                    |
+-------------+---------------------+--------------------------+--------------------+
| 3           | attr. 1 value       | attr. 2 value            | attr. 3 value      |
+-------------+---------------------+--------------------------+--------------------+

*CSV with three resources, one of which has two values for attribute 2.*

Depending on your Resource Model's graph structure, some attributes will be handled as "groups". For example, ``Name`` and ``Name Type`` attributes would be a group. Attributes that are grouped must be on the same row. However, a single row can have many different groups of attributes in it, but there may be only one of each group type per row. (e.g. you cannot have two names and two name types in one row).

+-------------+--------------------------+--------------------+---------------------------+
| Resource ID |  name                    | name_type          | description               |
+=============+==========================+====================+===========================+
| 1           | Yucca House              | Primary*           | "this house, built in..." |
+-------------+--------------------------+--------------------+---------------------------+
| 2           | Big House                | Primary*           | originally a small cabin  |
+-------------+--------------------------+--------------------+---------------------------+
| 2           | Old Main Building        | Historic*          |                           |
+-------------+--------------------------+--------------------+---------------------------+
| 3           | Writer's Cabin           | Primary*           | housed resident authors   |
+-------------+--------------------------+--------------------+---------------------------+

*CSV with three resources, one of which has two groups of* ``name`` *and* ``name_type`` *attributes. See below for more on the 'Primary' and 'Historic' labels.*

You must have values for any required nodes in your resource models.

The data_type attribute in the mapping file(see below) can help you determine what data type (string, number, conceptid, geometry, etc.) is valid for each column in your csv.

Some data types require specially formatted values:

* **Dates** All dates must be formatted as YYYY-MM-DD. Dates that are not four digits must be zero padded.
* **Geometry** All geometry must be formatted in `"well-known text" (WKT) <https://en.wikipedia.org/wiki/Well-known_text>`_, and that all coordinates stored as WGS 84 (EPSG:4326) decimal degrees. Multi geoms must be in quotes.
* **Rich Text** Rich text must be quoted html.
* **Concepts** See the Concepts_ section below.
* ``concept-list`` and ``domain-value-list`` Values must be represented as quoted, comma-separated strings in the source CSV file. Each value within the concept/domain list does not need to be in quotation marks.

    ``concept-list`` example::

        "3b4bddbb-0ca6-44d5-8622-ce9982412883,10ac2b9b-3425-4c3c-9b5c-983bb16f93d3,052e8714-3f19-4179-b1f9-dcbe45df673e"

Mapping File Requirements
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

All CSV files must be accompanied by a **mapping file**. This is a JSON-structured file that indicates which node in a Resource Model's graph each column in the CSV file should map to. The mapping file should contain the source column name populated in the ``file_field_name`` property for all nodes in a graph the user wishes to map to. The mapping file should be named exactly the same as the CSV file but with the extension '.mapping', and should be in the same directory as the CSV.

To create a mapping file for a Resource Model in your database, go to the Arches Designer landing page. Find the Resource Model into which you plan to load resources, and choose Export Mapping File from the Manage menu.

.. image:: images/create_mapping_file.gif

Unzip the download, and you'll find a ``.mapping`` file as well as a ``...concepts.json`` file (more on the latter below). The contents of the mapping file will look something like this::

    {
        "resource_model_id": "bbc5cee8-fa16-11e6-9e3e-026d961c88e6",
        "resource_model_name": "HER Buildings",
        "nodes": [
            {
                "arches_nodeid": "bbc5cf1f-fa16-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Name",
                "file_field_name": "",
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
            {
                "arches_nodeid": "d4896e3b-fa30-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Name Type",
                "file_field_name": "",
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
            ...
        ]
    }

The mapping file contains cursory information about the resource model (name and resource model id) and a listing of the nodes that comprise that resource model. Each node contains attributes to help you import your business data (not all attributes are used on import, some are there simply to assist you). The concept_export_value attribute is only present for nodes with datatypes of `concept`, 'concept-list', 'domain', and 'domain-list' - this attribute is not used for import. It is recommended that you not delete any attributes from the mapping file. If you do not wish to map to a specfic node simply leave the ``file_field_name`` attribute blank.

You will now need to enter the column name from your CSV into the ``file_field_name`` in appropriate node in the mapping file. For example, if your CSV has a column named "activity_type" and you want the values in this column to populate "Activity Type" nodes in Arches, you would add that name to the mapping file like so::

    {
        ...
            {
                "arches_nodeid": "bbc5cf1f-fa16-11e6-9e3e-026d961c88e6",
                "arches_node_name": "Activity Type",
                "file_field_name": "activity_type", <-- place column name here
                "data_type": "concept",
                "concept_export_value": "label",
                "export": false
            },
       ...
    }

To map more than one column to a single node, simply copy and paste that node within the mapping file.

.. _concepts:

Concept Values in CSVs, and the **concepts file**
`````````````````````````````````````````````````

Values in the CSV that represent concepts in the RDM (Reference Data Manager) must not use the label of that concept, but its actual valueid that represents that concept in the RDM. Thus, in the above example, 'Primary' and 'Historic', because these are actually concepts stored in the RDM, would actually need to be replaced by the valueid (a UUID) for their respective concepts.

To aid with the process, a **concepts file** is created every time you download a mapping file, which lists the concept valueids and corresponding labels for all of the Concept Collections associated with any of the Resource Model's nodes. For example::

    "Name Type": {
        "ecb20ae9-a457-4011-83bf-1c936e2d6b6a": "Historic",
        "81dd62d2-6701-4195-b74b-8057456bba4b": "Primary"
    },

Thus, using the concepts file, the example above should be changed to look like this:

+-------------+--------------------------+--------------------------------------+---------------------------+
| Resource ID |  name                    | name_type                            | description               |
+=============+==========================+======================================+===========================+
| 1           | Yucca House              | 81dd62d2-6701-4195-b74b-8057456bba4b | "this house, built in..." |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 2           | Big House                | 81dd62d2-6701-4195-b74b-8057456bba4b | originally a small cabin  |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 2           | Old Main Building        | ecb20ae9-a457-4011-83bf-1c936e2d6b6a |                           |
+-------------+--------------------------+--------------------------------------+---------------------------+
| 3           | Writer's Cabin           | 81dd62d2-6701-4195-b74b-8057456bba4b | housed resident authors   |
+-------------+--------------------------+--------------------------------------+---------------------------+


Uploading a Shapefile to Arches
-------------------------------

Uploading a shapefile to Arches is very similar to uploading a csv file with a few exceptions. The same rules apply to rich text, concept data, grouped data, and contiguousness. And, like csv import, shapefile import requires a mapping file.

The shapefile must contain a field with a unique identifier for each resource named 'ResourceID'.

The shapefile must be in WGS 84 (EPSG:4326) decimal degrees.

The shapefile must consist of at least a .shp, .dbf, .shx, and .prj file. It may be zipped or unzipped.

Dates in a shapefile can be in Esri Shapefile date format, Arches will convert them to the appropriate date format.

In your mapping file, the node you wish to map the geometry to must have a ``file_field_name`` value of 'geom'.

To import a shapefile use the same command as csv import, simply point to your shapefile instead of your csv file. `import_business_data <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_

**Note:** More complex geometries may encounter a ``mapping_parser_exception`` error. This error occurs when a geometry is not valid in elasticsearch. To resolve this, first make sure your geometry is valid using ArcMap, QGIS, or PostGIS. Next, you can modify the precision of your geometry to 5 decimals or you can simplify your geometry using the QGIS simplify geometry geoprocessing tool, or the PostGIS st_snaptogrid function.


Exporting Arches Data
---------------------

Currently all export must happen throught the command line interface. Export commands are listed in the `Wiki documentation <https://github.com/archesproject/arches/wiki/Command-Line-Reference#importexport-commands>`_.

Resource Database Views
^^^^^^^^^^^^^^^^^^^^^^^

To export to tabular formats such as shapefile, it is necessary to flatten the graph structure of your resources. One way to do this is to create a database view of your resource models. Arches does not do this automatically because there are many ways to design a flattened table depending on your needs.

You can add any number of database views representing a given resource model either for export, or to connect directly to a GIS client such as QGIS or ArcGIS. When writing a view to support shapefile export be sure that your view does not violate any shapefile restrictions. For example, shapefile field names are limited to 10 characters with no special characters and text fields cannot store more than 255 characters.

If you plan to use the arches `export` command to export your view as a shapefile, you also need to be sure that your view contains 2 fields: `geom` with the geometry representing your resource instance's location and `geom_type` with the postgis geometry type of your `geom` column.

To write your view, you should start by getting a mapping file for your resource. You can do that by going to the Arches Designer page and then in the `manage` dropdown of your resource model select `Create Mapping File`. A zip file will be downloaded and within that file you will find your `.mapping` file. This file lists all the ids that you will need to design your view.

Below is an example of a simple resource model view. If a resource instance has a tile with geojson saved to it, that tile will be represented as a record in the view along with the corresponding nodeid and tileid. A unique id (gid) is assigned to each row. If a node has more than one geometry, the geometries are combined into a multipart geometry. If a node has more than one geometry of different types, a record will be created for each type. The UUID (ab74af76-fa0e-11e6-9e3e-026d961c88e6) in the last line of this this example is the id of the view's resource model.

#. When creating your own view, you will need to replace this UUID with your own resource model's id. You can find this UUID in your mapping file assigned to the property: `resource_model_id`.

    .. code-block:: sql

        CREATE OR REPLACE VIEW vw_monuments_simple AS
        WITH mv AS (SELECT tileid, resourceinstanceid, nodeid, ST_Union(geom) as geom, ST_GeometryType(geom) AS geom_type
        FROM mv_geojson_geoms
        GROUP BY tileid, nodeid, resourceinstanceid, ST_GeometryType(geom))
        SELECT row_number() OVER () AS gid,
            mv.resourceinstanceid,
            mv.tileid,
            mv.nodeid,
            ST_GeometryType(geom) AS geom_type,
            geom
        FROM mv
        WHERE (SELECT graphid FROM resource_instances WHERE mv.resourceinstanceid = resourceinstanceid) = 'ab74af76-fa0e-11e6-9e3e-026d961c88e6'


#. Here is a more complete example which includes columns with tile data:

    .. code-block:: sql

        CREATE OR REPLACE VIEW vw_monuments AS
        WITH mv AS (select tileid, resourceinstanceid, nodeid, ST_Union(geom) AS geom, ST_GeometryType(geom) AS geom_type
        FROM mv_geojson_geoms
        GROUP BY tileid, nodeid, resourceinstanceid, ST_GeometryType(geom))
        SELECT
            row_number() over () AS gid,
            mv.resourceinstanceid,
            mv.tileid,
            mv.nodeid,
            ST_GeometryType(geom) AS geom_type,
            name_tile.tiledata ->> '677f303d-09cc-11e7-9aa6-6c4008b05c4c' AS name,
            (SELECT value FROM values WHERE cast(name_tile.tiledata ->> '677f39a8-09cc-11e7-834a-6c4008b05c4c' AS uuid) = valueid ) AS nametype,
            (SELECT value FROM values WHERE cast(component.tiledata ->>'ab74b009-fa0e-11e6-9e3e-026d961c88e6' AS uuid) = valueid ) AS construction_type,
            array_to_string((select array_agg(v.value) FROM unnest(ARRAY(SELECT jsonb_array_elements_text(component.tiledata -> 'ab74afec-fa0e-11e6-9e3e-026d961c88e6'))::uuid[]) item_id LEFT JOIN values v ON v.valueid=item_id), ',') AS const_tech,
            (SELECT value FROM values WHERE cast(record.tiledata ->> '677f2c0f-09cc-11e7-b412-6c4008b05c4c' AS uuid) = valueid ) AS record_type,
            geom
        FROM mv
        LEFT JOIN tiles name_tile
            ON mv.resourceinstanceid = name_tile.resourceinstanceid
            AND name_tile.tiledata->>'677f39a8-09cc-11e7-834a-6c4008b05c4c'
            != ''
        LEFT JOIN tiles component
            ON name_tile.resourceinstanceid = component.resourceinstanceid
            AND component.tiledata->>'ab74afec-fa0e-11e6-9e3e-026d961c88e6'
            != ''
        LEFT JOIN tiles record
            ON name_tile.resourceinstanceid = record.resourceinstanceid
            AND record.tiledata->>'677f2c0f-09cc-11e7-b412-6c4008b05c4c'
            != ''
        WHERE (SELECT graphid FROM resource_instances WHERE mv.resourceinstanceid = resourceinstanceid) = 'ab74af76-fa0e-11e6-9e3e-026d961c88e6'


#. You will notice that for each node added as a column in the table, we perform a LEFT JOIN to the tiles table and the nodeid from which we want data. Here is an example joining to the tile containing the `record` node which has a nodeid of `677f2c0f-09cc-11e7-b412-6c4008b05c4c`.

    .. code-block:: sql

        LEFT JOIN tiles record
            ON name_tile.resourceinstanceid = record.resourceinstanceid
            AND record.tiledata->>'677f2c0f-09cc-11e7-b412-6c4008b05c4c'
            != ''

#. We can then define a field be referencing that tile:

    .. code-block:: sql

        (SELECT value FROM values WHERE cast(record.tiledata ->> '677f2c0f-09cc-11e7-b412-6c4008b05c4c' AS uuid) = valueid ) AS record_type


#. How you define your fields depends largely on what the node datatype is:

    A node with a string datatype:

    .. code-block:: sql

        name_tile.tiledata ->> '677f303d-09cc-11e7-9aa6-6c4008b05c4c' AS name


    A node with a concept value id. The following returns the concept values label:

    .. code-block:: sql

        (SELECT value FROM values WHERE cast(name_tile.tiledata ->> '677f39a8-09cc-11e7-834a-6c4008b05c4c' AS uuid) = valueid ) AS nametype


    A node with a concept-list. The following returns a concatenated string of concept value labels:

    .. code-block:: sql

        array_to_string((SELECT array_agg(v.value) FROM unnest(ARRAY(SELECT jsonb_array_elements_text(component.tiledata -> 'ab74afec-fa0e-11e6-9e3e-026d961c88e6'))::uuid[]) item_id LEFT JOIN values v ON v.valueid=item_id), ',') AS const_tech
{'fraction_non_alphanumeric': 0.19449265301399976, 'fraction_numerical': 0.029590419993057965, 'mean_word_length': 2.0877020630526033, 'pattern_counts': {'":': 48, '<': 13, '<?xml version=': 0, '>': 35, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3269466', 'n_tokens_mistral': 9456, 'n_tokens_neox': 8386, 'n_words': 3770}