postgres tips: postgis operations

What is PostGIS?
PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension

PostGIS Initialization

createdb ${dbname}
createlang plpgsql ${dbname}
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

PostGIS Tips

1. Create geometry data from x, y

-- 4326 means WGS84
CREATE TABLE sample_data AS
  SELECT
    ST_GeomFromText('POINT('||x||' '||y||')', 4326) AS the_geom
  FROM
    ${table}

2. Create spatial index to geometry data

-- GiST: Generalized Search Trees Indexes
CREATE INDEX
  geom_index
ON
  sample_data
USING GIST (the_geom);"

3. Create intersection and its area

-- intersection(geom_1, geom_2) returns intersection
CREATE TABLE intersections AS
  SELECT
    Area(intersection(a.the_geom, b.the_geom)) AS area,
    intersection(a.the_geom, b.the_geom) AS the_geom
  FROM
    geom_data_1 as a
  INNER JOIN
    geom_data_2 as b
  ON
    ST_Intersects(a.the_geom, b.the_geom);