Database Objects

The edit documentation (spatial and non-spatial) assumes that the following database objects are defined in a PostgreSQL/PostGIS instance.

Shared Lookup Table: Staff

DROP TABLE IF EXISTS public.staff;

CREATE TABLE public.staff (
  id serial not null primary key,
  name varchar(100)
);

ALTER TABLE public.staff ADD CONSTRAINT staff_uq UNIQUE(name);

INSERT INTO public.staff (name) values 
('Fred Flintstone'),
('Wilma Flintstone'),
('Pebbles Flintstone'),
('Barney Rubble'),
('Betty Rubble'),
('Bam Bam Rubble');

Shared Graffiti Table

DROP TABLE IF EXISTS public.graffiti CASCADE;

CREATE TABLE public.graffiti (
  ID            serial not null primary key,
  DESCRIPTION   text,
  STATUS        varchar(1) check (STATUS IN ('N','V','S','R')) default ('N'),
  FIELDVERIFIED varchar(1) check (FIELDVERIFIED in ('Y','N')),
  /* Who reported the graffiti */
  REPORTEDBY    varchar(100),
  REPORTEDON    timestamp,
  /* record / edit level metadata ie who created/updated record in database */
  CREATEDBY     varchar(100),
  CREATEDON     timestamp,
  MODIFIEDBY    varchar(100),
  MODIFIEDON    timestamp,
  /* Geometry not restricted to a specific type but SRID is limited to 4326 */
  GEOM          geometry(Geometry,4326)
);

INSERT INTO public.graffiti (DESCRIPTION,STATUS,FIELDVERIFIED,REPORTEDBY,REPORTEDON,CREATEDBY,CREATEDON,GEOM)
VALUES ('Banksy like mural',
        'V',
        'Y',
        'Cohga',
        CURRENT_TIMESTAMP,
        'Cohga',
        CURRENT_TIMESTAMP,
        ST_GeomFromEWKT('SRID=4326;POINT(144.953454336348 -37.8060850834926)'));

SELECT * 
  FROM public.geometry_columns 
 WHERE f_table_name = 'graffiti';

Database Showing Geometry Metadata For graffitiTable

<< Previous

Next >>