At 06:20 AM 3/13/2009, pgsql-sql-ow...@postgresql.org wrote:
Message-ID: <457532.70947...@web45913.mail.sp1.yahoo.com>
Date: Thu, 12 Mar 2009 10:28:19 -0700 (PDT)
From: Duffer Do <dufferd...@yahoo.com>
Subject: select count of all overlapping geometries and return 0 if
none.
To: pgsql-sql@postgresql.org
X-Archive-Number: 200903/24
X-Sequence-Number: 32267
Hello all,
I have 2 tables locations and user_tracker:
locations has 2 columns
location_name
location_geometry
user_tracker has 3 columns
user_name
user_geometry
user_timestamp
locations table is coordinates and names of areas of interest.
user_tracker basically is an archive of a user's movements as he pans
his map.
I have a need to assign a ranking of locations based on how many times
users have intersected this location.
The problem I am having is that my query only returns locations that
have been intersected by a user.
I need it to return ALL locations and a zero if this location has not
been intersected.
As an example:
LOCATIONS
1: Talahassee, FL | talahassee's bounding box
2: Manhattan, NY | Manhattan's bounding box
3: Frankfurt, GE | Frankfurt's bounding box
USER_TRACKER
john doe | geometry that overlaps Frankfurt | today
john doe | geometry that overlaps Frankfurt | today
john doe | geometry that overlaps Frankfurt | today
john doe | geometry that overlaps Frankfurt | yesterday
john doe | geometry that overlaps Frankfurt | Monday
john doe | geometry that overlaps Frankfurt | Sunday
Mary Jane | geometry that overlaps Manhattan | today
Rob Roy | geometry that overlaps Manhattan | today
Rob Roy | geometry that overlaps Manhattan | today
I want to return the following:
locations | number_visits
Frankfurt | 6
Manhattan | 3
Talahassee | 0
My query only returns:
Frankfurt | 6
Manhattan | 3
Now I have really simplified this example for readability, my actual
tables are more complex.
How can I accomplish this?
My query:
SELECT count(user_name) as number_visits, location_name from
locations, user_tracker WHERE user_geometry && location_geometry
Thanks in advance
Hi,
I am stuck at an airport right now, and had the time to hack out your
solution. I hope it helps. In the future, it would be helpful to the
list (and more likely to yield responses) if you include the
create/insert statements such as what I've put together below. It makes
it much easier for people to quickly get you an answer - in this case I
had a spare 20 minutes so I did it for you.
Also, I put a gist index on your locations table but that assumes your
areas are flat, which isn't right for spherical coordinates on the
earth. PostGis should be able to get you closer, if that kind of
accuracy matters.
Anyway, here are the table setups and what I believe is the solution
query:
-- START SQL SCRIPT --
drop table if exists locations;
create table locations
(id serial,
name varchar(255),
area circle);
insert into locations (name, area)
values
('Talahassee, FL','((0,0),1)'),
('Manhattan, NY','((2,0),1)'),
('Frankfurt, GE','((4,0),1)');
CREATE INDEX idx_locations_area ON locations USING gist ("area");
drop table if exists user_tracker;
create table user_tracker
(id serial primary key,
name varchar(255),
location point,
time varchar(255));
insert into user_tracker (name,location,time)
values
('john doe', '(4,0)', 'today'),
('john doe', '(4,0)', 'today'),
('john doe', '(4,0)', 'today'),
('john doe', '(4,0)', 'yesterday'),
('john doe', '(4,0)', 'Monday'),
('john doe', '(4,0)', 'Sunday'),
('Mary Jane', '(2,0)', 'today'),
('Rob Roy', '(2,0)', 'today'),
('Rob Roy', '(2,0)', 'today');
-- SOLUTION QUERY HERE --
select count(user_tracker.id), locations.name from user_tracker
right outer join locations on user_tracker.location <@ locations.area
group by locations.name;
-- END SQL SCRIPT --
OUTPUT:
3;"Manhattan, NY"
0;"Talahassee, FL"
6;"Frankfurt, GE"
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
