-- Downloaded from:  http://www.koders.com/sql/fidC547F57D920C926CBE4EAE889AA4E4C8499DC960.aspx  Normand Savard


-- This file inserts all the appropriate cite data into postgis to run with
-- geoserver.  To work with the tar file of featureType directories it
-- should be run in a database called cite, that either has a user with
-- a password of cite, cite, or that allows anyone read/write access.
-- You can also just run this script in whatever database you like with
-- your user name, but then you have to modify all the user access info
-- of the info.xml files.  Uncommenting lines at 253 will create the cite
-- user and grant access to it on all the relavant tables, the user you are
-- connecting with must have the appropriate permissions to do that.  


-- Uncomment and change this to the user who has permissions to drop and
-- create tables in this db.
-- \connect - cite




--uncomment these if you want to reset everything.
drop table "nulls";
drop table "points";
drop table "other";
drop table "lines";
drop table "polygons";
drop table "mlines";
drop table "mpolygons";
drop table "mpoints";
drop table "seven";
drop table "fifteen";
drop table "updates";
drop table "inserts";
drop table "deletes";
drop table "locks";
delete from "geometry_columns" where srid=32615;




--
-- TOC Entry ID 23 (OID 312261)
--
-- Name: SevenFeature Type: TABLE Owner: ciesin
--

CREATE TABLE "seven" (
	"boundedby" geometry,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;


--
-- TOC Entry ID 24 (OID 312275)
--
-- Name: NullFeature Type: TABLE Owner: cite
--

CREATE TABLE "nulls" (
	"description" character varying,
	"name" character varying,
	"boundedby" geometry,
	"integers" integer,
	"dates" date,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 25 (OID 312300)
--
-- Name: DeleteFeature Type: TABLE Owner: cite
--

CREATE TABLE "deletes" (
	"boundedby" geometry,
	"id" character varying,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))

) WITH OIDS;

--
-- TOC Entry ID 26 (OID 312305)
--
-- Name: InsertFeature Type: TABLE Owner: cite
--

