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

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hello all,<br />I have 2
tableslocations and user_tracker:<br /><br />locations has 2 columns<br />location_name<br />location_geometry<br /><br
/>user_trackerhas 3 columns<br />user_name<br />user_geometry<br />user_timestamp<br /><br /><br />locations table is
coordinatesand names of areas of interest.<br />user_tracker basically is an archive of a user's movements as he pans
hismap.<br /><br />I have a need to assign a ranking of locations based on how many times users have intersected this
location.<br/><br />The problem I am having is that my query only returns locations that have been intersected by a
user.<br/>I need it to return ALL locations and a zero if this location has not been intersected.<br /><br />As an
example:<br/><br />LOCATIONS<br />1: Talahassee, FL | talahassee's bounding box<br />2: Manhattan, NY  | Manhattan's
boundingbox<br />3: Frankfurt, GE    | Frankfurt's bounding box<br /><br /><br />USER_TRACKER<br />john doe     |
geometrythat overlaps Frankfurt  | today<br />john doe     | geometry that overlaps Frankfurt  | today<br />john doe 
  | geometry that overlaps Frankfurt  | today<br />john doe     | geometry that overlaps Frankfurt  | yesterday<br
/>johndoe     | geometry that overlaps Frankfurt  | Monday<br />john doe     | geometry that overlaps Frankfurt  |
Sunday<br/>Mary Jane  | geometry that overlaps Manhattan  | today<br />Rob Roy    | geometry that overlaps Manhattan  |
today<br/>Rob Roy    | geometry that overlaps Manhattan  | today<br /><br /><br />I want to return the following:<br
/>locations   |  number_visits<br />Frankfurt    |  6<br />Manhattan  |  3<br />Talahassee |  0<br /><br />My query
onlyreturns:<br />Frankfurt    |  6<br />Manhattan  | 3<br /><br />Now I have really simplified this example for
readability,my actual tables are more complex.<br /><br />How can I accomplish this?<br /><br />My query:<br />SELECT
count(user_name)as number_visits, location_name from locations, user_tracker WHERE user_geometry &&
location_geometry<br/><br />Thanks in advance</td></tr></table><br /> 

On Thursday 12 March 2009 19:28:19 Duffer Do wrote:
> I want to return the following:
> locations    |  number_visits
> Frankfurt    |  6
> Manhattan  |  3
> Talahassee |  0
>
> My query only returns:
> Frankfurt    |  6
> Manhattan  | 3

> My query:
> SELECT count(user_name) as number_visits, location_name from locations,
> user_tracker WHERE user_geometry && location_geometry

I think something like this:

SELECT count(user_name) as number_visits, location_name FROM locations LEFT
JOIN user_tracker ON (user_geometry && location_geometry)