PostGIS Tutorial

Creating a PostGIS database

CREATE DATABASE your_database WITH TEMPLATE = template_postgis;

Additional information about the template

CREATE TABLE spatial_ref_sys ( 
  srid       INTEGER NOT NULL PRIMARY KEY, 
  auth_name  VARCHAR(256), 
  auth_srid  INTEGER, 
  srtext     VARCHAR(2048), 
  proj4text  VARCHAR(2048) 
)

and

CREATE TABLE geometry_columns ( 
  f_table_catalog    VARRCHAR(256) NOT NULL, 
  f_table_schema     VARCHAR(256) NOT NULL,
  f_table_nam        VARCHAR(256) NOT NULL, 
  f_geometry_column  VARCHAR(256) NOT NULL, 
  coord_dimension    INTEGER NOT NULL, 
  srid               INTEGER NOT NULL, 
  type               VARCHAR(30) NOT NULL 
) 

Using PostGIS

Creating a table that GIS Objects

CREATE TABLE test_tutorial_postgis ( ID int4);
SELECT AddGeometryColumn(
  '', 
  'test_tutorial_postgis',
  'geometry',
  4326,
  'POLYGON',
  2
);

Delete a GIS object column

SELECT DropGeometryColumn('', 'test_tutorial_postgis','geometry');

Inserting data into that postGIS Table

insert into 
  test_tutorial_postgis (ID,geometry) 
values 
  (
   1,  
   GeomFromText('POLYGON( (-128 50, -128 49.948, -127.874 49.948, -127.874 50, -128 50))', 
                  4326)
  );

Perform spatial query

  SELECT id 
  FROM test_tutorial_postgis  
  WHERE ST_DWithin(geometry, GeomFromText('POINT(-128 49.948 49.948)',4326), 100.0);

References