Error spatially subsetting and PostGIS database

Question

I am trying to make an spatial operation using sqlalchemy and geoalchemy2 on Python 3.5. I have a table with points as a geom attribute. I have 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 name of the columns of my table. Now, I want to create a spatial subset of the data selecting only the points that are inside a 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 defined SRID=4326. I have tried already with different forms of that same polygon, but none have worked. When executing the query, the following error returns:

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

Where I am failing?

put your polygon WKT. As the error says, a ring is probably not closed (for example the 1st and last point of each ring must be the same) — JGH, Jun 27 '17 at 12:50
`wkt_string = "POLYGON((141.0205078125 -9.166331387642987, 143.602294921875 -9.155485188844034, 143.67919921875 -11.112885070321443, 140.965576171875 -11.11288507032144))"` Is the POLYGON object. As you said, the POLYGON is indeed no valid. Nonetheless, I have tried to use `sqlalchemy.sql.func.ST_MakeValid` and the problem persist. Any ideas? — topcat, Jun 27 '17 at 12:56

JGH · Accepted Answer · 2017-06-27T13:39:42.203

The polygon 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 could construct the polygon from a line 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;

Error spatially subsetting and PostGIS database

1 Answers1