Re: [Hampshire] SQL SELECT query.

On Sun, Dec 06, 2009 at 09:53:56PM +0000, Philip Stubbs wrote:
> 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.

What SQL does is return the requested values from all of the rows
in the database that match the WHERE clause (in the order given by the
ORDER BY clause). So, if there are several records in the database
with the same name (i.e. multiple "way" values), you'll get several
rows returned... as you've already discovered.

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

SQL is really, really bad at this kind of job, I'm afraid. If you
want precisely the longest way, it's going to have to look something
like this, I think:

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
FROM planet_osm_line,
(SELECT MAX(ST_length(way)) AS waylen, name, ref GROUP BY name, ref) AS longway
WHERE waterway IS NULL
 AND leisure IS NULL
 AND landuse IS NULL
 AND (name IS NOT NULL OR ref IS NOT NULL)
AND planet_osm_line.road_len = longway.waylen
AND planet_osm_line.name = longway.name
AND planet_osm_line.ref = longway.ref
ORDER BY road_len DESC

I'm not sure if the "AS longway" bit is going to work -- I can't
remember the spec, or the variants of it for different SQL engines.
The sub-select may also not function correctly on some SQL systems.

Hugo.

-- 
=== Hugo Mills: hugo@... carfax.org.uk | darksatanic.net | lug.org.uk ===
  PGP key: 515C238D from wwwkeys.eu.pgp.net or http://www.carfax.org.uk
    --- Klytus! Are your men on the right pills? Maybe you should ---    
                         execute their trainer!