Spatial Subset and PostGIS Database Error

Question

I am trying to do a spatial operation using sqlalchemy and geoalchemy2 in Python 3.5. I have a table with dots as a geom attribute. I already read the table and follow the documentation instructions:

 metadata = MetaData() table = Table('table', metadata, autoload=True, schema = "schema", autoload_with=engine) print(table.columns) 

This correctly returns me the column name of my table. Now I want to create a spatial subset of data that selects only those points that are inside the POLYGON object. I tried with ST_Contains and ST_Intersection :

 # Create session for queries Session = sessionmaker(bind=engine) session = Session() #SELECT * FROM table: q = session.query(table).filter(table.c.geom.ST_Intersects(func.GeomFromEWKT(<POLYGON>))) 

POLYGON is a WKT geometry with a specific SRID=4326 . I already tried to use different forms of the same polygon, but no one worked. When executing the query, the following error is returned:

 (psycopg2.InternalError) geometry contains non-closed rings HINT: "...140.965576171875 -11.11288507032144))" <-- parse error at position 166 within geometry 

Where am I failing?

Answer 1

The landfill you are using is not closed. The first and last coordinates must be the same. Change it to:

 wkt_string = "POLYGON((141.0205078125 -9.166331387642987, 143.602294921875 -9.155485188844034, 143.67919921875 -11.112885070321443, 140.965576171875 -11.11288507032144, 141.0205078125 -9.166331387642987))" 

Alternatively, you can build a polygon from a string and automatically add the missing point

 SELECT ST_MakePolygon( ST_AddPoint(foo.open_line, ST_StartPoint(foo.open_line))) FROM ( SELECT ST_GeomFromText( 'LINESTRING(141.0205078125 -9.166331387642987, 143.602294921875 -9.155485188844034, 143.67919921875 -11.112885070321443, 140.965576171875 -11.11288507032144)') As open_line) As foo;