Some PostGIS notes

These are some notes regarding the creation and some manipulation of a georeferenced table.

First let’s create a table in our database. Note that in this step we do not add the geometry column yet:

CREATE TABLE item (
   id INTEGER,
   type VARCHAR
);

Creating the geometry column (a point in my case). After this a new column will be added to the item table named item_location:

SELECT AddGeometryColumn('item','item_location',27492,'POINT',2);

Inserting a point in the database table (in the previous step a restriction was created forcing the verification of the coordinates, so be sure that those are correct):

INSERT INTO item
  VALUES(1,'item_type',
  ST_GeomFromText('POINT(-43833.469 166045.598)',27492));

While inserting a new point with a diferent SRID one must use the transform PostGIS function:

INSERT INTO item
  VALUES(...,
  transform(
    ST_GeomFromText('POINT (lat long)', <point_srid>), <destination_srid>));