EDB Docs - PostGIS v3 - Using PostGIS

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)

Using PostGIS v3

← Prev

↑ Up