Handling GIS data

CYPEX supports GIS (Geographical Information Systems) data. However, in order to use GIS data in CYPEX, there are some things which have to be taken into consideration.

Let’s take a look at a sample table:

cypex=# CREATE EXTENSION postgis;
CREATE EXTENSION
cypex=# CREATE TABLE t_area (
    id 		serial 	PRIMARY KEY,
        name 		text,
        g 		geometry
);
CREATE TABLE

The keys to GIS data are the “geometry” and “geography” columns. These aren’t directly visible in a web frontend. Let’s take a look at how default queries are generated:

GIS data

When we generate a default query, the end product will still contain a geometry column:

cypex=# \d+ cypex_generated.t_area
                        View "cypex_generated.t_area"
 Column |   Type   | Collation | Nullable | Default | Storage  | Description
--------+----------+-----------+----------+---------+----------+-------------
 id     | integer  |           |          |         | plain    |
 name   | text     |           |          |         | extended |
 g      | geometry |           |          |         | main     |
View definition:
 SELECT f0.id,
    f0.name,
    f0.g
   FROM t_area f0;

As it stands, this one isn’t readable. To fix this issue, you have to take care of GeoJSON creation on your own. The reason is that the developer has to define what the GeoJSON is supposed to contain. Check out the ST_AsGeoJSON function to transform your column to the desired format.

The following example shows how a GeoJSON can be created using a custom query (instead of a default one):

SELECT id,
    name
    (st_asgeojson(t_area.*, 'g'::text))::jsonb AS json_position,
FROM 	t_area;

You also have to create a trigger, in case you want to modify the GeoJSON coming in. You need to define how to transform things back to “geography” or back to “geometry”.

GIS apps in action

With CYPEX you can build powerful GIS apps. The following screenshot shows an example of what’s possible. What you see below is a visual editor which allows you to modify polygons.

GIS apps

It’s important to understand how this image was created: let’s take a look at what was done in the WYSIWYG editor. A Leaflet Map element was used and the JSON column was selected as the data source. If all triggers are correctly in place, you’ll see a map similar to the one above.

The configuration of such a widget is similar to any other widget known to CYPEX. The important part is to use the GeoJSON column to feed the widget with GIS data. In addition to that, you can use background layers to display additional information:

geojson editor

In general, working with GIS data is easy. The CYPEX development team will expand this capability in the future, and add more features to the GIS backend.

Geographic Data

Handling GIS data

GIS apps in action