Visual Field

Drop your CSV File here to Import and Browse

Explore, Process and Visualize your data right in your browser.
visualfield.org

Import File

Click or drop your delimited File here

Preview

Charset:

Separator Custom

Delimiter Custom

Skip first lines. Header row Empty Fields as Null

Destination Table Temp

After Import, start Task Id Automate Import next time

Import URL

URL

Preview

Charset:

Separator Custom

Delimiter Custom

Skip first lines. Header row Empty Fields as Null

Destination Table Temp

Save as Task Id Cache Max-Age seconds

URL Header 1

URL Header 2

General Help

Mandatory, Optional input fields and Editable Content

This Styling Indicates a Mandatory Input Field
This Styling indicates an Optional Input Field
This Styling Indicates an Editable Data Content

File Import

If your Import File is in CSV standards format (RFC 4180) with a 1 row header containing field names, then you can generally accept the default settings and import your data without needing to change any settings. Many open data CSV datasets are in this format.

CSV parsing of multi line fields

The internal algorithm in which Visual Field parses import delimited files (eg, CSV) varies dependent upon the size of the input file. For files less than 20Mb, the parser will perform a full standards parse (RFC 4180) on the input file as a whole (internally done by the fantastic jQuery-csv parser). So any multi-line input fields (fields containing carriage returns or line feeds) should be parsed correctly. However, full file parsing of the input files in this way is not scalable for extremely large input data files. And this can cause memory utilisation issues within the browser (ie, crash) if this approach were to be adopted for all file sizes. To mitigate against this, there is an internal threshold set in Visual Field of 20Mb. For files larger than this 20Mb threshold, the input file will be split line-by-line and processed incrementally (ie, CSV parsed line-by-line). This enables Visual Field to ingest extremely large CSV, or other delimited, files (without having to parse the file as a whole). However for such large files (larger than this 20Mb threshold), multi-line input fields will typically cause a parse failure of those particular lines. In actuality, most of the very large delimited files, there are typically no multi-line fields. So in general, this 20Mb threshold for parsing approach - should go unnoticed and should not cause any issues - it also means that the Visual Field input file ingestion is very scalable for extremely large files with little to no inconvenience. Please refer to the Internal Limits section below.

Querying Columns containing spaces

If you are not accustomed to referencing database columns containing spaces when using SQL or similar, you may need to enclose those fields either in double quotes or square brackets. For example, suppose you have a table called SCHOOLS and one of its fields is "Upper Grade", then you could query this by something like;


select "Upper Grade" from SCHOOLS;

Or,


select [Upper Grade] from SCHOOLS;

Casting to correct datatypes

When importing delimited data (from the File Import or URL Import), for the created database table all of the field datatypes will be TEXT. No heuristics are applied to attempt to ascertain import field datatypes. Columns are simply all created as TEXT datatype and you may wish to CAST these to other datatypes. Casting can easily be done by various ways. You can cast in subsequent SQL queries. You may also wish to consider creating a task that runs after your table import and drops/(re)creates a database view that does the appropriate datatype casting. That is, the fields in the view could be cast to the appropriate datatypes. Casting is not strictly important in general - you can typically query numeric and string values in a similar way. However casting to the correct datatypes may become important when you start to relationally join tables together by SQL joins. Also, imported Query String Parameters placed in the VF_QUERY_STRING table are created as "TEXT" - and again they may need casting to appropriate datatypes if you relationally join these to other tables via SQL queries or views.


create view if not exists SCHOOLS_GRADE
as 
select cast("Upper Grade" as INT) "Upper Grade",
"Campus Name"
from SCHOOLS;

group_concat

There are several Custom Functions in Visual Field that require an array input by way of a delimited list. That is typically a list of values separated by a comma to form an input array of values. This may initially seem difficult to produce from SQL but thankfully, within WebSQL (sqllite), there is the excellent group_concat function. This is a string aggregation function with an optional delimiter. For example, if you wish to apply a statistical Custom Function against all lower grade values from the SCHOOLS table, you could prepare input to the Custom Function by a statement similar to the following;


create table if not exists SCHOOLS_LOWER_GRADES
as 
select group_concat("Lower Grade",',') as lower_grade_list
from SCHOOLS;

There are also several Spatial Custom Functions in Visual Field (courtesy of the fantastic Turf js library) that accept an array of geometries as input. Again, the group_concat function may be useful in producing a list (array) of WKT geometries for input. In this case however, WKT geometries may contain commas, so it is safer to enclose the grouped geometries in double quotes (delimiter). Eg, suppose our SCHOOLS table has a WKT geometry field called "Location 1", then to get a list (array) of geometries, you could use the following;


