Geometry Accessors

Geometry Accessors

Unlike the previous category of functions which were concerned with creating new geometries, this category involves functions used to retrieve information about geometries that already exist.

GeometryType()
This function returns the input geometry’s type as a string (e.g., ‘POINT’, ‘LINESTRING’, or ‘POLYGON’). It comes in particularly handy when dealing with a table of mixed geometries. Here we retrieve just the lines from the mixed nyc_poi table:

SELECT name FROM nyc_poi.mixed WHERE GeometryType(geom) = 'LINESTRING';

ST_X() and ST_Y()
These functions take a point as input and return its X or Y coordinate in numeric form. Similar functions exist for the M and Z coordinates as well. Here we get the coordinates of our nyc_poi pts data:

SELECT name, ST_X(geom), ST_Y(geom) FROM nyc_poi.pts;

ST_StartPoint() and ST_EndPoint()

These functions take a LINESTRING or POLYGON as input and return the first and last vertex of that geometry. Here is an example based on our nyc_poi lines table:

SELECT name, ST_AsText(ST_StartPoint(geom)), ST_AsText(ST_EndPoint(geom))
    FROM nyc_poi.lines;

Note that this example, and many others throughout this section, use the ST_AsText() function to output the returned geometry in a more human-friendly WKT.

ST_NPoints()
This function returns the number of points (vertices) that define the input geometry. Here we get the number of vertices from the states table:

SELECT name, ST_NPoints(geom) FROM usa.states ORDER BY name;

ST_Envelope()
This function accepts any type of geometry and returns that geometry’s minimum bounding box. Here we get the bounding box for Pennsylvania:

SELECT ST_AsText(ST_Envelope(geom)) FROM usa.states WHERE name = 'Pennsylvania';

ST_ExteriorRing()
This function takes a polygon as input and returns its exterior ring as a LINESTRING.  Example:

SELECT name, ST_AsText(ST_ExteriorRing(geom)) FROM nyc_poi.polys;

ST_NumInteriorRings()
This function takes a polygon as input and returns the number of interior rings it contains.  Example:

SELECT name, ST_NumInteriorRings(geom) FROM nyc_poi.polys;

ST_InteriorRingN()
This function takes a polygon and interior ring number as inputs and returns that ring as a LINESTRING. Note that the rings are numbered beginning with 1. This may seem obvious, but in many programming contexts items are numbered beginning with 0. Here we retrieve the Central Park reservoir ring:

SELECT name, ST_AsText(ST_InteriorRingN(geom,1)) FROM nyc_poi.polys;