Re: [Hampshire] SQL SELECT query.

2009/12/6 Hugo Mills <hugo@???>:
> On Sun, Dec 06, 2009 at 07:32:25PM +0000, Philip Stubbs wrote:
>> Hi,
>>
>> I have been playing with mapnik to create some maps. It has been fun,
>> but I am a bit lacking in Postgres SQL. Can anybody help me to adjust
>> the following SELECT statement so that it will return a result set
>> that consists of only one of each distinct 'name'?
>
>   In brief, no. What you're asking for, on the face of it, doesn't
> really make sense in SQL.
>
>>  SELECT way,highway,aeroway,name,ref,char_length(ref) AS length,
>> ST_Length(way) AS road_len,
>>        CASE WHEN bridge IN ('yes','true','1') THEN 'yes'::text ELSE
>> bridge END AS bridge
>
>   You've selected a whole bunch of fields here. If there's, say, two
> records with the same name but different "way" values, what do you
> want it to do? Pick an arbitrary record where the name matches?
>
>>        FROM planet_osm_line
>>        WHERE waterway IS NULL
>>          AND leisure IS NULL
>>          AND landuse IS NULL
>>          AND (name IS NOT NULL OR ref IS NOT NULL)
>>          ORDER BY road_len DESC
>
>   If you simply want a list of unique names, then:
>
> SELECT name FROM planet_osm_line WHERE [...] GROUP BY NAME;
>
>   It might help us if you explain what you want to do with the data
> from this query.

I guess that is part of the problem. I don't really understand what
happens. All I know is the results from this query are used to add
labels to my map. All the magic happens inside mapnik. The contents of
the name or ref field is used for the value of the label. The way
field contains the data for the road, and highway contains the type of
road. What I am trying to do is make sure that as many roads as
possible are labelled. However, many small closes etc are made up of
multiple segments, often short stubs used as a turning place. If I use
the SELECT statement as is, I get a label for every way, so the map is
cluttered with multiple labels with the same value. What I would like
is to modify this statement so that the returned results contains just
the one occurrence of each name representing the longest way.

I am only drawing small maps, so the chance of having two different
roads with the same name in the one map is very small indeed.

--
Philip Stubbs