Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
I tried underdark's solution, or a variation thereof: ALTER TABLE parcels2012_2 ADD x double precision; ALTER TABLE parcels2012_2 ADD y double precision; UPDATE parcels2012_2 SET x = ST_X(ST_PointOnSurface(the_geom)); UPDATE parcels2012_2 SET y = ST_Y(ST_PointOnSurface(the_geom)); However, I get an error: NOTICE: TopologyException: side location conflict at -80.206658911211804 26.809848924607316 ERROR: GEOS pointonsurface() threw an error! Any ideas? I can easily return single points, and even small subsets, but it can't handle all 625,000 polygons in one command. –  Todd Oct 17 '13 at 23:43
    
This is a question, not an answer. –  Mike T Oct 18 '13 at 0:21
    
I recommend that you ask a new Question if your research indicates that it has not already been answered here or elsewhere. –  PolyGeo Oct 18 '13 at 0:38
    
It has been answered (2 years ago) and with the correct answer marked –  djq Oct 18 '13 at 14:59
add comment

3 Answers 3

up vote 4 down vote accepted

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);
share|improve this answer
add comment

You must use ST_X function.

Assuming that you want to fill a column of a table that already has a geometry column with the x coordinate of the centroid you can use something like:

UPDATE test_table
SET x = ST_X(ST_Centroid(the_geom));

If you need something different post the complete structure of the table if you need more accurate help.

share|improve this answer
add comment

It is a Geometry (a Point in fact). What you are seeing is the text representation of the point as Well Known Binary (WKB) for debugging purposes you'd probably want well known text (WKT) (see http://postgis.refractions.net/docs/ch04.html#OpenGISWKBWKT),

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

Will show what you want to see.

To do the addition you need a geometry column rather than a double. For this you need to use addGeometryColumn which handles all the bookkeeping needed for you (things like adding the column to the geometry columns metadata etc).

share|improve this answer
    
Thanks @iant. Is there a way of accessing each element that returns? Something like ST_AsText(ST_Centroid(a.the_geom),1) so that I can get x and y separately. I do have a column called ` the_geom geometry` but I assume that is the default geometry of each element. –  djq Sep 24 '11 at 9:31
1  
Just found it - I used ST_X(ST_Centroid(a.the_geom)) and ST_Y(ST_Centroid(a.the_geom)) –  djq Sep 24 '11 at 10:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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