GIS / Spatial Data

CYPEX works directly on PostGIS: spatial columns keep their PostgreSQL geometry or geography type, and applications read them through generated queries and map elements. Generated default queries pass the raw geometry through unchanged, which a browser cannot render — convert it yourself with ST_AsGeoJSON in a custom query. Two Application Designer elements consume the result: Leaflet Map GeoJSON displays it read-only, and Leaflet Map GeoJSON Input adds drawing and editing of markers, lines, rectangles, polygons, and text annotations. Both render over one configurable base tile layer (OpenStreetMap by default).

There are a few things to take into consideration before you start.

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:

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):

1
2
3
4

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

Editing happens in the browser: the input element hands back the edited GeoJSON as the field value. Persisting it is your job, because the GeoJSON column is a query expression rather than an updatable column. Add an INSTEAD OF trigger on the view that converts the incoming GeoJSON back to geometry or geography and writes it to the base table.

The following screenshot shows polygon editing driven by a map element bound to a GeoJSON column:

In the Application Designer, a Leaflet Map GeoJSON Input element was configured with the JSON column as its data source. With the write-back trigger in place, edits made on the map are saved like any other field.

Configuring the element works like any other CYPEX element. The important part is to point it at the GeoJSON column. You can also set the base tile layer URL if you do not want the OpenStreetMap default, and cap the number of features a user may draw:

Working with GIS data follows the same schema-driven model as the rest of CYPEX: the database defines the shape of the data, and the application renders what the query exposes.