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.
Date: Sun, 29 Oct 2006 10:10:55 -0700
Message-ID: <20061029171054.GA71279@winnie.fuhr.org> (raw)
In-Reply-To: <be5110d00610270734o3e202cc0o725340d144aff3c2@mail.gmail.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, vamseejump@gmail.com
Subject: Re: not able to execute query on spatial database.
In-Reply-To: <20061029171054.GA71279@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox