1

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);
4

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

  • but for me it was not possible to call AddGeometryColumn twice on the same column (here geom) with different WKT types. Should this really work? – Georg Heiler Mar 21 '17 at 17:33
  • 1
    See edit to my answer. Short version: Choose one, then cast your data to one geom type or the other. – Get Spatial Mar 21 '17 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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