-- switch to a different db

\c test

-- drop old nga db

drop database nga;

-- create new nga db

create database nga;
\c nga;

-- add OGC geometry columns; comment this part out if your postgresql
-- doesn't support OGC SQL92 with geometry types

\! createlang plpgsql nga
\i /usr/src/postgis-1.0.1/lwpostgis.sql
\i /usr/src/postgis-1.0.1/spatial_ref_sys.sql

-- create the gns table;

create table gns (
rc              int,
ufi             int,
uni             int             primary key,
dd_lat          real,
dd_long         real,
dms_lat         int,
dms_long        int,
utm             char(4),
jog             char(7),
fc              char,
dsg             text,
pc              int,
cc1             char(2),
adm1            char(2),
adm2            text,
dim             real,
cc2             char(2),
nt              char,
lc              char(2),
short_form      text,
generic         text,
sort_name       text,
full_name       text,
full_name_nd    text,
modify_date     text
);

copy gns from '/usr/local/pgsql/data/backup/nga/geonames_dd_dms_date_20050701.txt' with delimiter as '\t' null as '';

-- add the_geom column; comment this section out if your postgresql
-- doesn't support OGC SQL92 with geometry types

select AddGeometryColumn('gns', 'the_geom', 4326, 'POINT', 2 );

alter table gns add constraint geometry_valid_check check (isvalid(the_geom));

-- add a helper plpgsql function; comment this section out if your
-- postgresql doesn't support OGC SQL92 with geometry types

create or replace function UpdateTheGeomColumn() returns integer as $$

declare

singlerow record;
wkt varchar;

begin

for singlerow in select * from gns order by uni loop

wkt := 'POINT(' || singlerow.dd_lat::varchar || ' ' || singlerow.dd_long::varchar || ')';

execute 'update gns set the_geom = GeomFromText(''' || wkt::varchar || ''', 4326) where uni = ' || singlerow.uni || ';';

end loop;

return 1;

end;

$$ language plpgsql;

-- populate the geometry column; comment this section out if your
-- postgresql doesn't support OGC SQL92 with geometry types

select UpdateTheGeomColumn();

-- add indexes

create index gns_cc1_adm1 on gns(cc1, adm1);
create index gns_cc1 on gns(cc1);
create index gns_adm1 on gns(adm1);
create index gns_dd_lat_dd_long on gns(dd_lat, dd_long);
create index gns_dd_lat on gns(dd_lat);
create index gns_dd_long on gns(dd_long);
create index gns_dsg on gns(dsg);
create index gns_lc on gns(lc);
create index gns_full_name_nd on gns(full_name_nd);

-- Add a GIST index on the geometry column; comment this section out
-- if your postgresql doesn't support OGC SQL92 with geometry types

create index gns_the_geom_gist on gns using gist (the_geom);

-- vacuum analyze and grant privledges

vacuum analyze;

grant select on gns to public;

-- comment these two out if your postgresql doesn't support OGC SQL92
-- with geometry types

grant select on spatial_ref_sys to public;
grant select on geometry_columns to public;