Scribd
Upload
11 views1 page

PostGIS Geometry Type Conversion Guide

The document provides SQL commands to convert geometry types between polygon and multipolygon. It describes removing existing geometry type constraints, updating the geometry data using ST_Geometryn or ST_Multi functions, re-adding constraints for the new geometry type, and updating the geometry_columns metadata table.

Uploaded by

Ashima Gupta
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views1 page

PostGIS Geometry Type Conversion Guide

The document provides SQL commands to convert geometry types between polygon and multipolygon. It describes removing existing geometry type constraints, updating the geometry data using ST_Geometryn or ST_Multi functions, re-adding constraints for the new geometry type, and updating the geometry_columns metadata table.

Uploaded by

Ashima Gupta
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT rownum rn,t.Y, t.

X FROM sam_aoi
c,TABLE(SDO_UTIL.GETVERTICES(c.aoi_area)) t where c.aoi_name='43K1

ST_Geometryn
st_dump
st_union

update cultiv set the_geom = ST_Geometryn(the_geom,1) ---------- From


MultiPolygon to Polygon, MultiLineString to LineString and many more.

To Convert geom type from Multipolygon to Polygon

-- 1. Remove the geom_type constraint (if existing)


alter table cultiv DROP CONSTRAINT enforce_geotype_the_geom;

-- 2. Update the geometry data -- skip if it is an empty table


update cultiv set the_geom = ST_Geometryn(the_geom,1);

-- 3. Re-add a different geometry constraint for the new type


alter table cultiv add CONSTRAINT enforce_geotype_the_geom CHECK
(geometrytype(the_geom) = 'POLYGON'::text OR the_geom IS NULL);

-- 4. Update the geometry_columns metadata table


update geometry_columns set type = 'POLYGON' where f_table_schema = 'public' and
f_table_name = 'cultiv' and f_geometry_column = 'the_geom';

To Convert geom type from Polygon to Multipolygon

-- 1. Remove the geom_type constraint (if existing)


ALTER TABLE cultiv DROP CONSTRAINT enforce_geotype_geom;

-- 2. Update the geometry data to multi-part -- skip if it is an empty table


UPDATE cultiv SET the_geom = ST_Multi(the_geom);

-- 3. Re-add a different geometry constraint for the new type


ALTER TABLE cultiv ADD CONSTRAINT enforce_geotype_geom CHECK
(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR geom IS NULL);

-- 4. Update the geometry_columns metadata table


UPDATE geometry_columns SET type = 'MULTIPOLYGON' WHERE f_table_schema = 'public'
AND f_table_name = cultiv' AND f_geometry_column = 'the_geom';

Thanks & Regards


Surekha Batwal

You might also like