Insert data to a spatial database in postgis-CodePudding

I have create a table which has 3 fields: id(integer), name(char) and surface(polygon). When i try to insert a new row in this table by executing:

INSERT INTO public."Area" ("ID", "Name", "Surface") VALUES(0, 'myArea', POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)));

I get this error:

SQL Error [42601]: ERROR: syntax error at or near "0" Position: 84

I searched in the documentation of postgis etc but i didnt find out how to define this field (same for points and linestring).

I also tried something like this:

INSERT INTO public."Area" ("ID", "Name", "Surface") VALUES(1, 'myArea', ST_GeomFromText('POLYGON((8 4, 10.5 4, 10.5 1.5, 8 1.5, 8 4))'));

but the result was :

SQL Error [42804]: ERROR: column "Surface" is of type polygon but expression is of type geometry Hint: You will need to rewrite or cast the expression. Position: 76

Could you please help me to find out which is my mistake?

Thank you a lot for your time!

CodePudding user response：

Try ST_PolygonFromText instead of ST_GeomFromText.

Or declare the column as generic GEOMETRY, rather than POLYGON type.

CodePudding user response：

The error message tells the column is of type polygon, which is a native postgres type. What one can do with this type is very limited and if you are working with spatial data, using Postgis and its geometry type is indicated.

To create a column containing geometries of the type polygon, with the projection 4326, you would do

CREATE TABLE test (my_poly geometry(polygon,4326));

You can then load data using ST_GeomFromText.

Since your column is of geometry type, there is an automatic cast from text to geometry, or you can force it. Unlike in your 1st query, note that the polygon definition must be enclosed between quotes.

insert into test values ('polygon((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry)

At last, it is technically possible to create a Postgis geometry and to cast it to Postgres native polygon type (SELECT ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')::polygon;), but doing so brings very little benefit since you still won't have access to most of Postgis functions (compute distances, reproject, compute intersections etc)