create table if not exists SCHOOLS_GEOMS_LIST
as 
select group_concat('"'||"Location 1"||'"',',') as geom_list
from SCHOOLS;

But what about the case where your input may contain double quotes or commas? The approach here is to take group_concat further with the replace function and replace any occurrence of a double quote with a double double quote. In this case you will essentially be producing a standards based CSV input list (array) from SQL! Eg, suppose we wish to make a listing of the "Campus Name" field in the SCHOOLS table and that field may contain either commas or double quotes, then the following approach is suggested;


create table if not exists SCHOOLS_NAME_LIST
as 
select 
group_concat('"'||replace("Campus Name",'"','""')||'"',',') 
as name_list
from SCHOOLS;

If for some reason you need the inverse function to group_concat, please see the Custom Function Misc:ListSplit - which takes a list (delimited array) and splits the results out to individual rows in the result table.

Editable Data Content

Editable Data Content enables you to easily change a database field value by simply clicking on the value and changing the content in page. Your WebSQL database will be updated with the new value. Slightly different styling is used to indicate an Editable Data Content and it may be displayed in;

Browse Screens - directly in the displayed result table
Table Visualizations - directly in the displayed result table
Map Visualizations (including Photo Map) - in Map Feature Popups
Media Visualizations - in Media Info Popups

There are several limitations that will cause a data value to not be able to be edited in page. These limitations are;

The Content is based on a database View instead of a Table.
The Content is not a simple scalar field. Eg, it cannot be edited if it is made up of;
- A hyperlink
- Media - eg; Image, Audio or Video
The Content has had a formatting function applied.
The Content has been truncated for display. Refer to the Config Section for setting length limits of displayed fields.

Exceptions to Guidelines relating to VF_ dictionary objects

As mentioned in the Reference Guide section, the Visual Field data dictionary is also maintained in the same WebSQL database that your tables will reside. The Visual Field dictionary tables and views are generally prefixed with "VF_" and there should generally be no need to query or run DML directly against these dictionary objects. There are a few specific exceptions to this guideline however, namely; VF_QUERY_STRING, VF_DEVICE_ATTRIBUTRES and VF_TILE_STORE.

VF_QUERY_STRING

This table contains the name value pairs of any Query String parameters passed to Visual Field in a GET context.

To obtain Query String expressions within SQL, it may be necessary to query the VF_QUERY_STRING table directly. No shortcut technique is provided - it is up to you to manually query this table. Some points worth noting also are;

The passed Query String values are of type TEXT. As per mentioned above, if you are joining such values to other tables, you may need to explicitly CAST such values correctly either as part of the join or otherwise.
Multi value parameters are acceptable and can be passed in so you may need to account for that also in any SQL referencing the VF_QUERY_STRING table.

Here are some SQL examples of querying VF_QUERY_STRING; Suppose our URL is; vf.html?A=-2&B=0&B=1


select "Campus Name"
from SCHOOLS
where "Lower Grade" =
(select cast(value as INT)
from VF_QUERY_STRING
where name = 'A');

And for a multi valued parameter;


select "Campus Name"
from SCHOOLS
where "Lower Grade" in 
(select cast(value as INT) 
 as "Value"
from VF_QUERY_STRING
where name = 'B');

VF_DEVICE_ATTRIBURES

Similar to VF_QUERY_STRING the VF_DEVICE_ATTRIBUTES will be populated with a Collect Device Attributes task invoked and acceptance by the user. Device Attributes are similar to VF_QUERY_STRING in that they may need to be explicitly queried and CAST to appropriate datatypes.

VF_TILE_STORE

The VF_TILE_STORE houses stored offline Map Tiles. This table is initially empty and gets populated if a user clicks the button in a Map Visualization and downloads Map Tiles for possible Offline use. Map Tile images must be CORS enabled and are simply stored in the VF_TILE_STORE table as an encoded array of Uint8 values. This is referred internally as ENCODING_ALGORITHM 1. However, more advanced users should recognise that the VF_TILE_STORE can be Browsed in Visual Field just like any other table and extracted as a CSV download. So, if you are technically impelled, you could create a tile store, extract it, and make the CSV available as a web service. You could then define a Table (Import URL) as part of a Page load, Run Menu, or Sequence that will fetch the web accessible CSV Tile Store and reload it into the VF_TILE_STORE table. You will then be able to create a mini application complete with an Offline Map Tile store. Please Note, as mentioned in the Reference Guide, downloading Map tiles for Offline use may be subject to your Tile providers usage policies.

Preparing multi table input for Custom Function invocation - an example

