Re: not able to execute query on spatial database.

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