-2

I have recently installed PostGIS for the first time. I'm trying to use psycopg2 to create some polygon features.

This runs:

cur.execute('CREATE TABLE test_insert_shape (uuid varchar PRIMARY KEY, wkt polygon);')

Then this runs:

cur.execute('CREATE INDEX test_insert_shape_polygon_idx ON test_insert_shape USING GIST (wkt);')

Then this fails: wkt comes from shapely and should not be the problem Doesn't matter if I use different names for wkt, the error is the same while using wkt geometry (Polygon) makes a new error Missing datattype geometry

cur.execute(f'INSERT INTO test_insert_shape (uuid, wkt) VALUES (\'iaufiubasdfasdfgsd\', ST_GeomFromText( \'POLYGON ((689370 7724110, 689370 7724130, 689350 7724130, 689330 7724130, 689330 7724170, 689350 7724170, 689350 7724230, 689370 7724230, 689370 7724210, 689410 7724210, 689430 7724210, 689430 7724130, 689410 7724130, 689410 7724110, 689370 7724110))\', 25833);')

Error:

LINE 1: SELECT ST_GeomFromText( 'POLYGON (( ^ Hint: No function matches the given name and argument types. (ST_GeomFromText)

Then I try to enable postgis extension:

try:
    cur.execute('CREATE EXTENSION postgis;')
except Exception as e:
    print(e)

I get ProgrammingError: extension "postgis" already exists

I try to see what functions is available in the pgadmin under databasename.Schemas.public.Functions

I see none..

How can I enable this function?

Or is there a better way to insert polygon geometry?

For those used to FME. I actually get an error there to: ERROR |Error executing SQL command ('select version(), postgis_version()'): 'ERROR: function postgis_version() does not exist LINE 1: select version(), postgis_version()

versions: POSTGIS: 2.5 r15475 http://postgis.net/source CGAL VERSION: 4.11 http://www.cgal.org Boost VERSION: 1.59.0 http://www.boost.org GMP VERSION: 5.1.2 https://gmplib.org MPFR VERSION: 3.1.2 http://www.mpfr.org PROTOBUF VERSION: 3.2.0 https://github.com/google/protobuf PROTOBUF-C VERSION: 1.2.1 https://github.com/protobuf-c/protobuf-c GEOS VERSION: 3.7.1 http://trac.osgeo.org/geos GDAL VERSION: 2.2.4 trac.osgeo.org/gdal PROJ VERSION: 4.9.3 trac.osgeo.org/proj CGAL VERSION: 4.11 cgal.org BOOST VERSION: 1.59.0 boost.org SFCGAL VERSION: 1.3.2 github.com/Oslandia/SFCGAL

  • Your title and body ask different questions. What you're really asking is "What is the correct format of WKT to represent polygons?" The answer is to not put parens around vertices. – Vince Feb 18 at 14:25
  • The format of wkt seen here is just my fast typing off it. I use shapely to get the correct wkt. – Paal Pedersen Feb 18 at 14:35
  • You have created your table to have a column "wkt" of type "polygon" which is a native PostgreSQL type postgresql.org/docs/8.3/datatype-geometric.html. What you want is to have a column that is of the Postgis type "geometry". – user30184 Feb 18 at 14:35
  • Thanks for the information. But it still complains about the missing function. – Paal Pedersen Feb 18 at 14:37
  • 1
    Please edit your question to include responses to comments, so that those seeking to help don't have to dig through the comments for clarification. – smiller Feb 18 at 14:46
3

You are using the PostgreSQL geometry type:

polygon

If you want to use PostGIS you need the PostGIS geometry type in the first query:

cur.execute('CREATE TABLE test_insert_shape (uuid varchar PRIMARY KEY, wkt geometry (Polygon) );')
  • Thanks for reply: I get this:psycopg2.ProgrammingError: type "geometry" does not exist – Paal Pedersen Feb 18 at 14:44
  • Sounds like a user roles issue. Try running in PGAdmin first. – HeikkiVesanto Feb 18 at 14:50
  • Try the suggestions in: stackoverflow.com/questions/6850500/… – HeikkiVesanto Feb 18 at 14:53
  • I have seen that post and it asks to Create extension postgis, which I have allready done. see original post. – Paal Pedersen Feb 18 at 14:58
  • Could there bee something wrong with the installation? running on windows.. – Paal Pedersen Feb 18 at 14:59
-2

it was indeed this line that was faulty:

cur.execute('CREATE TABLE test_insert_shape (uuid varchar PRIMARY KEY, wkt polygon);')

I change it and the following lines to:

cur.execute('CREATE TABLE test_insert_shape (uuid varchar PRIMARY KEY, wkb_geometry geometry);')

cur.execute('CREATE INDEX test_insert_shape_polygon_idx ON test_insert_shape USING GIST (wkb_geometry);')

cur.execute(f'INSERT INTO test_insert_shape (uuid, wkb_geometry) VALUES (\'iaufiubasdfasdfgsd\', ST_GeomFromText( \'POLYGON ((689370 7724110, 689370 7724130, 689350 7724130, 689330 7724130, 689330 7724170, 689350 7724170, 689350 7724230, 689370 7724230, 689370 7724210, 689410 7724210, 689430 7724210, 689430 7724130, 689410 7724130, 689410 7724110, 689370 7724110))\', 25833);')

someone did point this out in the comment, but I don't know why I failed to get it right the first time.

  • I forgot to tell. In the meanwhile I also did a re-installation where I choose not to create spatial databse upon installation. (my first installation took a long time to execute this process, where I had to kill the last process) In case there was something else causing the error. – Paal Pedersen Feb 18 at 17:18

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.