Suppose we have 2 tables; SCHOOLS and HAZARDS. The SCHOOLS table contains a point WKT geometry ("Location 1") and the HAZARDS contains a polygon WKT geometry ("the_geom"). Suppose we want to run the Turf:booleanPointInPolygon test against these 2 tables. To do this, it will be necessary to combine these prior for input to the Custom Function into a single table. The following SQL extract demonstrates how to possibly do this by way of a preparing a Cartesian Product. You could then use this joined table as input to the Custom Function and link the output of the Custom Function by way of referencing the appropriate ROWID's back to the source tables;


create table if not exists SCHOOLS_HAZARD
as select s.rowid as SCHOOLS_ROWID,
  s."Location 1" as the_point,
  h.rowid as HAZARDS_ROWID,
  h."the_geom" as the_polygon
from SCHOOLS s, HAZARDS h;

Chart and Map - Table Visualization Fallback

Table Visualizations in Visual Field are paginated in the number of rows displayed on a page and a limit also set for the maximum field display length. As such, Table Visualizations and SQL Statement (output) is intended to be much more scalable and allow far greater result sets. Browse and Table Visualizations are written in a way that should enable indefinite scalability where SQL Statement (output) is scalable to about 512Mb result set size. However, this is far greater than what is practical as a Chart or Map Visualization. And a Table Visualization should be fully scalable (and render without crashing the browser) even for extremely large tables. This is currently not the case for Chart and Map Visualizations. Currently, Chart (using the Chartjs library internally) and Map (using the Leaflet library internally) Visualizations typically attempt to render (or process) the entire result set. This tends to mean that, currently, such visualizations may not scalable for extremely large datasets and may cause memory issues in the browser (ie, may crash the browser) if left unbounded. To mitigate this, there are some fixed internal fallback thresholds in Visual Field that will render a Table Visualization instead of a Chart or Map after a certain number of rows. Please refer to the Internal Limits section below for more detail. Once a Chart or a Map result set result set exceeds a certain number of rows - a Table Visualization will be shown instead. To avoid such fallbacks, you may need to either reduce or somehow aggregate your data to produce a smaller result set before showing as a Chart or Map Visualization.

Visual Field - Internal Limits

The following are some fixed internal limits within Visual Field;

20Mb - Maximum full standards parse (RFC 4180) CSV file size. Larger CSV files will be parsed line-by-line.
10,000 - Maximum number of rows in Chart Visualization result set before falling back to Table Visualization.
30 - Maximum number of rows in Pie Chart Visualization result set before Pie Chart header legend will be omitted.
120,000 - Maximum number of rows in Map Visualization result set before falling back to Table Visualization.
3,000 or any coincident Points - Maximum number of rows in Map Visualization Point only result set before Points will clustered.
100 unsorted rows - Maximum number of rows displayed in Visual Field mini-lists.

Visual Field - Soft Limits

The following are some observed limits;

512Mb - Maximum SQL Statement result set limit. If you generate a result set greater than this size you may encounter browser memory issues (browser crash). For "select" statements, consider using the OFFSET and LIMIT directives to reduce the total result set.
512Mb - Maximum CSV Download size. If you generate CSV greater than this size you may encounter browser memory issues (browser crash). You may need to break up your CSV downloads into chunks by using the SQL Statement pane and specifying appropriate OFFSET and LIMIT directives in order to reduce the total result set.

Brief Discussion of Differences and Limitations between Metadata, Export Dump and SQL Dump

Visual Field Metadata Exports primarily define the key aspects of your Visual Field configuration. That is in respect to what you define in various Visual Field screens in the way of CSV file ingestion, defined sequences and visualizations for example. Visual Field Metadata is primarily intended to be useful if you are using Visual Field in an online (web) capacity, or a reporting, or mini data-driven application. Metadata is a JSON representation of your Visual Field configuration and can be imported into Visual Field by use of the METADATA parameter in the Query String when you invoke vf.html as a standalone file or from a web page. Visual Field Metadata can be used in both an online and offline capacity to define a particular Visual Field configuration.

A Visual Field Export Dump is a variable width csv file of encoded DDL and DML instructions. Visual Field Export Dumps are specific to Visual Field and should be able to be imported into Visual Field using another device or HTML5 WebSQL browser running Visual Field. As such Visual Field Dumps are intended to be a robust offline way of transferring your database objects between devices, browsers, or just making a backup.

SQL Dumps just consist of a series of DDL and DML SQL statements. SQL Dumps are intended to be a portable mechanism to facilitate the offline transfer of your Visual Field objects to another database such as sqlite or PostgreSQL. If you are using Visual Field for offline asset management, this SQL Dump option may be useful to send you offline edits to a central database.

However, SQL Dumps are perhaps not as robust as Export Dumps for a number of reasons.

