Storing Data in PostGIS

Overview

iShare now uses PostgreSQL 9.x and as a result we now recommend storing all data except for large, fairly static files within PostGIS. There are many ways of load spatial data into PostGIS. Here we show you how this can be done using iShare Studio and OGR (the preferred option) together with highlighting the best practice for how a spatial table should look.

Preparation

Build required schemas in PostgreSQL this could be one such as gis or multiple related to the service areas i.e. councilanddemocracy, environmentandplanning etc.

These schemas can be created via pgAdmin by right clicking on the Schemas node and selecting New Schema.

Steps Required if using iShare Studio

In the following steps we will be referring to a schema name councilanddemocracy and layer pollingstations.

This is a multi step process which would need to be repeated for all data layers.

• Step 1 - Load data into PostGIS [Datashare] (instead of Shapefiles)
• Step 2 - Create your Layers in Studio

If you wish to use your data in My House / My Nearest then you will need to replace Step 2 above with the following to create your Classic Layers.

• Step 2 - Update the .map files (MapServer) to reference PostGIS
• Step 3 - Update the Map Sources (My Maps / My House / My Nearest) with new lower case fields instead of the upper case fields (Shape files are uppercase, PostGIS are lower) - including link / name references.

Checking your Spatial Data

Here is an example of how your table should look

CREATE TABLE landreg.pricepaid
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry,
  price integer,
  property_type character(1),
  paon character(100),
  saon character(100),
  street character(100),
  locality character(100),
  town character(100),
  district character(100),
  county character(100),
  record_status character(1),
  CONSTRAINT pricepaid_pk PRIMARY KEY (ogc_fid),
  CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2),
  CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'POINT'::text OR wkb_geometry IS NULL),
  CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 27700)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE landreg.pricepaid
  OWNER TO postgres;

-- Index: landreg.pricepaid_geom_idx

-- DROP INDEX landreg.pricepaid_geom_idx;

CREATE INDEX pricepaid_geom_idx
  ON landreg.pricepaid
  USING gist
  (wkb_geometry);

Checklist

ogc_fid serial NOT NULL,

CONSTRAINT pricepaid_pk PRIMARY KEY (ogc_fid),

wkb_geometry geometry,

CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2),
CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'POINT'::text OR wkb_geometry IS NULL),
CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 27700)

CREATE INDEX pricepaid_geom_idx
  ON landreg.pricepaid
  USING gist
  (wkb_geometry);

Other Considerations

If you are using a View on a large table see How and When to use Views.

If you are finding performance slow then switch on warning messages in PostgreSQL Logging.