Date: Sun, 29 Oct 2006 10:10:55 -0700
From: Michael Fuhr <mike@fuhr.org>
To: vamsee movva <vamseejump@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: not able to execute query on spatial database.
Message-ID: <20061029171054.GA71279@winnie.fuhr.org>
References: <be5110d00610270734o3e202cc0o725340d144aff3c2@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <be5110d00610270734o3e202cc0o725340d144aff3c2@mail.gmail.com>

On Fri, Oct 27, 2006 at 09:34:21AM -0500, vamsee movva wrote:
> I am working with spatial tables. i have two spatial data tables,

Are you using PostGIS?  If so then you might get more help on the
postgis-users list.

http://postgis.refractions.net/mailman/listinfo/postgis-users

> one represents the whole state and another table represents the damaged
> locations in the state. My aim is to find number of damaged locations in
> every county or parish.
> Here i am giving the query i used to do this, could you please tell me
> whether i am doing right thing or not.
> 
> select count(*) from damagedlocations l1,county l2 where (l2.the_geom and
> l1.the_geom) and l2.parishid=particular_parishid;

You might be looking for something like this; it should return all
parish IDs that have damaged locations and the number of damaged
locations in each parish:

SELECT c.parishid, count(*)
FROM county AS c
JOIN damagedlocations AS d ON d.the_geom && c.the_geom
                          AND distance(d.the_geom, c.the_geom) = 0
GROUP BY c.parishid;

"d.the_geom && c.the_geom" restricts the result set based on bounding
box overlaps; this expression can take advantage of indexes on the
geometry columns so it's an efficient way to get a set of possible
matches (put another way, an efficient way to eliminate impossible
matches).  "distance(d.the_geom, c.the_geom) = 0" does the more
expensive work of finding certain matches.  You could instead use
"intersects(d.the_geom, c.the_geom)" but distance = 0 is often
faster (if two geometries intersect then the distance between them
is 0).

-- 
Michael Fuhr