Introduction to PostGIS Geometries
This scenario is going give you an introduction to Geometries in PostGIS. You may remember geometries from your math classes in school and the word has the same meaning here. Point, lines, and polygons will form the building blocks for all the work we do in vector capabilities in PostGIS.
The database has already been started and the spatial data has already been loaded. This scenario will use data from New York City (NYC). If you want to dig in deeper on the data please go ahead and do this scenario first. Data from this scenario will be used in all the other exercises as well.
We have already logged you into the PostgreSQL command line but, if you get disconnected here are the details on the database we are connecting to:
- Username: groot
- Password: password (same password for the postgres user as well)
- A database named: nyc
And with that, let's dig in.
And with that, we are finished covering geometries. You can now go on and do some of the other scenarios that cover other analysis you can do with PostGIS.
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
Introduction to PostGIS Geometries
Introduction to Geometries and Metadata
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.