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.

Is there a PostGIS function that can change geometry type for an existing table?

We need to change from POINT to MULTIPOINT.

The table will be empty when we change the geometry type and we cannot just drop/create the table.

share|improve this question
add comment

2 Answers 2

For PostGIS 2.x, you can use the ALTER TABLE DDL using an expression.

To convert from a single-part to multi-part geometry, use ST_Multi:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(MultiPoint,4326) USING ST_Multi(geom);

To convert from a multi-part to a single-part geometry, it is a bit more tricky since you can only use one part, and ignore all other parts (if they exist). Check your data first to see if you have some geometries with more than one part:

SELECT COUNT(CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END) AS multi_geom,
       COUNT(geom) AS total_geom
FROM my_table;

If you see multi_geom greater than 0, then you will risk loosing data, and you should probably keep it as a multi-part geometry. If you see 0, then it is safe to make into a single-part geometry with:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_GeometryN(geom, 1);

For PostGIS 1.x, it is a bit more messy, as there are several steps (thanks @rec.thegeom!).

Assuming a table my_table and geometry column geom, here are the steps to convert to multi-part:

-- 1. Remove the geom_type constraint (if existing)
ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_geom;

-- 2. Update the geometry data to multi-part -- skip if it is an empty table
UPDATE my_table SET geom = ST_Multi(geom);

-- 3. Re-add a different geometry constraint for the new type
ALTER TABLE my_table ADD CONSTRAINT enforce_geotype_geom
  CHECK (geometrytype(geom) = 'MULTIPOINT'::text OR geom IS NULL);

-- 4. Update the geometry_columns metadata table
UPDATE geometry_columns SET type = 'MULTIPOINT'
WHERE f_table_name = 'my_table' AND f_geometry_column = 'geom';
share|improve this answer
    
Hi @Mike Toews (and Ulrik). I don't think your second step for PostGIS 1.x is necessary in this case, Mike. Ulrik said the table will be empty at the time of the type conversion, so there won't be any non-multi values to cause an error with something like: 1) ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_the_geom; 2) ALTER TABLE my_table ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOINT'::text OR the_geom IS NULL); then 3) UPDATE geometry_columns SET type = 'MULTIPOINT' WHERE f_table_name = 'my_table'; (perhaps the sloppiest comment ever - my bad) –  rec.thegeom Jul 11 '12 at 17:56
    
@rec.thegeom correct; with an empty table there would be nothing to update. Thanks for posting the actual commands! –  Mike T Jul 11 '12 at 18:47
add comment

Change, I don't think so. But you can create a new table with identical structure, except for the geom column, then run:

SELECT AddGeometryColumn('new-pt_table','geom',<SRID>,'MULTIPOINT',2);

INSERT INTO new_pt_table (attr1, attr2, attr3, ..., geom) 
SELECT attr1, attr2, attr3, ... , ST_Multi(geom) FROM old_pt_table;
share|improve this answer
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.