2009/12/6 Hugo Mills <hugo@???>:
> 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
Ok, another small step forward. I have tried your suggestion, mapnik
then refused to print anything. So I decided it was time to actually
run some of these commands in psql so that I could see what is
happening. The problem is, I believe the select statement that I am
working on becomes part of a larger complex select built by mapnik.
Anyhow, I have almost achieved what I want with the following:-
SELECT DISTINCT ON (name)
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
WHERE waterway IS NULL
AND leisure IS NULL
AND landuse IS NULL
AND (name IS NOT NULL OR ref IS NOT NULL)
ORDER BY name,road_len DESC
The problem I have now is that it is searching the entire database for
unique names, not just the area I am interested in. That is a PostGIS
issue I will save for another day.
--
Philip Stubbs
