[GIS] Postgis AddGeometryColumn for WKT with different types i.e. Polygon and Multipolygon

geometrypostgiswell-known-text

I have some polygon data

first;1;MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))
second;2;POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))
third;3;POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))

and points

1,30,10
2,30,10
3,40,20
4,40,30
5,20,20
6,21,21

For these

CREATE TABLE polygons (
  id            CHARACTER VARYING(50),
  value            INT,
  geo                 TEXT
);
CREATE EXTENSION postgis;
CREATE TABLE points (
  id  CHARACTER VARYING(50),
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION
);

Then points are prepared like

COPY points (id, lat, lng)
FROM '/dataFiles/samples/points_sample.csv'
WITH
DELIMITER AS ','
CSV HEADER;

-- Add point geometry column to table
SELECT AddGeometryColumn('public', 'points', 'geom', 900913, 'POINT', 2);

-- Populate column with point geometries
UPDATE points
SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 900913);

-- Create a spatial index on points
CREATE INDEX idx_points
  ON points USING GIST (geom);

VACUUM ANALYZE points;

for the polygons I use

COPY polygons (sitename, db_range,geo)
FROM '/dataFiles/samples/geometry_sample.csv'
WITH
DELIMITER AS ';'
CSV HEADER;

However, when trying to convert to a geometry column I run into the following problem:

SELECT AddGeometryColumn('public', 'polygons', 'geom', 900913, 'WKT', 2);

will not take WKT but rather only POLYGON or MULTIPOLYGON. How should such a column be processed? To not have the statement below fail?

UPDATE polygons
SET geomW = ST_SetSRID(ST_GeomFromText(geo), 900913);

Best Answer

You are trying to combine two steps into one.
Your SQL here is only for adding a new geometry column to your Polygons table:
SELECT AddGeometryColumn('public', 'polygons', 'geom', 900913, 'WKT', 2);

That column has to have one of the recognized geometry types, Point, LineString, Polygon, etc. You are attempting to have it accept 'WKT', which is not an geometry type, but a "text markup language for representing said geometry", from Wikipedia Well-known text

What you need to do, is create your geometry column with a recognized geometry type, like:
SELECT AddGeometryColumn('public', 'polygons', 'geom', 900913, 'POLYGON', 2);
SELECT AddGeometryColumn('public', 'polygons', 'geom', 900913, 'MULTIPOLYGON', 2);

EDIT To be clear, when you are adding the geometry column, you only choose one geometry type. If you have both MultiPolygon and Polygon geometry, you will need to cast them one way or the other.

When you update your table, you will do the conversion from WKT to the database encoding. Assume your geometry column is MultiPolygon. Use ST_Multi to cast your data. Data already in Multi format will remain as such: 

UPDATE polygons
SET geom = ST_SetSRID(ST_Multi(ST_GeomFromText(geo)), 900913);

Hat tip to @Underdark with her answer to: How to convert/typecast from Polygon to MultiPolygon in PostGIS

Related Question