select count of all overlapping geometries and return 0 if none.

From: Duffer Do <dufferdo25(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: select count of all overlapping geometries and return 0 if none.
Date: 2009-03-12 17:28:19
Message-ID: 457532.70947.qm@web45913.mail.sp1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Allan Kamau 2009-03-12 18:10:21 Re: Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
Previous Message Thomas Kellerer 2009-03-12 14:39:35 Re: Permanent alias for postgresql table