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

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Cc: Duffer Do <dufferdo25(at)yahoo(dot)com>
Subject: Re: select count of all overlapping geometries and return 0 if none.
Date: 2009-03-13 19:43:24
Message-ID: 200903132143.24730.peter_e@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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)

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Greenhorn 2009-03-15 23:35:02 Oracle to PostgreSQL
Previous Message Mina R Waheeb 2009-03-13 18:38:06 Re: Permanent alias for postgresql table