0

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?

1
  • I would try if deleting and re-creating spatial index helps.
    – user30184
    Jul 6 '15 at 17:10
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.

2
  • You're right regarding the point. I must have mixed up my request during my attempts to make it work. However I have the same behaviour with an point inside my area : SELECT ST_Contains(area, ST_GeomFromText('POINT(-0.454296 51.517297)',4326) ) from partnerarea where id=212; return false but if I flip back my area, SELECT ST_Contains(area, ST_GeomFromText('POINT(51.517297 -0.454296)',4326) ) from partnerarea where id=212; returns true Jul 7 '15 at 8:42
  • We will need data to reproduce your issue in an easy and reliable way. One or two polygons in WKT format and with only few vertices should be enough. SQL for CREATE TABLE... and INSERT INTO... would be excellent.
    – user30184
    Jul 7 '15 at 13:39
0

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;

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.