Thread: searching polygons

What query would i have to use to search for an item using a polygon as a
parameter? (i.e a very large polygon that would identify smaller polygons
within it) ideally i would like to give postgresq a series of co-ordinates
and then have it return all those results whose polygons fall into that set
of co-ordinates, is this possible?

at the moment all i can think of is


select * from species where location between '(0,0)' and  '(1000,0)' and
'(0, 1000)' and '(1000; 1000)';

I think im way off, any suggestions?

Cheers Dave

You should use some variation of overlaps or
contains within.  There is some discussion and
a list of operators in Issue #61 of General Bits.
( http://www.varlena.com/GeneralBits/61 )

I would also suggest looking at the geometric
operators in the documentation.  You may have
to cast the polygon to a circle to use the operators,
but it will still tell you whether the smaller polys
are contained within or overlap the larger.

elein

On Tue, Feb 17, 2004 at 07:01:51PM -0000, David wrote:
> What query would i have to use to search for an item using a polygon as a
> parameter? (i.e a very large polygon that would identify smaller polygons
> within it) ideally i would like to give postgresq a series of co-ordinates
> and then have it return all those results whose polygons fall into that set
> of co-ordinates, is this possible?
> 
> at the moment all i can think of is
> 
> 
> select * from species where location between '(0,0)' and  '(1000,0)' and
> '(0, 1000)' and '(1000; 1000)';
> 
> I think im way off, any suggestions?
> 
> Cheers Dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org

Hi elin thanks for advice, i looked at your website but i didnt really
understand the information given, sorry. However i looked at the postgres
documentation and realised what i had to do

select * from species where location[1] @ polygon '((0,0), (1000,0),
(1000,1000), (0,1000))':

This works fine for just the one location ([1]), but when i tried to search
the entire array of polygons using the query:

select * from species where location @ polygon '((0,0), (1000,0),
(1000,1000), (0,1000))':

i got this:

Error: ' and 'polygon'
You will have to retype this query using an explicit cast (State:S1000,
Native Code: 7)

I am not sure how to do this, any suggestions
Many thanks Dave


----- Original Message ----- 
From: "elein" <elein@varlena.com>
To: "David" <de4@kent.ac.uk>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, February 22, 2004 2:12 AM
Subject: Re: [SQL] searching polygons


> You should use some variation of overlaps or
> contains within.  There is some discussion and
> a list of operators in Issue #61 of General Bits.
> ( http://www.varlena.com/GeneralBits/61 )
>
> I would also suggest looking at the geometric
> operators in the documentation.  You may have
> to cast the polygon to a circle to use the operators,
> but it will still tell you whether the smaller polys
> are contained within or overlap the larger.
>
> elein
>
> On Tue, Feb 17, 2004 at 07:01:51PM -0000, David wrote:
> > What query would i have to use to search for an item using a polygon as
a
> > parameter? (i.e a very large polygon that would identify smaller
polygons
> > within it) ideally i would like to give postgresq a series of
co-ordinates
> > and then have it return all those results whose polygons fall into that
set
> > of co-ordinates, is this possible?
> >
> > at the moment all i can think of is
> >
> >
> > select * from species where location between '(0,0)' and  '(1000,0)' and
> > '(0, 1000)' and '(1000; 1000)';
> >
> > I think im way off, any suggestions?
> >
> > Cheers Dave
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>