Обсуждение: 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
==================================================================

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