Thread: not able to execute query on spatial database.
Hello all,<br /> I am working with spatial tables. i have two spatial data tables, one represents the whole stateand another table represents the damaged locations in the state. My aim is to find number of damaged locations in everycounty or parish.<br /> Here i am giving the query i used to do this, could you please tell me whether i am doing rightthing or not.<br /><br /><span style="font-weight: bold;">select count(*) from damagedlocations l1,county l2 where (l2.the_geomand l1.the_geom) and l2.parishid=particular_parishid;<br /><span style="font-weight: bold;"><br /><span style="font-weight:bold;"></span></span></span>thanks in advance.<br /> vamsee movva<span style="font-weight: bold;"><spanstyle="font-weight: bold;"><span style="font-weight: bold;"></span><br /></span></span>
On 10/27/06, vamsee movva <vamseejump@gmail.com> wrote:
What is your table structure? I think you mean (l2.the_geom = l1.the_geom) . If you use l2.parishid=particular_parishid then you are not going to get EVERY country or parish.
Have you run this query? If so, what are you getting, is it right and if so why not?
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
Hello all,
I am working with spatial tables. i have two spatial data tables, 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;
thanks in advance.
vamsee movva
What is your table structure? I think you mean (l2.the_geom = l1.the_geom) . If you use l2.parishid=particular_parishid then you are not going to get EVERY country or parish.
Have you run this query? If so, what are you getting, is it right and if so why not?
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.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