1

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?

4

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)
  • Was just typing this up myself. I can't imagine why ST_GeomFromGeoJSON should return POLYGON Z EMPTY on this input, so I reported it as a bug. – dbaston Aug 28 '18 at 17:50
  • @dbaston What do you believe is wrong, the Z part or the Empty part (or both?) – JGH Aug 28 '18 at 17:56
  • Both. I'd expect a parse error. – dbaston Aug 28 '18 at 17:58
  • Invalid, non-closing WKT select ST_GeomFromText('POLYGON((0 0,0 5,5 5,5 0))')yields ERROR: geometry contains non-closed rings. But select st_astext(ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[[0,0], [0,5], [5, 5], [5,0]]]}')); gives an invalid, non-closing polygon as a result: "POLYGON((0 0,0 5,5 5,5 0))". – user30184 Aug 28 '18 at 18:23
  • Yes, and my point is that obviously GeomFromJSON is not validated as strictly as GeomFromText. Perhaps @dbaston could mention also that in the bug report. – user30184 Aug 28 '18 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.