Determining if Point is in Polygon using PostGIS?

Question

I want to check if a point is inside a fenced polygon.

INSERT INTO polygon (name, geom) VALUES ('test', ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[0,0], [0,5], [5, 5], [5,0]]}'));

SELECT * FROM polygon WHERE name = 'test' AND ST_Intersects(geom, 'POINT(2 2)');
SELECT * FROM polygon WHERE name = 'test' AND ST_Contains(geom, 'POINT(2 2)');

Ideally, I'd expect it to return the row inserted as it should lie within the area. But I get 0 rows for both queries.

I also tried with much bigger, and more realistic data (using lat/long from a map), but I simply cannot get it working and the docs don't seem to help either?

Answer 1

The polygon is not defined properly, making it an empty geometry. You need to add an extra square bracket. The doc says that polygons are defined by an array of linear rings, to allow having holes.

While at it, let's make a valid polygon by having the same first and last coordinate.

select st_astext(ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[0,0], [0,5], [5, 5], [5,0]]}'));
    st_astext
-----------------
 POLYGON Z EMPTY
(1 row)


select st_astext(ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[[0,0], [0,5], [5, 5], [5,0], [0,0]]]}'));
         st_astext
----------------------------
 POLYGON((0 0,0 5,5 5,5 0,0 0))
(1 row)