0

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).

ScreenShot of Data

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

  • Thanks, PolyGeo for editing. – Sahil Nagpal Mar 20 at 5:53
0

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) |           |          |
  • 1
    On a second thought, if you need to mix polygons/multipolygons, you may not want to constrain the column to multipolygons... – JGH Mar 19 at 12:52
  • psycopg2.DataError: Geometry SRID (0) does not match column SRID (4326) facing this issue now. I have changed the data type GEOMETRY to GEOMETRY(MULTIPOLYGON,4326) – Sahil Nagpal Mar 19 at 13:03
  • My data contains both polygon and multipolygon. – Sahil Nagpal Mar 19 at 13:22
  • ok, so you can keep the geometry column as you already have. The 1st part of the answer remains applicable (display vs storage) – JGH Mar 19 at 13:52

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.