CODEWITHSUNDEEP
postgisspatialitegeoalchemy2
user3772884
user3772884

Reputation: 11

ST_Contains and ST_Within returning -1 values when comparing points and polygon/multipolygons using GeoAlchemy2 and Spatialite

I'm using Spatialite and GeoAlchemy2 ORM and trying to identify the Neighbourhood that a given point falls within.

My neighbourhoods are stored as <class 'geoalchemy2.elements.WKBElement'> converted from GeoJSON files. I've tested these and they successfully render the shapes I'm looking to plot.

I've got my points stored as EWKs (they're strings now, but they will be EWKs in the database when I move past testing).

I'm not really seeing any result that makes sense and am pulling my hair out here.

from geoalchemy2.functions import ST_Contains, ST_Within
from sqlalchemy import select

from app import app, db
from app.models import Neighbourhood

point = 'Point (-79.3438 43.7377)'

with app.app_context():
    query = select(Neighbourhood.name).where(ST_Within(Neighbourhood.geometry, point))
    results = db.session.scalars(query).all()

Above is a small sample of what I'm trying to run. What's odd is that the values from ST_Contains is returning -1, not a Boolean value as expected.

https://github.com/whosonfirst-data/whosonfirst-data-admin-ca/blob/d2ebb5f61dc8b6384a01c93e69aa50000dceb153/data/858/952/13/85895213-alt-quattroshapes.geojson

This shape exists within my database and should be the returned value, however it just does not seem to work.

Note - I've tried every combination of lat/lon in my points as well as ST_Within vs ST_Contains, and swapping the order of my geometry as well as point. If they're wrong above it's because my brain is now fried and I don't know what's going on. :(

Any help would be greatly appreciated. I'm spinning tires here.

Upvotes: 0

Views: 72

Answers (1)

user3772884
user3772884

Reputation: 11

So I did end up solving this. It was a really basic fix... I ended up having to cast my points with ST_Point within the query itself.

from geoalchemy2.functions import ST_Point
from sqlalchemy import select, func

from app import app, db
from app.models import Neighbourhood

lon = -79.3808706120814
lat = 43.681118858992335
pnt = ST_Point(lon,lat)

with app.app_context():
    query = select(Neighbourhood.name).filter(func.ST_Contains(Neighbourhood.geometry,pnt))
    results = db.session.scalars(query).all()
results

Upvotes: 1

Related Questions