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.
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.
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.
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.
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); |
Primary key | ogc_fid serial NOT NULL, CONSTRAINT pricepaid_pk PRIMARY KEY (ogc_fid), |
Geometry field with Projection set | wkb_geometry geometry, CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2), |
Column names | Without spaces, lowercase, no reserved words. |
Spatial Index | CREATE INDEX pricepaid_geom_idx |
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.