Creating a view

Mapping with PostGIS...

Overview of the problem

1. I want to add KML layers to an Android application.
2. Each KML layer can only have one icon associated with it.
3. My source layer has 3 or 4 symbol types.

My solution

1. Create a separate view in PostGIS for each symbol type.
2. Publish each view as a separate layer in GeoServer
3. Create a style for each layer in GeoServer using KML styling.
4. Add each layer to the Android map.

This means the data still live in one layer in PostGIS so they can be treated as a single layer in other smarter applications (ArcMap) but appear as separate layers in Android.

Using views is also a good way to limit the attributes visible in the KML output. I only pass through the attributes that will be needed for each view. For example, I don't need to pass a flow rate attribute through if the feature is a Knox box.

Details

Create views

Connect to the database.

psql -U postgres hydrant_survey

First let's do a little normalization? Normal is good in databases.

CREATE TABLE fire_symbol_names (
  id SERIAL PRIMARY KEY,
  name VARCHAR(10)
);

INSERT INTO fire_symbol_names (name) VALUES ('FDC');
INSERT INTO fire_symbol_names (name) VALUES ('Knox');
INSERT INTO fire_symbol_names (name) VALUES ('Preplan');
INSERT INTO fire_symbol_names (name) VALUES ('Stpp');

SELECT * FROM fire_symbol_names;

ALTER TABLE corvallis_fire_symbols ADD COLUMN type INT;

UPDATE corvallis_fire_symbols SET type=1 WHERE name='FDC';
UPDATE corvallis_fire_symbols SET type=2 WHERE name='Knox';
UPDATE corvallis_fire_symbols SET type=3 WHERE name='PrePlan';
UPDATE corvallis_fire_symbols SET type=4 WHERE name='Stpp';

ALTER TABLE corvallis_fire_symbols ADD FOREIGN KEY (type) REFERENCES fire_symbol_names;

ALTER TABLE corvallis_fire_symbols DROP COLUMN name;
ALTER TABLE corvallis_fire_symbols DROP COLUMN description;

Now let's create our views. We don't have any attributes since each view will have only one feature type. I can rename the original table and create a new view so that GeoServer will see the same old view with text names.

ALTER TABLE corvallis_fire_symbols RENAME TO cvo_fire_symbols;
CREATE VIEW corvallis_fire_symbols AS (
  SELECT ogc_fid,wkb_geometry,name FROM cvo_fire_symbols AS fs, fire_symbol_names AS fsname WHERE fs.type = fsname.id
); 

CREATE VIEW cvo_fdc     AS ( SELECT ogc_fid, wkb_geometry FROM cvo_fire_symbols WHERE type = 1 );
CREATE VIEW cvo_knox    AS ( SELECT ogc_fid, wkb_geometry FROM cvo_fire_symbols WHERE type = 2 );
CREATE VIEW cvo_preplan AS ( SELECT ogc_fid, wkb_geometry FROM cvo_fire_symbols WHERE type = 3 );
CREATE VIEW cvo_stpp    AS ( SELECT ogc_fid, wkb_geometry FROM cvo_fire_symbols WHERE type = 4 );

All done; the original table is still visible as corvallis_fire_symbols and I now have 4 child views each presenting one symbol type.

Create metadata

If you want to be able to edit data, you need a consistent FID. Geoserver can't tell what column contains the primary key in a view (it's stupid, it should guess that it's ogc_fid!) You can get around this by creating a table to tell it. This note is straight from here.

CREATE TABLE gt_pk_metadata_table (
 table_schema VARCHAR(32) NOT NULL,
 table_name VARCHAR(32) NOT NULL,
 pk_column VARCHAR(32) NOT NULL,
 pk_column_idx INTEGER,
 pk_policy VARCHAR(32),
 pk_sequence VARCHAR(64),
 unique (table_schema, table_name, pk_column),
 check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
)

Now I need to create entries in this table. I don't need pk_column_idx [1]

INSERT INTO gt_pk_metadata_table (table_schema, table_name, pk_column, pk_policy, pk_sequence)\
VALUES ('public','cvo_knox_view','ogc_fid', 'sequence', 'public.symbols_ogc_fid_seq');

Create style for each view

I could use simple simples here like Google balloons, in different colors. I used icons though to be fancy.

Publish each view

This is the standard geoserver operation. In this case I will add a layer for each view and a layer group including the 4 view layers.