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
