Thanks, that's helpful. Thinking of wrapping the whole current query up as a
subquery, and applying the date constraints to the result.
* Mikel Maron * +14152835207 @mikel s:mikelmaron
>________________________________
> From: sk53.osm <[email protected]>
>To: Mikel Maron <[email protected]>
>Cc: "[email protected]" <[email protected]>
>Sent: Monday, June 10, 2013 12:42 PM
>Subject: Re: [OHM] Modifying the default renderer
>
>
>
>Hi Mikel et al.,
>
>Has anyone looked at performance issues with PostgreSQL.
>
>When I tried something similar a couple of years ago with Peter Koerner's
>Berlin History data I had problems because my constraints on start & end date
>caused the optimiser to not pick any of the GIST indexes on the geometry
>columns (which would have been much more performant).
>
>I can't remember how complex the queries were & the relevant database is now
>in an archive, plus relevant data volumes were high (overview here).
>
>A quick scan in a project folder suggests the way I did it was using views
>like this:
>
>
>CREATE OR REPLACE VIEW planet_osm_line AS
> SELECT w.osm_id, w.version, w.access, w."addr:flats", w."addr:housenumber"
> , w."addr:interpolation", w.admin_level, w.aerialway
> , w.aeroway, w.amenity, w.area, w.barrier, w.bicycle, w.bridge, w.boundary
> , w.building, w.capital, w.construction, w.cutting, w.disused, w.ele
> , w.embankment, w.foot, w.highway, w.historic, w.horse, w.junction
> , w.landuse, w.layer, w.learning, w.leisure, w.lock, w.man_made
> , w.military, w.motorcar, w.name, w."natural", w.oneway, w.operator
> , w.poi, w.power, w.power_source, w.place, w.railway, w.ref, w.religion
> , w.residence, w.route, w.service, w.shop, w.sport, w.tourism
> , w.tracktype, w.tunnel, w.waterway, w.width, w.wood, w.z_order, w.way,
>0.0::float as way_area, w.date_from, w.date_to
> FROM planet_osm_way_merge w, notional_date
> WHERE notional_date.not_date >= w.date_from AND notional_date.not_date <=
>w.date_to
>
>The notional_date table just was a 1 row, 1 column table to enable me to avoid
>having to change the mapnik stylesheet at all, and was changed in the python
>script for each render run. Anyway the queries ran several hundred times
>slower than expected because the optimser picked the date_from column index
>rather than the geometry one which would have been much more selective.
>
>
>I have no idea if this is anything close to what you guys are working on, but
>thought it might be worth sharing in case you hit similar problems.
>
>
>I also have a vague notion that it's better to have two predicates on the
>dates rather than using BETWEEN because the optimiser doesn't seem to
>automatically re-write the BETWEEN predicate during parsing.
>
>
>Cheers,
>
>Jerry
>
>
>
>
>On Mon, Jun 10, 2013 at 7:09 PM, Mikel Maron <[email protected]> wrote:
>
>Jerry
>>
>>
>>Today, we're looking at switching to the Carto stylesheet as a starting
>>point, and adding in variables to filter on start_date and end_date. We'd
>>manage the carto in github, so totally possible to experiment with styles.
>>Will let everyone know how the sprint turns out today.
>>
>>
>>-Mikel
>>
>>* Mikel Maron * +14152835207 @mikel s:mikelmaron
>>
>>
>>>________________________________
>>> From: sk53.osm <[email protected]>
>>>To: [email protected]
>>>Sent: Monday, June 10, 2013 8:50 AM
>>>Subject: [OHM] Modifying the default renderer
>>>
>>>
>>>
>>>I've been looking at the Seattle data and feel a bit dissatisfied with how
>>>the data looks. I realised that my issue was the early grid being
>>>represented as tracks.
>>>
>>>I believe that we should try and use a functional classification of highways
>>>for all historical periods, and keep the actual physical condition (whether
>>>a residential street is a muddy trackway, a narrow alley filled with ordure
>>>or a stone paved road with raised sidewalks) in distinct tags.
>>>
>>>For most historical periods values of highway=motorway, trunk, motorroad
>>>will be irrelevant, but I think there will always be at least two classes of
>>>highways loosely corresponding to longer distance roads, and local routes:
>>>for now I would suggest continuing to use primary/secondary. I'm not sure
>>>that tertiary is relevant in the pre-car age. We should also consider
>>>whether specific tags are needed for pack-horse trails & mule paths: the
>>>remnants of both are common across Western Europe, usually now tracks or
>>>bridleways.
>>>
>>>Whatever tags are used I think the appropriate cartography for main highways
>>>needs to be much more muted that what I've learnt is the "Telly Tubby
>>>style". One potential point of inspiration is the cartography of older
>>>editions of the Ordnance Survey's Roman Britain map (extract here). WIth a
>>>single cartographic style covering multiple periods I think we should aim to
>>>be fairly conservative. Furthermore there are many style rules which can be
>>>removed.
>>>
>>>Obviously any changes should wait until the main OSM site goves over to the
>>>CartoCSS style sheet. However, I think it's worth kicking off a discussion
>>>about both tagging & cartography at this point, before too much data is
>>>entered. So far it's only highways which have caught my attention: there may
>>>be some other feature classes which need a more period-neutral cartography.
>>>
>>>Regards,
>>>
>>>Jerry
>>>
>>>PS. I'd love to hear a resume of the BoF session in SF.
>>>
>>>_______________________________________________
>>>Historic mailing list
>>>[email protected]
>>>http://lists.openstreetmap.org/listinfo/historic
>>>
>>>
>>>
>
>
>
_______________________________________________
Historic mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/historic
