CODEWITHSUNDEEP
pythonpostgresqlflaskpostgisqgis
Paulo Renato
Paulo Renato

Reputation: 21

ST_WITHIN function not working in POSTGIS

Why does the SQL query using spatial functions (e.g., ST_Within) return 'Location not found' instead of 'Location found' when providing latitude and longitude coordinates that are within a polygon stored in the PostgreSQL/PostGIS database?

@app.get('/polygons/<latitude>/<longitude>')
def verify_polygon(latitude, longitude):
    try:
        conn = connect_db()
        cur = conn.cursor()

        cur.execute(f'SELECT id_0 FROM public."polygons-c3" WHERE ST_Within(ST_SetSRID(ST_MakePoint({longitude}, {latitude}), 4326), geom)')
        result = cur.fetchone()
        cur.close()
        conn.close()

        if result:
            return jsonify({'status': 'Location found', 'lote': result[0]}), 200
        else:
            return jsonify({'status': 'Location not found'}), 404
    except Exception as e:
        return jsonify({'error': str(e)}), 500

I expected "location found" because i get a point with Google maps that is on one of those polygons

Upvotes: 2

Views: 140

Answers (1)

Zegarek
Zegarek

Reputation: 26322

ST_MakePoint() accepts x+y, lon+lat, in that order.

I get a point with Google maps that is on one of those polygons

My bet is you're comparing to coords straight from Google Maps r-click/URL, and those come in reverse order compared to that: y+x, lat+lon. For example, if you want to see near the North Pole it's

https://www.google.com/maps/@85.0,1.0,4.0z

The parameter pair and what's shown if you right-click is 85° North, 1° West, (4.0 zoom). To get that location in PostGIS, you'd use the coordinates in the opposite order

ST_SetSRID(ST_MakePoint(1,85),4326)

Make sure you're not passing latitude as longitude and vice versa. If you're accidentally flipping them, above becomes:

ST_SetSRID(ST_MakePoint(85,1),4326)

Which is somewhere south of Sri Lanka (85° West, 1° North), near the equator (hot), not Greenland (cold). If you flip the coords back, your test points should fit your polygons fine.

Upvotes: 0

Related Questions