pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednot able to execute query on spatial database.
3+ messages / 3 participants
[nested] [flat]
* not able to execute query on spatial database.
@ 2006-10-27 14:34 vamsee movva <vamseejump@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: vamsee movva @ 2006-10-27 14:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not able to execute query on spatial database.
@ 2006-10-27 15:25 Aaron Bono <postgresql@aranya.com>
parent: vamsee movva <vamseejump@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Aaron Bono @ 2006-10-27 15:25 UTC (permalink / raw)
To: vamsee movva <vamseejump@gmail.com>; +Cc: pgsql-sql
On 10/27/06, vamsee movva <vamseejump@gmail.com> wrote:
>
> 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
==================================================================
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not able to execute query on spatial database.
@ 2006-10-29 17:10 Michael Fuhr <mike@fuhr.org>
parent: vamsee movva <vamseejump@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2006-10-29 17:10 UTC (permalink / raw)
To: vamsee movva <vamseejump@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-10-29 17:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-27 14:34 not able to execute query on spatial database. vamsee movva <vamseejump@gmail.com>
2006-10-27 15:25 ` Aaron Bono <postgresql@aranya.com>
2006-10-29 17:10 ` Michael Fuhr <mike@fuhr.org>
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