WikiProject Sutton England/OPL map/PostGIS Schema

Just a sketchy idea at the moment.

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: categories; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE categories (
    name character varying(20),
    defaulticon smallint,
    cid integer NOT NULL,
    null_geom geometry DEFAULT GeomFromText('POINT(0 0)', -1)
);


--
-- Name: icons; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE icons (
    path character varying(100) NOT NULL,
    width smallint,
    height smallint,
    iid integer NOT NULL,
    null_geom geometry DEFAULT GeomFromText('POINT(0 0)', -1)
);


--
-- Name: pois; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE pois (
    geometry geometry,
    name text,
    description text,
    category smallint NOT NULL,
    icon smallint,
    fid integer NOT NULL
);


--
-- Name: categories_cid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE categories_cid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: categories_cid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE categories_cid_seq OWNED BY categories.cid;


--
-- Name: icons_iid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE icons_iid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: icons_iid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE icons_iid_seq OWNED BY icons.iid;


--
-- Name: pois_fid_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE pois_fid_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: pois_fid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE pois_fid_seq OWNED BY pois.fid;


--
-- Name: cid; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE categories ALTER COLUMN cid SET DEFAULT nextval('categories_cid_seq'::regclass);


--
-- Name: iid; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE icons ALTER COLUMN iid SET DEFAULT nextval('icons_iid_seq'::regclass);


--
-- Name: fid; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE pois ALTER COLUMN fid SET DEFAULT nextval('pois_fid_seq'::regclass);


--
-- Name: categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY categories
    ADD CONSTRAINT categories_pkey PRIMARY KEY (cid);


--
-- Name: icons_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY icons
    ADD CONSTRAINT icons_pkey PRIMARY KEY (iid);


--
-- Name: pois_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY pois
    ADD CONSTRAINT pois_pkey PRIMARY KEY (fid);


--
-- Name: categories_defaulticon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY categories
    ADD CONSTRAINT categories_defaulticon_fkey FOREIGN KEY (defaulticon) REFERENCES icons(iid);


--
-- Name: pois_category_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY pois
    ADD CONSTRAINT pois_category_fkey FOREIGN KEY (category) REFERENCES categories(cid);


--
-- Name: pois_icon_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY pois
    ADD CONSTRAINT pois_icon_fkey FOREIGN KEY (icon) REFERENCES icons(iid);

CREATE OR REPLACE VIEW "poisWithIcons" AS 
SELECT pois.fid, pois.geometry, pois.name, pois.description, pois.category AS categoryid, icons.path AS externalgraphic, icons.width AS externalgraphicwidth, icons.height AS externalgraphicheight
FROM pois, icons
WHERE pois.icon = icons.iid;

--
-- PostgreSQL database dump complete
--