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
