How can I insert a bounding box or polygon in a PostGIS geometry field?

Question

I have a field in my table called 'geometry' that was created thus:

CREATE TABLE "public"."sites" (
    "id" int8 DEFAULT nextval('sites_id_seq' :: regclass) NOT NULL,
    #... A few MORE fields 
    "geometry" "public"."geometry",
    CONSTRAINT "sites_pkey" PRIMARY KEY ("id")
) WITH (OIDS = FALSE);
CREATE INDEX "index_sites_on_geometry" ON "public"."sites" USING gist (geometry);

I can create a point with the command:

UPDATE sites
SET geometry = ST_SetSRID (
    ST_MakePoint (44.28, - 96.68, 495),
    4326
),
WHERE
    ID = 12;

I would like to create a bounding box, but being new to the syntax have not been able to get the correct syntax.

My best attempt:

UPDATE sites
SET geometry = ST_SetSRID (
    ST_3DMakeBox (
        ST_MakePoint (44.2, - 96.6, 400),
        ST_MakePoint (44.3, - 97.0, 400)
    ),
    4326
)
WHERE
    ID = 1123;

Gives the error:

Column has Z dimension but geometry does not

I get this error even after trying

1. wrapping the ST_SetSRID(...) in a select statement as some online examples show (though the select statement returns a 2D result)
2. using ST_Envelope(ST_Makeline as suggested by @mattmakesmaps

My apologies that I am new to this. I have been looking through the documentation and googling postgis insert bouding box etc, but at my level, this isn't clear.

Answer 1

You can use St_GeomFromText to add geometries in WKT format

UPDATE sites
SET  geometry = st_geomfromtext('POLYGON((0 0, 1 1, 2 2, 3 3, 0 0))') 
WHERE ID = 1123

Answer 2

You may want to use the ST_Envelope() function.

Specifically, see the 2nd example using a LINESTRING geometry. Given a LINESTRING composed of two vertices that represent your points, you should be able to get back a Polygon geometry which might be more useful to you.

Answer 3

Perhaps you may need to think through the data type for sites. What is it for? It seems that geometry is defined as geometry(PolygonZ,4326), which I think is a bit silly. A polygon could be expressed as a 3D object if all the linear rings are coplanar (like a triangle or a lake). However, if it were, for example, the border of Nepal, why keep elevations with these coordinates? I'd argue it's nonsense, since you can't visualize this kind of PolygonZ.

If you want to keep the Z dimension, use ST_Force3D (or ST_Force_3D for older versions), which will assign a default Z of 0. Otherwise, you may want to change the geometry type to geometry(Polygon,4326). Lastly, you could always use a box3d type, if you want to only store 3D boxes.

Lastly, your axis order is reversed. It is always (long, lat) for PostGIS.