Before we start playing with our data lets have a look at some simpler examples. Go ahead and execute the following SQL statement in the console.
CREATE TABLE geometries (name varchar, geom geometry); INSERT INTO geometries VALUES ('Point', 'POINT(0 0)'), ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'), ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'), ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'), ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'); SELECT name, ST_AsText(geom) FROM geometries;
The bottom part of the image shows what you should see
The above example CREATEs a table (geometries) then INSERTs five geometries: a point, a line, a polygon, a polygon with a hole, and a collection. Finally, the inserted rows are SELECTed and displayed in the Output pane.
In conformance with the Simple Features for SQL (SFSQL) specification, PostGIS provides two tables to track and report on the geometry types available in a given database.
- The first table,
spatial_ref_sys, defines all the spatial reference systems known to the database and will be described in greater detail later.
- The second table (actually, a view),
geometry_columns, provides a listing of all "features" (defined as an object with geometric attributes), and the basic details of those features.
Let's have a look at the
geometry_columns table in our database.
SELECT * FROM geometry_columns;
Again this screenshot shows you the output of the command above in a GUI.
f_table_nameprovide the fully qualified name of the feature table containing a given geometry. Because PostgreSQL doesn't make use of catalogs,
f_table_catalogwill tend to be empty.
f_geometry_columnis the name of the column that geometry containing column -- for feature tables with multiple geometry columns, there will be one record for each.
sriddefine the the dimension of the geometry (2-, 3- or 4-dimensional) and the Spatial Reference system identifier that refers to the
typecolumn defines the type of geometry as described below; we've seen Point and Linestring types so far.
By querying this table, GIS clients and libraries can determine what to expect when retrieving data and can perform any necessary projection, processing or rendering without needing to inspect each geometry.