PostGIS : ST_Contains doesn't work after ST_FlipCoordinates

Question

I'm using postGis in a personal project, mainly to determine if a point is inside a Polygon. I realized I was using lat/long instead of long/lat for creating my polygons and using ST_Contains. Thus i used ST_FlipCoordinates

update partnerarea set area = st_flipcoordinates(area)::geometry(Polygon,4326);

But since, I can't manage to have a correct response from ST_Contains.

Here some examples:

. Before flip

SELECT st_AsEWKT(area), ST_Contains(area, ST_GeomFromText('POINT(51.7261050412711 -0.440412894822658)',4326) ) from partnerarea where id=212;
st_asewkt                                                       |st_contains
 -------------------------------------------------------------------------------------------------------------------------------------------------+-------------

SRID=4326;POLYGON((51.7261050412711 -0.440412894822658,51.7158961385226 0.103410347364843,51.5624858755914 0.320390327833593,51.3090817887527 0.202287300489843,51.257544662456 0.114396675489843,51.2317544106116 -0.264631644822657,51.3176656880587 -0.542036429978907,51.4633463581247 -0.583235160447657,51.6256173634884 -0.555769340135157,51.7261050412711 -0.440412894822658)) | t

After flip :

SELECT st_AsEWKT(area), ST_Contains(area, ST_GeomFromText('POINT(-0.440412894822658 51.7261050412711)',4326) ) from partnerarea where id=212;
st_asewkt                                                       |st_contains
 -------------------------------------------------------------------------------------------------------------------------------------------------+-------------
SRID=4326;POLYGON((-0.440412894822658 51.7261050412711,0.103410347364843 51.7158961385226,0.320390327833593 51.5624858755914,0.202287300489843 51.3090817887527,0.114396675489843 51.257544662456,-0.264631644822657 51.2317544106116,-0.542036429978907 51.3176656880587,-0.583235160447657 51.4633463581247,-0.555769340135157 51.6256173634884,-0.440412894822658 51.7261050412711)) | f

As you can see, all the coordinates of my polygon have been correctly switched but the call of ST_Contains return false even if I switch the coordinates of the point. Is there something I'm missing here?

Answer 1

What feels odd to me is that your reference point

POINT(51.7261050412711 -0.440412894822658)

is the same as the first vertex of the polygon

POLYGON((51.7261050412711 -0.440412894822658....

However, if the point is on the border then Contains is not true which you can test with

select ST_Contains(ST_GeomFromText('POLYGON((51.7261050412711 -0.440412894822658,51.7158961385226 0.103410347364843,51.5624858755914 0.320390327833593,51.3090817887527 0.202287300489843,51.257544662456 0.114396675489843,51.2317544106116 -0.264631644822657,51.3176656880587 -0.542036429978907,51.4633463581247 -0.583235160447657,51.6256173634884 -0.555769340135157,51.7261050412711 -0.440412894822658))',4326),
ST_GeomFromText('POINT(51.7261050412711 -0.440412894822658)',4326))

However, Intersects is true in this case

select ST_Intersects(ST_GeomFromText('POLYGON((51.7261050412711 -0.440412894822658,51.7158961385226 0.103410347364843,51.5624858755914 0.320390327833593,51.3090817887527 0.202287300489843,51.257544662456 0.114396675489843,51.2317544106116 -0.264631644822657,51.3176656880587 -0.542036429978907,51.4633463581247 -0.583235160447657,51.6256173634884 -0.555769340135157,51.7261050412711 -0.440412894822658))',4326),
ST_GeomFromText('POINT(51.7261050412711 -0.440412894822658)',4326))

Read also http://postgis.net/docs/ST_Contains.html

ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

That you got true with your first query is probably because the coordinates converted into WKT are not accurate and your reference point really is inside the polygon and not on the border.

Answer 2

If someone encounters the same problem, I wrote a function that solved it

CREATE OR REPLACE FUNCTION  homeflip() RETURNS integer AS $func$
DECLARE
  area_cursor CURSOR IS select * from your_table;
  area_geom geometry(Polygon,4326);
  nbLines integer := 0;
BEGIN
  RAISE NOTICE 'START homeflip';
      FOR area_var IN area_cursor LOOP
    area_geom := st_flipcoordinates(area_var.your_column)::geometry(Polygon,4326);
    update your_table set your_column = area_geom where id = area_var.id;
    nbLines := nbLines+1;
  END LOOP;
  RAISE NOTICE 'END homeflip';
  return nbLines;
END;
$func$ LANGUAGE plpgsql;