PostGIS example tutorial

Fellow geekerati and Random Googlers!!!

The following is the process(with a few slight variations each time) that i always take whenever i have a postGIS related project. 

And.... need i say?... it has never let me down.

1. INITIALIZATION - Create a spatially-enabled database
====================
NOTE: tavevo is the database name. change accordingly

a. confirm postgis version
    #psql -d tavevo -c "SELECT postgis_full_version();"
    POSTGIS="1.5.3" GEOS="3.2.1-CAPI-1.6.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS

b. #psql -d tavevo < /usr/sesame/systems/sesame/postgis-1.5/postgis.sql
    Load the PostGIS object and function definitions into your database

c. #psql -d tavevo < /usr/sesame/systems/sesame/postgis-1.5/spatial_ref_sys.sql
    For a complete set of EPSG coordinate system definition identifiers,
    you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table.
    This will permit you to perform ST_Transform() operations on geometries.


2. CREATE ROLE/USER
#sudo su postgres
CREATE ROLE gisgroup NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
CREATE ROLE admin LOGIN PASSWORD '@dm1n' NOINHERIT;
GRANT gisgroup TO admin;


3. PERMISSIONS
Connect:
#psql -U postgres tavevo

Give permissions:
ALTER TABLE geometry_columns OWNER TO admin;
ALTER TABLE spatial_ref_sys OWNER TO admin;
ALTER TABLE water_meter OWNER TO admin;

--TESTING
--GRANT ALL ON TABLE geometry_columns TO admin;
--GRANT ALL ON TABLE spatial_ref_sys TO admin;
--GRANT ALL ON TABLE water_meter TO admin;



4. Create SCHEMA
CREATE SCHEMA gis_schema AUTHORIZATION admin;


5. GIVE OWNERSHIP TO admin
ALTER DATABASE tavevo OWNER TO "admin";


6. UPLOAD DATA

    a. COAST ADMINISTRATIVE

        Generate SQL:
                #shp2pgsql -I -s 32633 KEN-1.shp coast_admin > coast_admin.sql
                Shapefile type: Polygon
                Postgis type: MULTIPOLYGON[2]

        Execute SQL:
                #psql -U admin -d tavevo < coast_admin.sql

    b. COAST BOUNDARIES

        Generate SQL:
            #shp2pgsql -I -s 32633 KEN-1_boundaries.shp coast_boundaries > /usr/sesame/systems/tavevo/database/coast_boundaries.sql
            Shapefile type: Arc
            Postgis type: MULTILINESTRING[2]

        Execute SQL:
            #psql -U admin -d tavevo < coast_boundaries.sql

    c. KENYA - ADMINISTRATIVE
        Generate SQL:
            #shp2pgsql -I -s 32633 KEN.shp kenya_admin > /usr/sesame/systems/tavevo/database/kenya_admin.sql
            Shapefile type: Polygon
            Postgis type: MULTIPOLYGON[2]
        Execute SQL:
            #psql -U admin -d tavevo < kenya_admin.sql