Using PostGIS v3.2

Suggest edits

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

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)