Re: select count of all overlapping geometries and return 0 if none. - Mailing list pgsql-sql

At 06:20 AM 3/13/2009, pgsql-sql-owner@postgresql.org wrote:
>Message-ID: <457532.70947.qm@web45913.mail.sp1.yahoo.com>
>Date: Thu, 12 Mar 2009 10:28:19 -0700 (PDT)
>From: Duffer Do <dufferdo25@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