PlexBI Documentation

Adding Layers with SQL and PostGIS

Previously, we saw how to create a map from SQL with PostGIS.

In this section, we will see how we added a new layer using SQL and add it as a layer to the map we made earlier.

1. Click on Maps > Map Layers

2. Click the “Add New Map Layer” button.

3. Select the data source from the Add Map Layer Wizard

4. Enter the SQL for the new layer.

Below, we are going to simply use a different query against the same states table we used in earlier.  This time, we will write our SQL to show US Sub Regions layers rather than state layers.

Our SQL, as below, will be:

SELECT sub_region, {citqlGEOMCol|ST_AsGeoJSON(ST_Multi(ST_Union(geom)))} FROM states GROUP BY sub_region

Above, we are using the PostGIS ST_Multi and ST_Union functions and passing these to ST_AsGeoJSON to create the json object for our map.

5. Click Submit

Ughh!! That doesn’t look very good!

Click on the marker icon at top right to style the layer you have created

Add the data elements you want to appear in the pop-up content.

Set the layer fill and line colour, as well as opacity and weight.

Click Submit:

OK – that’s a bit better:

6. Add the new  layer to your existing map.

Go to the map you create in First Map

Click on the Layer icon on the top right corner of the map

Select the Layer you just created, “Sub Regions”.

Your layer has been added!

You map now has two layers (the states layer and regions layer):