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
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 |