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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
