Hi, does nobody has a hint or better idea to do it?
cheers Ivo On 28.01.2009, at 15:16, Ivo Brodien wrote: > Hello, > > for a project I want to render SVG maps on-the-fly using Geoserver > and a PostGIS data store. I know there is Cairo but don't need it to > be that perfect. The files should be as small as possible while > still nice enough to see recognize something. > > As already mentioned on this list I managed to import the > "planet_osm" file into my PostgreSQL DB. > > The machine is: > > CPU: 2.66 GHz Intel Dual Core > RAM: 3.2 GB RAM > OS: Ubuntu 8.10 > DB: PostgreSQL 8.3 > > In order to get not all the features (rows) for each query for the > different zoom levels I thought of adding a maxlevel & minlevel > column to each row in each table and creating an index on these > columns. > > So I could do something like: "SELECT * FROM osm_roads WHERE way in > BBOX and maxlevel =< 18 and minlevel >= 17" > > The problem is, that I am not an expert for databases especially big > ones like the "osm" DB, so I would like to ask for some help in > tuning the databese: > > The first thing that causes headaches is adding the new columns: > > ALTER TABLE planet2_line ADD COLUMN maxlevel integer NOT NULL > DEFAULT 0; > ALTER TABLE planet2_line ADD COLUMN minlevel integer NOT NULL > DEFAULT 100; > > This statement however never returns - at least not in reasonable > time. > > If I do: > > ALTER TABLE planet2_line ADD COLUMN maxlevel integer; > ALTER TABLE planet2_line ADD COLUMN minlevel integer; > > it works. So I guess writing the default value takes so much time. > > I want to use a default value, because afterwards I want to add the > correct values by statements like these, so there have to be the > initial values for comparing: > > --line > UPDATE planet2_line SET maxlevel=max(17,planet2_line.maxlevel), > minlevel=min(15,planet2_line.minlevel) WHERE highway = > 'secondary_link' and not (tunnel='yes' or tunnel='true'); > UPDATE planet2_line SET maxlevel=max(18,planet2_line.maxlevel), > minlevel=min(17,planet2_line.minlevel) WHERE highway = > 'secondary_link' and not (tunnel='yes' or tunnel='true'); > > --point > UPDATE planet2_point SET maxlevel=max(8,planet2_point.maxlevel), > minlevel=min(6,planet2_point.minlevel) WHERE place = 'city'; > UPDATE planet2_point SET maxlevel=max(10,planet2_point.maxlevel), > minlevel=min(8,planet2_point.minlevel) WHERE place = 'city'; > > -- polygon > UPDATE planet2_polygon SET > maxlevel=max(18,planet2_polygon.maxlevel), > minlevel=min(12,planet2_polygon.minlevel) WHERE aeroway = 'apron'; > UPDATE planet2_polygon SET > maxlevel=max(18,planet2_polygon.maxlevel), > minlevel=min(13,planet2_polygon.minlevel) WHERE 'natural' = 'beach'; > > Running these UPDATE statement needs a lot of time as well, but I > don't care that much, because I only have to do it once. > > The maxlevel and minlevel values come from the osm.xml file which is > included in the Mapnik repository. > > > So the big question now is: > > How do I tune my DB Server for more performance? The server does not > need to do more work than running the DB and there will be only one > client connected. > > What could be good values for the Memory in postgresql.conf? > > At the moment I changed these values: > > # - Memory - > > shared_buffers = 500MB # min 128kB or > max_connections*16kB > temp_buffers = 8MB # min 800kB > work_mem = 10MB # min 64kB > > I probably have to use different tunings for doing the level setting > task and the rendering task? But what would they look like? > > Thanks for any little help or advice! > > Ivo > > _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
