Installing PostgreSQL and PostGIS - Part 3

Storing some geospatial data.
Following the steps documented in Postgis documentation here.
I have used pgadmin for accessing postgresql but psql will suffice too. I recommend pgadmin as it lets you see the spatial structures much more easily.


Step 1 - Create some normal table

CREATE TABLE roads (
road_id INTEGER,
road_name VARCHAR
);

Step 2 - Add a special column for spatial data

SELECT AddGeometryColumn( 'roads', 'roads_geom', -1, 'GEOMETRY',
 2 );

The 2 at the end of the sql command indicates the dimension of the geometry.

Step 3 - Add some data

BEGIN;
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (1,ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',-1),'Jeff Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (2,ST_GeomFromText('LINESTRING(189141 244158,189265 244817)',-1),'Geordie Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (3,ST_GeomFromText('LINESTRING(192783 228138,192612 229814)',-1),'Paul St');
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (4,ST_GeomFromText('LINESTRING(189412 252431,189631 259122)',-1),'Graeme Ave');
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (5,ST_GeomFromText('LINESTRING(190131 224148,190871 228134)',-1),'Phil Tce');
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (6,ST_GeomFromText('LINESTRING(198231 263418,198213 268322)',-1),'Dave Cres');
COMMIT;

You can view the data from some postgis enabled application like uDIG or JGrass.

Related posts:
[1]Installing PostgreSQL and PostGIS - Part 1
[2]Installing PostgreSQL and PostGIS - Part 2