SpatialUtils (Keboola)

This tool provides basic spatial functions for processing a CSV files with WKT geometries. You can calculate intersection, buffer, bounding box, area, dissolve, difference and so on. It is built on top of Spatialite and thanks to it has a very low memory footprint and it is even ready for large datasets (millions of rows). This tool could be handy if you don't have PostGIS or similar tools available.

SpatialUtils is CSV based (input and output). There are some requirements for geometries:

Geometry has to be stored as WKT string (https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry).
The coordinate system of geometry has to be 4326 (WGS84).

If your data is in a other format than CSV with WKT (e.g. Shapefile, GeoJSON), you can convert it using for example ogr2ogr utility (https://gdal.org/drivers/vector/csv.html).

Spatial functions

You can use one of the following spatial functions in each execution of SpatialUtils:

area
- computes area in square meters for each polygon
- value for function parameter in configuration is area
bounding box
- computes x_min, x_max, y_min and y_max for each geometry
- value for function parameter in configuration is bbox
buffer
- computes buffer around geometry (point, line, polygon) with defined radius in meters
- value for function parameter in configuration is buffer
centroid
- computes a centroid point for each geometry
- value for function parameter in configuration is centroid
union
- computes an union of geometries based on same values of attribute key
- value for function parameter in configuration is union
intersection
- computes an intersection between geometries from two input tables
- value for function parameter in configuration is intersection
isvalid
- validates WKT geometries and writes result into a two new columns - isvalid and isvalid_reason
- value for function parameter in configuration is isvalid

You can check for example https://postgis.net/workshops/postgis-intro/geometry_returning.html for further information about spatial functions.

Installation

SpatialUtils is available as Keboola Connection Component and it is available to registered Keboola users. You have to be logged in your Keboola project (https://connection.keboola.com) to use it. To open SpatialUtils just go to Applications, choose New Application and write "SpatialUtils" into the search bar. Then choose CleverMaps SpatialUtils.

Configuration

With the SpatialUtils application opened, choose New Configuration and fill in the form.

Firstly, you need to specify input and output mappings.

Table Input Mapping
- Choose New Table Input and specify a table with your data which you want to process.
Table Output Mapping
- Choose New Table Output and create a new empty table where the results will be stored.

Secondly, you need to pass a configuration JSON into SpatialUtils. Please check the examples below to get an idea of how the configuration file should look.

CODE

{
  "function": "area",
  "in_csv": {
    "wkt_column_name": "geom",
    "out_columns": [
      "zone_id"
    ]
  }
}

CODE

{
    "function": "bbox",
    "in_csv": {
        "wkt_column_name": "wkt"
    }
}

CODE

{
  "function": "buffer",
  "in_csv": {
    "wkt_column_name": "wkt",
    "out_columns": [
      "id"
    ]
  },
  "buffer": {
    "srs_wkid": 3857,
    "dist": 100
  }
}

CODE

{
  "function": "centroid",
  "in_csv": {
    "wkt_column_name": "geom",
    "out_columns": [
      "zone_id"
    ]
  }
}

CODE

{
  "function": "union",
  "in_csv": {
    "wkt_column_name": "geom",
    "group_column_name": "restaurant_id",
    "out_columns": [
      "restaurant_id"
    ]
  },
  "simplify": true
}

CODE

{
  "function": "intersection",
  "in_left_csv": {
    "filename": "/workdir/orders.csv",
    "wkt_column": "geom",
    "join_column": "restaurant_id",
    "out_columns": [
      "order_id",
      "restaurant_id"
    ]
  },
  "in_right_csv": {
    "filename": "/workdir/zones.csv",
    "wkt_column": "geom",
    "join_column": "zone_id",
    "out_columns": [
      "zone_id",
      "restaurant_id"
    ]
  }
}

Additional info:

Parameter join_column is optional. If specified than left and right table is firstly joined using this column and then an intersection is computed.

CODE

{
  "function": "isvalid",
  "in_csv": {
    "wkt_column_name": "geom",
    "out_columns": [
      "zone_id"
    ]
  }
}