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?
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;