Using PostGIS

The following examples use PostGIS functions to create and query spatial objects. For more information about the PostGIS functions, see the

The following command creates a table named roads that holds GIS data and a geometry column.

CREATE  TABLE roads ( ID int4, NAME varchar(128) );

Use the PostGIS AddGeometryColumn function to add a column to the table:

SELECT AddGeometryColumn( roads, geom, -1, GEOMETRY, 2 );

Use the following SQL commands to insert data into the table roads . This data consists of the geometry of the type of Linestring (a line between two points):

INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (1,ST_GeomFromText(LINESTRING(0 10,0 0),-1),Beacon Road);
INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (2,ST_GeomFromText(LINESTRING(0 0,0 10),-1),Violet Road);
INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (3,ST_GeomFromText(LINESTRING(0 0,10 0),-1),Skelton Street);
INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (4,ST_GeomFromText(LINESTRING(0 0,10 10),-1),Fifth Avenue);
INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (5,ST_GeomFromText(LINESTRING(0 10,0 0),-1),Main Street);
INSERT INTO ROADS (ID,GEOM,NAME ) VALUES (6,ST_GeomFromText(LINESTRING(10 0,0 0),-1),Lipton Street);

You can use the GIST function to create an index on the geometry column:

CREATE INDEX roads_index ON roads using GIST (geom);

AsText(geometry) is a PostGIS function that returns a text representation of the geometry:

SELECT id, ST_AsText(geom) AS geom, name FROM ROADS order by id;
__OUTPUT__
 id |         geom          |      name
- ---+-----------------------+----------------
  1 | LINESTRING(0 10,0 0)  | Bacon Road
  2 | LINESTRING(0 0,0 10)  | Violet Road
  3 | LINESTRING(0 0,10 0)  | Skelton Street
  4 | LINESTRING(0 0,10 10) | Fifth Avenue
  5 | LINESTRING(0 10,0 0)  | Main Street
  6 | LINESTRING(10 0,0 0)  | Lipton Street
(6 rows)

After an index is created, you can use the && operator in a query:

SELECT NAME, ST_AsText(GEOM) FROM ROADS WHERE GEOM && SetSRID(BOX3D(10 10,10 10)::box3d,-1);
__OUTPUT__
     name     |        astext
- -------------+-----------------------
 Fifth Avenue | LINESTRING(0 0,10 10)
(1 row)

Use the BOX3D function to specify a bounding box. The && operator uses the index to quickly reduce the result set down to only those geometries with bounding boxes that overlap the specified area.

You can use the ~= operator to check if two geometries are geometrically identical:

SELECT ID, NAME FROM roads WHERE GEOM ~= ST_GeomFromText(LINESTRING(0 10,0 0),-1) order by id;
__OUTPUT__
 id |    name
- ---+-------------
  1 | Bacon Road
  5 | Main Street
(2 rows)