SQL Dumps are just SQL statements and are not encoded. This may cause issues if you are using non-ascii (non-English) data or non-ascii object names. Ie, UTF8. Importing UTF8 database objects or data into another database may be problematic when running as SQL in another target database. And hence may cause SQL parsing errors in your target database.
Another significant limitation with SQL Dumps, is that, at the time of writing, a single WebSQL SQL statement is limited to 1,000,000 characters. Unfortunately, this is a significant limitation and pretty much means that the exportation of large database objects (such as Audio, Image or Video objects) is not practical in the way of an SQL Export. So again, whilst portable, the SQL Export is perhaps not as robust as the Export Dump facility. Meaning SQL Dumps are probably best suited to exporting tables with field data no greater than 1,000,000 characters.
There is currently no "SQL Import" facility provided within Visual Field.

Storage Allocation Error

If you experience this error during the Import phase (there was not enough remaining storage space, or the storage quota was reached and the user declined to allow more space), it may indicate that a request for the initial WebSQL database storage was initially successful however during actual allocation, your browser is enforcing quota restrictions on the amount of usable storage/disk space on your device for TEMPORARY objects. Common quota restrictions for Chrome and Opera on Windows and Android platforms for TEMPORARY objects (ie, WebSQL database) may mean that it is necessary to have up to 10 times the required database storage space available. Such quota restrictions commonly apply the following rules; 1) only 50% of remaining storage/disk space on your device will be considered in the usable "pool", 2) of the usable "pool" a single web page may only be permitted to use a maximum of 20% of the pool. In other words, you may need up to 10 times free space on your device that what your Offline Visual Field WebSQL database will occupy.

Application Usage

Visual Field is intended to be able to be used in any of the following ways;

Online Web Application
Offline Web Application
As a stand alone html file
As an Installed Progressive Web Application

Online Web Application

For an online web application, just install the vf.html file in your intended web server directory. This will allow Visual Field on your web server to be used in an online way.

Offline Web Application

For an offline web application, you will need to install both vf.html and the vf_service_worker.js files in your intended web server directory. This will allow Visual Field to be loaded in a browser even if your users do not have an internet connection (after initial load). Both the vf.hml and vf_service_worker.js will need to reside in the same directory.

As a stand alone html file

Visual Field can function as a stand alone html file just by loading the vf.html file in your browser using a file:// protocol context. That is, no web server is necessary to use the full functions of Visual Field. This usage option may be useful if you are unable to install Visual Field on a web server or unable to install it as a Progressive Web Application.

As an Installed Progressive Web Application

Progressive Web Applications are intended to be able to be installed on your device by using HTML component "Add to Home Screen" (A2HS). As of version 3.1.0, Visual Field is typically able to be installed on your device by a simple click on the "Install" option under the "Help" tab. This enables a quick, simple, non-technical install of Visual Field on to your users device. In order to support Visual Field as a Progressive Web Application, you will need to place vf.html, vf_service_worker.js, vf.webmanifest and the favicon.svg all 4 files on to your web server in the same directory. Should all these files be installed on your web server, and your web server can serve these over https, then end users should be able to successfully invoke the "Install" option to add Visual Field to their device as an installed Progressive Web Application. Ie, a simple, non-technical, install on their device.

About

Copyright (c) 2019-2021 Harris Hudson
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are
permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of
conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright notice, this list
of conditions and the following disclaimer in the documentation and/or other materials
provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Collect Device Attributes

On Page Load

Visual Field

Import File

Import URL

General Config

Table Config

Map Config

Enter SQL Statement

Save Statement

Define Run Sequence

Define Custom Function

Define Prompt Input

Define Run Menu

Browse

Table Visualization

Self Drilldown

Chart Visualization

X-Axis

Series 1

Series 2

Series 3

Series 4

Map Visualization

Geometry

Custom Style

Self Drilldown

HeatMap Visualization

Network Visualization

Node Custom Config/Style

Edge Custom Config/Style

Physical Network

Self Drilldown

Media Visualization (Slide Show)

Media Source

Self Drilldown

Export Metadata

Import Metadata

Reset Metadata

Reference Guide

Menu (Home)

Table (Import File)

Charset

Separator

Delimiter

Skip first lines

Header Row

Empty Fields as Null

Destination Table

Temp

After Import Start Task Id

Automate Import Next Time

Table (Import URL)

Charset

Separator

Delimiter

Skip first lines

Header Row

Empty Fields as Null

Destination Table

Temp

Save as Task Id

Cache Max-Age

URL Header Name & Value

SQL Statement

Enter SQL Statement

Statement Description

Task Id

Custom Function

Input Table

Function

Library

Input Table

Function

Result Table

Temp

Description

Task Id

Table Visualization

Chart Visualization