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

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.

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

• when I wrap your example in select st_geomfromtext('POLYGON((0 0, 1 1, 2 2, 3 3))') I get " ERROR: geometry contains non-closed rings" – David LeBauer Aug 21 '14 at 0:56
• Ooops sorry. Coordinates should end with the first point. I will edit – amenadiel Aug 21 '14 at 1:02

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.

• I still get the error "Column has Z dimension but geometry does not" with update sites set geometry = ( select ST_SetSRID(ST_Envelope(ST_Makeline(ST_MakePoint(42.794448, -76.116081, 415),ST_MakePoint(42.79231, -76.116679, 415))),4326)) where id = 1123 – David LeBauer Aug 20 '14 at 23:09
• Is the column you're trying to load these data into a 3d column? If so you can use the ST_Force_3D function, as seen in this previous question – mattmakesmaps Aug 20 '14 at 23:31
• Based on your above example, the following SQL Statement outputs a Polygon Z geometry: SELECT ST_AsText(ST_Force_3D(ST_SetSRID(ST_Envelope(ST_Makeline(ST_MakePoint(42.794448, -76.116081, 415),ST_MakePoint(42.79231, -76.116679, 415))),4326))) AS geometry – mattmakesmaps Aug 20 '14 at 23:32
• The output is as follows: geometry ------------------------------------------------------------------------------------------------------------------------------- POLYGON Z ((42.79231 -76.116679 0,42.79231 -76.116081 0,42.794448 -76.116081 0,42.794448 -76.116679 0,42.79231 -76.116679 0)) (1 row)  – mattmakesmaps Aug 20 '14 at 23:33
• ST_Envelope drops the Z dimension, making any geometry a 2D polygon. – Mike T Aug 21 '14 at 1:32

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.

• We will want to keep elevation information, primarily because it informs the physical properties of the site (i.e., meteorology, hydrology). – David LeBauer Aug 22 '14 at 17:07
• That's not what I said. I was specifically singling out the meaning of a PolygonZ geometry. On the other hand, I think PointZ and LineStringZ are fantastic geometry types for features with elevation. – Mike T Aug 23 '14 at 1:18