Text File MultiPolygon/Polygon to PostgreSQL using Python/Psycopg2

Question

I am writing a python script attempting to insert GeoJSON Polygon coordinates into a Postgres table. I am writing a basic insertion code since I am new to GIS domain.

The code look like this:

createTableMain = "create table "+ tablename +" (id TEXT PRIMARY KEY DEFAULT NULL,Name TEXT DEFAULT NULL,type TEXT DEFAULT NULL ,wkt_geom GEOMETRY DEFAULT NULL)";
print(createTableMain)

cursor.execute(createTableMain)
    with open(datapath, 'r') as f:
        next(f)
        cursor.copy_from(f, tablename, sep='|')

conn.commit()
conn.commit()
cursor.close()
conn.close()

The data insert properly but the wkt_geom column does not contain data with multi-polygon and polygon format. It looks like this (seems like it as text).

I am reading the data from a text file, so I seek a solution with respect to that.

Answer 1

The column is of type geometry, so it does not contain the textual representation of the geometry. I would recommend using a column name to reflect this, like geom instead of wkt_geom.

To display its textual representation, you would use the function ST_AsText

with myTable as (select st_geomFromText('Polygon((0 0,0 1, 1 1, 1 0, 0 0))') geom)
SELECT ST_AsText(geom), geom
FROM myTable;
           st_astext            |                                                                                            geom
--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POLYGON((0 0,0 1,1 1,1 0,0 0)) | 01030000000100000005000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000
(1 row)

If you want, you can also create the table specifying it can accept mutlipolygons in a given coordinate system

CREATE TABLE myTable (
   id integer,
   geom geometry(MULTIPOLYGON,4326)
) ;

\d myTable
                        Table "public.mytable"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 id     | integer                     |           |          |
 geom   | geometry(MultiPolygon,4326) |           |          |