Basic data insertion into a postgis database

I assume you have setup postgis / postgresql using the tutorial here : http://www.basarat.com/2010/08/postgresql-portable.html

Now,
The objective is to make a simple table with site + cell + lon + lat and insert a value in it.

First off the SQL to create a the table :


CREATE TABLE "SITES"
(
  "SITENAME" text NOT NULL,
  "CELLNAME" text NOT NULL,
  "LOCATION" geometry,
  CONSTRAINT "SITES_pkey" PRIMARY KEY ("SITENAME", "CELLNAME")
)


The geometery type is one of the types that becomes available with postgis. As it is this table is complete. But the real joy of using a GIS based database starts with the ability to add a GIS index. You can do this by : 



CREATE INDEX "LOCATIONINDEX"
  ON "SITES"
  USING gist
  ("LOCATION");

Finally lets insert some data into the table : 

INSERT INTO "SITES"(

            "SITENAME", "CELLNAME", "LOCATION")

    VALUES ('somesite', 'somecell', ST_SetSRID(ST_Point(-71.1043443253471, 42.3150676015829),4326));

Two functions that come with PostGIS are used here : ST_Point and ST_SetSRID
ST_Point is the basic data type for point based geometries. SetSRID is used the set the Spatial reference system ID. 4326 corresponds to the reference system commonly known as WGS 84 and is the most commonly used. In fact this is the system employed by the GPS system. It is important to note that ST_Point and all other similar functions take coordinates in  Longitude ,  Latitude  order.

Till next time.
Enjoy!