1

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.

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
  • 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
1

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
  • 1
    ST_Envelope drops the Z dimension, making any geometry a 2D polygon. – Mike T Aug 21 '14 at 1:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.