[GIS] Inserting polygon centroids into an additional column in PostGIS

postgispostgis-1.5sql

I'm trying to calculate the x and y centroids for a table using ST_Centroid. The documentation says that it returns the geometric center of a geometry. I first made a column X:

ALTER TABLE "test_table" ADD X double precision;

and was then planning on storing the x component of the centroid there. My code to calculate the centroid is as follows:

select
  ST_Centroid(a.the_geom) 
FROM 
  "test_table" AS a

However this query returns the following result which I don't understand:

"01010000006BAEC2FBDFC61E415B3E9E2946520541"
"0101000000E6ABADEBFEC51E4102E9A95858520541"
"01010000003233333321C61E410BD7A37061520541"
"0101000000F710787300C61E4177C8C783D94E0541"

My second problem with this approach is trying to store the result. When I tried to insert this result into Column X I could not figure out the correct syntax. What I tried using was:

INSERT INTO "test_table" (X)
VALUES ( ST_Centroid(a.the_geom) ),
FROM "test_table" AS a

and also

INSERT INTO "test_table" (X)
VALUES ( SELECT ST_Centroid(a.the_geom) 
FROM "test_table" AS a)

but neither of these work.

My two very basic questions are (1) what is the value being returned by ST_Centroid, and (2) how I can I insert the result into an existing table?

Best Answer

If you want x and y columns:

ALTER TABLE "test_table" ADD x double precision;
ALTER TABLE "test_table" ADD y double precision;
UPDATE "test_table" SET x = ST_X(ST_Centroid(the_geom));
UPDATE "test_table" SET y = ST_Y(ST_Centroid(the_geom));

If you rather want a geometry column (adjust schema name and CRS to fit your needs):

SELECT AddGeometryColumn ('test_schema','test_table','centroid_geom',4326,'POINT',2);
UPDATE "test_table" SET centroid_geom = ST_Centroid(the_geom);
Related Question