The constructor functions are used to create new geometries. While it’s natural to think of using these functions to populate a table’s geometry column, you should keep in mind that they can also be used to create short-lived geometries that are used only within a query. For example, you might use the ST_MakeEnvelope() constructor function to create a bounding box that you then use to select intersecting geometries in some table.
We used this function throughout Lesson 3 to create various types of geometries. In the documentation, you may see that it takes Well-Known Text (WKT) as its input. The “Well-Known” in Well-Known Text refers to the fact that the text follows an industry standard for specifying geometries.
There are a number of other “FromText” functions (e.g., ST_LineFromText, ST_PointFromText) that produce the same result as ST_GeomFromText(). The difference in these functions is that they validate the input text to ensure that it matches the expected geometry type. Thus, ST_PointFromText() will return Null if it is passed something other than a POINT geometry. If you don’t see any benefit to this validation, then you’re better served using the more generic ST_GeomFromText() as it performs a bit better.
At the end of Lesson 3, we talked about the geography data type. You can use ST_GeogFromText() to populate geography columns in the same way we used ST_GeomFromText() to populate geometry columns. Both functions allow for specifying or omitting the SRID of the coordinates. If omitted, ST_GeomFromText() makes no assumption about the SRID; it is set to 0. ST_GeogFromText(), on the other hand, assumes that the SRID is 4326 unless specified otherwise.
The "Make" functions
An alternative to creating geometries with ST_GeomFromText() are the “Make” functions: ST_MakePoint(), ST_MakeLine() and ST_MakePolygon(). These functions offer better performance than ST_GeomFromText() and their syntax is a bit more compact. So, why does ST_GeomFromText() even exist? Well, ST_GeomFromText() provides compliance with the Open Geospatial Consortium (OGC)’s Simple Features specification. The advantage to using ST_GeomFromText() is that other RDBMSs (Oracle, SQL Server) offer nearly identical functions; your familiarity with the function would transfer to those other environments. One drawback of the “Make” functions is that they do not take SRID as an input, which results in the returned geometry having an undefined SRID. Thus, calls to these functions are normally nested inside a call to ST_SetSRID.
Given the advantages offered by the “Make” functions, let’s have a look at their use.
As you’d probably guess, this function accepts an X coordinate and a Y coordinate (and optionally Z and M values) as inputs. Here is how to add the Empire State Building to our nyc_poi.pts table using ST_MakePoint():
INSERT INTO pts (name, geom) VALUES ('Empire State Building', ST_SetSRID(ST_MakePoint(-73.985744, 40.748549),4269));
This function has three alternative syntaxes. The first simply accepts two point geometries as inputs and returns a straight line connecting the two. We could use this syntax to add the Lincoln Tunnel feature:
INSERT INTO lines (name, geom) VALUES ('Lincoln Tunnel', ST_SetSRID(ST_MakeLine(ST_MakePoint(-74.019921, 40.767119), ST_MakePoint(-74.002841, 40.759773)),4269));
The second syntax is used when the points that make up the line are stored in a table. The field containing the points is specified as the only input to the function. This example from the PostGIS documentation shows creating a line from a set of GPS points:
SELECT gps.gps_track, ST_MakeLine(gps.the_geom) As newgeom FROM (SELECT gps_track, gps_time, the_geom FROM gps_points ORDER BY gps_track, gps_time) As gps GROUP BY gps.gps_track;
In the example, a table called gps_points stores a track identifier (gps_track), the time (gps_time) and the point geometry (the_geom). The data held in those three columns are retrieved as a subquery with an alias of gps. The parent query groups by the track identifier and passes the point geometry field to the ST_MakeLine() function to create a line from the points.
The last ST_MakeLine() syntax accepts an array of points as its input. We could use this syntax to add the Holland Tunnel:
INSERT INTO lines (name, geom) VALUES ('Holland Tunnel', ST_SetSRID(ST_MakeLine(ARRAY[ST_MakePoint(-74.036486,40.730121), ST_MakePoint(-74.03125,40.72882), ST_MakePoint(-74.011123,40.725958)]),4269));
This example demonstrates the use of the Postgres ARRAY data type. Arrays are built using the ARRAY keyword followed by a list of items enclosed in square brackets.
The documentation shows a clever use of the array syntax, in which the centroids of polygons stored in a table called visit_locations are used as input to ST_MakeLine():
SELECT ST_MakeLine(ARRAY(SELECT ST_Centroid(the_geom) FROM visit_locations ORDER BY visit_time));
This function takes a LINESTRING representing the polygon’s exterior ring as an input. Optionally, an array of interior ring LINESTRINGs can be included as a second input. Let’s have a look at an example of both, starting with the simpler case:
INSERT INTO polys (name, geom) VALUES ('Central Park',ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(-73.973057 40.764356, -73.981898 40.768094, -73.958209 40.800621, -73.949282 40.796853, -73.973057 40.764356)')),4269));
In this example, I used ST_GeomFromText() to create the LINESTRING because supplying the string of points is much easier than if I had taken the ST_MakePoint() approach used for the Holland Tunnel example. In our previous uses of ST_GeomFromText(), we included the optional SRID argument but in this example I omitted it. Why? Because ST_MakePolygon() will return an SRID-less geometry no matter what, so it’s sensible to specify the SRID just once in the call to the ST_SetSRID() function.
And now, here’s an example that uses ST_MakePolygon() to cut out the reservoir from the Central Park polygon:
INSERT INTO polys (name, geom) VALUES ('Central Park',ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(-73.973057 40.764356, -73.981898 40.768094, -73.958209 40.800621, -73.949282 40.796853, -73.973057 40.764356)'), ARRAY[ST_GeomFromText('LINESTRING(-73.966681 40.785221, -73.966058 40.787674, -73.9649 40.788291, -73.963913 40.788194, -73.963333 40.788291, -73.962539 40.788259, -73.962153 40.788389, -73.96181 40.788714, -73.961359 40.788909, -73.960887 40.788925, -73.959986 40.788649, -73.959492 40.788649, -73.958913 40.78873, -73.958269 40.788974, -73.957797 40.788844, -73.957497 40.788568, -73.957497 40.788259, -73.957776 40.787739, -73.95784 40.787057, -73.957819 40.786569, -73.960801 40.782394, -73.961145 40.78215, -73.961638 40.782036, -73.962518 40.782199, -73.963076 40.78267, -73.963677 40.783661, -73.965694 40.784457, -73.966681 40.785221)')]),4269));
The ST_MakeEnvelope() function is used to create a rectangular box from a list of bounding coordinates: the box’s minimum x value, minimum y value, maximum x value, and maximum y value. While it’s rare that such a geometry would be used to depict a real-world feature, envelopes are often used as inputs to other functions (e.g., selecting all features that are within a bounding box). Here is an example that produces an envelope surrounding Pennsylvania:
SELECT ST_MakeEnvelope(-80.52, 39.72, -74.70, 42.27, 4269);
This example simply demonstrates the syntax of ST_MakeEnvelope(). Note that the SRID of the envelope is provided as the last input to the function. We’ll see a practical use for this envelope later in the lesson when we talk about the ST_Intersects() function.