CREATE TABLE "inserts" (
	"boundedby" geometry,
	"id" character varying,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;


--
-- TOC Entry ID 27 (OID 312310)
--
-- Name: UpdateFeature Type: TABLE Owner: cite
--

CREATE TABLE "updates" (
	"boundedby" geometry,
	"id" character varying,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 28 (OID 312315)
--
-- Name: PointFeature Type: TABLE Owner: cite
--

CREATE TABLE "points" (
	"id" character varying,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 29 (OID 312322)
--
-- Name: LineStringFeature Type: TABLE Owner: cite
--

CREATE TABLE "lines" (
	"id" character varying,
	"linestringproperty" geometry,
	CHECK ((srid("linestringproperty") = 32615)),
	CHECK (((geometrytype("linestringproperty") = 'LINESTRING'::text) OR ("linestringproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 30 (OID 312329)
--
-- Name: PolygonFeature Type: TABLE Owner: cite
--

CREATE TABLE "polygons" (
	"id" character varying,
	"polygonproperty" geometry,
	CHECK ((srid("polygonproperty") = 32615)),
	CHECK (((geometrytype("polygonproperty") = 'POLYGON'::text) OR ("polygonproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 31 (OID 312335)
--
-- Name: MultiPointFeature Type: TABLE Owner: cite
--

CREATE TABLE "mpoints" (
	"id" character varying,
	"multipointproperty" geometry,
	CHECK ((srid("multipointproperty") = 32615)),
	CHECK (((geometrytype("multipointproperty") = 'MULTIPOINT'::text) OR ("multipointproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 32 (OID 312341)
--
-- Name: MultiLineStringFeature Type: TABLE Owner: cite
--

CREATE TABLE "mlines" (
	"id" character varying,
	"multilinestringproperty" geometry,
	CHECK ((srid("multilinestringproperty") = 32615)),
	CHECK (((geometrytype("multilinestringproperty") = 'MULTILINESTRING'::text) OR ("multilinestringproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 33 (OID 312348)
--
-- Name: MultiPolygonFeature Type: TABLE Owner: cite
--

CREATE TABLE "mpolygons" (
	"id" character varying,
	"multipolygonproperty" geometry,
	CHECK ((srid("multipolygonproperty") = 32615)),
	CHECK (((geometrytype("multipolygonproperty") = 'MULTIPOLYGON'::text) OR ("multipolygonproperty" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 34 (OID 312391)
--
-- Name: FifteenFeature Type: TABLE Owner: cite
--

CREATE TABLE "fifteen" (
	"boundedby" geometry,
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 35 (OID 312430)
--
-- Name: LockFeature Type: TABLE Owner: cite
--

CREATE TABLE "locks" (
	"boundedby" geometry,
	"id" character varying, 
	"pointproperty" geometry,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;

--
-- TOC Entry ID 36 (OID 312570)
--
-- Name: OtherFeature Type: TABLE Owner: cite
--

CREATE TABLE "other" (
	"description" character varying,
	"name" character varying,
	"boundedby" geometry,
	"pointproperty" geometry,
	"string1" character varying NOT NULL,
	"string2" character varying,
	"integers" integer,
	"dates" date,
	CHECK ((srid("pointproperty") = 32615)),
	CHECK (((geometrytype("pointproperty") = 'POINT'::text) OR ("pointproperty" IS NULL))),
	CHECK ((srid("boundedby") = 32615)),
	CHECK (((geometrytype("boundedby") = 'POLYGON'::text) OR ("boundedby" IS NULL)))
) WITH OIDS;

--
-- Data for TOC Entry ID 37 (OID 16560)
--
-- Name: spatial_ref_sys Type: TABLE DATA Owner: cite
--

--uncomment this line if cite user has not yet been created.
--create user cite;

--uncomment these to grant permissions to cite.  These occur here
-- as the tables need to be created before granting privelages.
--GRANT ALL ON "nulls" TO cite;
--GRANT ALL ON "points" TO cite;
--GRANT ALL ON "other" TO cite;
--GRANT ALL ON "lines" TO cite;
--GRANT ALL ON "polygons" TO cite;
--GRANT ALL ON "mlines" TO cite;
--GRANT ALL ON "mpolygons" TO cite;
--GRANT ALL ON "mpoints" TO cite;
--GRANT ALL ON "seven" TO cite;
--GRANT ALL ON "fifteen" TO cite;
--GRANT ALL ON "updates" TO cite;
--GRANT ALL ON "deletes" TO cite;
--GRANT ALL ON "inserts" TO cite;
--GRANT ALL ON "locks" TO cite;
--GRANT ALL ON "geometry_columns" TO cite;


COPY "geometry_columns" FROM stdin;
	public	nulls	pointproperty	2	32615	POINT
 	public	nulls	boundedby	2	32615	POLYGON
 	public	points	pointproperty	2	32615	POINT
 	public	other	pointproperty	2	32615	POINT
	public	lines	linestringproperty	2	32615	LINESTRING
 	public	polygons	polygonproperty	2	32615	POLYGON
	public	mpolygons	multipolygonproperty	2	32615	MULTIPOLYGON
	public	mpoints	multipointproperty	2	32615	MULTIPOINT
	public	mlines	multilinestringproperty	2	32615	MULTILINESTRING
 	public	other	boundedby	2	32615	POLYGON
 	public	seven	pointproperty	2	32615	POINT
 	public	seven	boundedby	2	32615	POLYGON
 	public	fifteen	pointproperty	2	32615	POINT
 	public	fifteen	boundedby	2	32615	POLYGON
 	public	updates	pointproperty	2	32615	POINT
 	public	updates	boundedby	2	32615	POLYGON
 	public	inserts	pointproperty	2	32615	POINT
 	public	inserts	boundedby	2	32615	POLYGON
 	public	deletes	pointproperty	2	32615	POINT
 	public	deletes	boundedby	2	32615	POLYGON
 	public	locks	pointproperty	2	32615	POINT
 	public	locks	boundedby	2	32615	POLYGON
\.
--
-- Data for TOC Entry ID 39 (OID 113496)
--
-- Name: county Type: TABLE DATA Owner: public
--


 

COPY "seven" FROM stdin;
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
\.
--
-- Data for TOC Entry ID 59 (OID 312275)
--
-- Name: NullFeature Type: TABLE DATA Owner: public
--


COPY "nulls" FROM stdin;
nullFeature	\N	SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	\N	\N	\N
\.
--
-- Data for TOC Entry ID 60 (OID 312300)
--
-- Name: DeleteFeature Type: TABLE DATA Owner: public
--


COPY "deletes" FROM stdin;
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	td0001	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	td0002	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	td0003	SRID=32615;POINT(500050 500050)
\.
--
-- Data for TOC Entry ID 61 (OID 312305)
--
-- Name: InsertFeature Type: TABLE DATA Owner: public
--


COPY "inserts" FROM stdin;
\.
--
-- Data for TOC Entry ID 62 (OID 312310)
--
-- Name: UpdateFeature Type: TABLE DATA Owner: public
--


COPY "updates" FROM stdin;
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	tu0001	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	tu0002	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	tu0003	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	tu0004	SRID=32615;POINT(500050 500050)
\.
--
-- Data for TOC Entry ID 63 (OID 312315)
--
-- Name: PointFeature Type: TABLE DATA Owner: public
--


COPY "points" FROM stdin;
t0000	SRID=32615;POINT(500050 500050)
\.
--
-- Data for TOC Entry ID 64 (OID 312322)
--
-- Name: LineStringFeature Type: TABLE DATA Owner: public
--


COPY "lines" FROM stdin;
t0001	SRID=32615;LINESTRING(500125 500025,500175 500075)
\.
--
-- Data for TOC Entry ID 65 (OID 312329)
--
-- Name: PolygonFeature Type: TABLE DATA Owner: public
--


COPY "polygons" FROM stdin;
t0002	SRID=32615;POLYGON((500225 500025,500225 500075,500275 500050,500275 500025,500225 500025))
\.
--
-- Data for TOC Entry ID 66 (OID 312335)
--
-- Name: MultiPointFeature Type: TABLE DATA Owner: public
--


COPY "mpoints" FROM stdin;
t0003	SRID=32615;MULTIPOINT(500325 500025,500375 500075)
\.
--
-- Data for TOC Entry ID 67 (OID 312341)
--
-- Name: MultiLineStringFeature Type: TABLE DATA Owner: public
--


COPY "mlines" FROM stdin;
t0004	SRID=32615;MULTILINESTRING((500425 500025,500475 500075),(500425 500075,500475 500025))
\.
--
-- Data for TOC Entry ID 68 (OID 312348)
--
-- Name: MultiPolygonFeature Type: TABLE DATA Owner: public
--


COPY "mpolygons" FROM stdin;
t0005	SRID=32615;MULTIPOLYGON(((500525 500025,500550 500050,500575 500025,500525 500025)),((500525 500050,500525 500075,500550 500075,500550 500050,500525 500050)))
\.
--
-- Data for TOC Entry ID 69 (OID 312391)
--
-- Name: FifteenFeature Type: TABLE DATA Owner: public
--


COPY "fifteen" FROM stdin;
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)
\.
--
-- Data for TOC Entry ID 70 (OID 312430)
--
-- Name: LockFeature Type: TABLE DATA Owner: public
--


COPY "locks" FROM stdin;
\N	lfla0001	\N
\N	lfla0002	\N
\N	lfla0003	\N
\N	lfla0004	\N
\N	gfwlla0001	\N
\N	gfwlla0002	\N
\N	gfwlla0003	\N
\N	gfwlla0004	\N
\N	lfbt0001	\N
\N	lfbt0002	\N
\N	lfbt0003	\N
\N	lfbt0004	\N
\N	lfbt0005	\N
\N	lfbt0006	\N
\N	gfwlbt0001	\N
\N	gfwlbt0002	\N
\N	gfwlbt0003	\N
\N	gfwlbt0004	\N
\N	gfwlbt0005	\N
\N	gfwlbt0006	\N
\N	lfe0001	\N
\N	lfe0002	\N
\N	lfe0003	\N
\N	lfe0004	\N
\N	gfwle0001	\N
\N	gfwle0002	\N
\N	gfwle0003	\N
\N	gfwle0004	\N
\N	lfra0001	\N
\N	lfra0002	\N
\N	lfra0003	\N
\N	lfra0004	\N
\N	lfra0005	\N
\N	lfra0006	\N
\N	lfra0007	\N
\N	lfra0008	\N
\N	lfra0009	\N
\N	lfra0010	\N
\N	gfwlra0001	\N
\N	gfwlra0002	\N
\N	gfwlra0003	\N
\N	gfwlra0004	\N
\N	gfwlra0005	\N
\N	gfwlra0006	\N
\N	gfwlra0007	\N
\N	gfwlra0008	\N
\N	gfwlra0009	\N
\N	gfwlra0010	\N
\N	lfrs0001	\N
\N	lfrs0002	\N
\N	lfrs0003	\N
\N	lfrs0004	\N
\N	lfrs0005	\N
\N	lfrs0006	\N
\N	lfrs0007	\N
\N	lfrs0008	\N
\N	lfrs0009	\N
\N	lfrs0010	\N
\N	gfwlrs0001	\N
\N	gfwlrs0002	\N
\N	gfwlrs0003	\N
\N	gfwlrs0004	\N
\N	gfwlrs0005	\N
\N	gfwlrs0006	\N
\N	gfwlrs0007	\N
\N	gfwlrs0008	\N
\N	gfwlrs0009	\N
\N	gfwlrs0010	\N
\.

UPDATE "locks" SET "boundedby" = GeometryFromText('POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))', 32615) WHERE TRUE;
UPDATE "locks" SET "pointproperty" = GeometryFromText('POINT(500050 500050)', 32615) WHERE TRUE;

--
-- Data for TOC Entry ID 71 (OID 312570)
--
-- Name: OtherFeature Type: TABLE DATA Owner: public
--


COPY "other" FROM stdin;
A Single Feature used to test returning of properties	singleFeature	SRID=32615;POLYGON((500000 500000,500000 500100,500100 500100,500100 500000,500000 500000))	SRID=32615;POINT(500050 500050)	always	sometimes	7	2002-12-02
\.

--
-- Fixes to have primary keys and no oids
--
alter table "deletes" add column pkey serial;
alter table "deletes" add primary key (pkey);
alter table "deletes" set without oids;

alter table "fifteen" add column pkey serial;
alter table "fifteen" add primary key (pkey);
alter table "fifteen" set without oids;

alter table "inserts" add column pkey serial;
alter table "inserts" add primary key (pkey);
alter table "inserts" set without oids;

alter table "lines" add column pkey serial;
alter table "lines" add primary key (pkey);
alter table "lines" set without oids;

alter table "locks" add column pkey serial;
alter table "locks" add primary key (pkey);
alter table "locks" set without oids;

alter table "mlines" add column pkey serial;
alter table "mlines" add primary key (pkey);
alter table "mlines" set without oids;

alter table "mpoints" add column pkey serial;
alter table "mpoints" add primary key (pkey);
alter table "mpoints" set without oids;

alter table "mpolygons" add column pkey serial;
alter table "mpolygons" add primary key (pkey);
alter table "mpolygons" set without oids;

alter table "nulls" add column pkey serial;
alter table "nulls" add primary key (pkey);
alter table "nulls" set without oids;

alter table "other" add column pkey serial;
alter table "other" add primary key (pkey);
alter table "other" set without oids;

alter table "points" add column pkey serial;
alter table "points" add primary key (pkey);
alter table "points" set without oids;

alter table "polygons" add column pkey serial;
alter table "polygons" add primary key (pkey);
alter table "polygons" set without oids;

alter table "seven" add column pkey serial;
alter table "seven" add primary key (pkey);
alter table "seven" set without oids;

alter table "updates" add column pkey serial;
alter table "updates" add primary key (pkey);
alter table "updates" set without oids;
