Thread: how to check if a point is contained in a polygon ?
Hello, I'm busy to work on an application where the user can select (with precision) an area on a map (for example the contours of a lake) and I have to retrieve all the data (specimen observations) within this area. I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, long3), (..., ...)] which form a polygon. In my database every specimen has a pair of coordinates where it has been collected. Is there an SQL function to check if a point is contained in a polygon shape (before I start to write my own) ? I tried something like : rodentia=> select point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; ERROR: operator does not exist: point <@ polygon HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. ... but without success as you can see ;\ In advance thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: jcigar@ulb.ac.be @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52
Julien Cigar wrote: > Hello, > > I'm busy to work on an application where the user can select (with > precision) an area on a map (for example the contours of a lake) and I > have to retrieve all the data (specimen observations) within this area. > > I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, > long3), (..., ...)] which form a polygon. In my database every specimen > has a pair of coordinates where it has been collected. Are you familiar with the PostGIS project - if you're doing a lot of this, it might be worthwhile looking into. http://www.postgis.org/ > Is there an SQL function to check if a point is contained in a polygon > shape (before I start to write my own) ? > > I tried something like : > > rodentia=> select point '(-8,25)' <@ polygon > '((-3,10),(8,18),(-3,30),(-10,20))'; Works both ways around here: SELECT point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)'; I'm running 8.3, but it seems to be in 8.2 too. Try \do '<@' from psql to see what operators are available. -- Richard Huxton Archonet Ltd
Oops, you're right .. I'm still running 8.1 and I missed this note in the documentation : "Note: Before PostgreSQL 8.2, the containment operators @> and <@ were respectively called ~ and @. These names are still available, but are deprecated and will eventually be retired." thanks On Wed, 2008-05-07 at 15:38 +0100, Richard Huxton wrote: > Julien Cigar wrote: > > Hello, > > > > I'm busy to work on an application where the user can select (with > > precision) an area on a map (for example the contours of a lake) and I > > have to retrieve all the data (specimen observations) within this area. > > > > I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, > > long3), (..., ...)] which form a polygon. In my database every specimen > > has a pair of coordinates where it has been collected. > > Are you familiar with the PostGIS project - if you're doing a lot of > this, it might be worthwhile looking into. > > http://www.postgis.org/ > > > Is there an SQL function to check if a point is contained in a polygon > > shape (before I start to write my own) ? > > > > I tried something like : > > > > rodentia=> select point '(-8,25)' <@ polygon > > '((-3,10),(8,18),(-3,30),(-10,20))'; > > Works both ways around here: > > SELECT point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; > > SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)'; > > I'm running 8.3, but it seems to be in 8.2 too. > > Try \do '<@' from psql to see what operators are available. > -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: jcigar@ulb.ac.be @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52
Julien Cigar <jcigar@ulb.ac.be> writes:
> Is there an SQL function to check if a point is contained in a polygon
> shape (before I start to write my own) ?
> I tried something like :
> rodentia=> select point '(-8,25)' <@ polygon
> '((-3,10),(8,18),(-3,30),(-10,20))';
> ERROR: operator does not exist: point <@ polygon
> HINT: No operator matches the given name and argument type(s). You may
> need to add explicit type casts.
> ... but without success as you can see ;\
I think you are reading recent documentation and trying to apply it to
an old Postgres version. <@ had some other name before 8.2 ... check
the docs for whatever you are running.
regards, tom lane