[osmosis-dev] Changes to Osmosis Pgsql Schema

[Stefan Große Pawig]

  Hi Brett,

brett at bretth.com (Brett Henderson) writes:
> The pgsql changes are complete and are available in SVN.  For bbox style
> queries, with the linestring optional script installed the performance
> improvements are quite drastic running approx 5-10 times faster on large
> datasets.
> http://svn.openstreetmap.org/applications/utils/osmosis/trunk
>
> The new scripts are available in the package/script directory.  The
> pgsql_simple.txt describes what each script is for.  You will typically want
> pgsql_simple_schema_0.6.sql and possibly
> pgsql_simple_schema_0.6_linestring.sql.  The database requires that postgis
> and hstore extensions are installed.  Docs could probably be improved here,
> I haven't had much time to spend on it lately.

I am also in the process of setting up a database with the new simple
schema for the whole planet.  To fill the database, I used the two-step
process: use osmosis with the --write-pgsql-dump task to create the dump
files, followed by (an adapted version of) pgsql_simple_load_0.6.sql to
load those files into the database.

I found three issues with the supplied pgsql_simple_load_0.6.sql file:

* Most importantly, since the indexes are dropped before the import and
  recreated afterwards, the CLUSTER setup from from the
  pgsql_simple_schema_0.6.sql and pgsql_simple_schema_0.6_linestring.sql
  is lost, and the
    CLUSTER;
  command in pgsql_simple_load_0.6.sql does nothing.  This  should be
  replaced with
    CLUSTER nodes USING idx_nodes_geom;
    CLUSTER ways USING idx_ways_linestring;
  instead.

* Since there are no more xxx_tags tables in the new scheme, the
  corresponding \copy lines should be removed.

* In the UPDATE command for the manual linestring creation, the
  terminating semicolon is missing.

  Kind regards,
    Stefan