Paul Norman | 19 Apr 14:14 2013
Picon

pgsnapshot alternative query optimizations: ways using nodes

In part 1 of a N+1 part series, I'm intending to outline some of the
benchmarking I have done into different queries and indexes with a
pgsnapshot database.

Meta: Where on the wiki should this type of stuff go?

All benchmarking times are with a hot cache, but similar trends were
observed with a cold cache. I tested with two areas: the large area
-87.75,41.80,-87.65,41.85 (322k nodes, 58.1k ways) and the small area
-111.92,33.82,-111.91,33.83 (159 nodes, 30 ways)

My testing machine is a 6 core AMD 1090T with all databases on a 6 x 7200
RPM drive array, 32GB ram.

The traditional way of running a query to get all ways which contain a set
of nodes as their children is to do two joins. This type of query is used in
extracting a bbox from the database.

First you need a set of nodes. This could be generated by this query for a
bbox

CREATE TEMPORARY TABLE bbox_nodes AS 
  SELECT * FROM nodes 	
    WHERE geom &&
ST_SetSRID(ST_MakeBox2D(ST_Point(-87.75,41.80),ST_Point(-87.65,41.85)),4326)
;
	
The exact query doesn't matter much - it could be a polygon and
ST_Intersects(geom, area_geom) and everything below should still apply. I am
assuming that some kind of geometry query is used. A random list of nodes
(Continue reading)

Paul Norman | 18 Apr 09:36 2013
Picon

Wrapper scripts for streaming replication

I'm considering making use of streaming replication to feed change data to a
process, but I'm not quite sure how to script it to handle interruptions
like power outages and such correctly without getting in advance of itself.
Does anyone have any wrapper scripts that would help with this?

I'd rather not reinvent the wheel so I can focus on the logic of
interpreting the osc data and putting it into my database.

Alternately, I could re-implement a streaming replication reader in python,
which is what I intend to be writing my osc to DB code in.
Brett Henderson | 16 Apr 13:50 2013

Re: FW: question

On 15 April 2013 00:28, mahmoud ghareebh <m.soliman_2010-PkbjNfxxIARBDgjK7y7TUQ@public.gmane.org> wrote:
yes an old version , do i need to remove it?

The plugin is failing to load, so yes I'd remove it.

Again, do you really need to compile Osmosis yourself?
 



Hi Mahmoud,

Is there a reason you're not using the pre-compiled version?
http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-latest.zip

The errors you're receiving are unusual.  I see the following message in your logs.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Cannot load JPF-plugin 'org.mapsforge.MapFileWriter' for extensionpoint 'Task'
Do you have some custom Osmosis plugins installed somewhere?

Brett
 
_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Paul Norman | 16 Apr 10:31 2013
Picon

Bad PostgreSQL plans with pgsnapshot

I ran across a PostgreSQL query planner bug today when benchmarking
pgsnapshot. This may affect others so I'm documenting it.

A common task is to extract a bounding box or polygon from the database,
equivalent to a map? call.

The way I was benchmarking was a multi-step process, but there's two that
are relevant for this.

The first is getting all the nodes in the area. The query for this is

CREATE TEMPORARY TABLE bbox_nodes AS 
  SELECT * FROM nodes 
    WHERE geom &&
ST_SetSRID(ST_MakeBox2D(ST_Point(-87.75,41.80),ST_Point(-87.65,41.85)),4326)
;
ANALYZE bbox_nodes;

The next is to get the ways with any of those nodes as members.

I started this with 

SELECT DISTINCT ... FROM ways
  JOIN way_nodes ON (ways.id=way_nodes.way_id)
  JOIN bbox_nodes ON (way_nodes.node_id=bbox_nodes.id);

This was basically the query I intended to benchmark, but I was getting
horrible query plans and run times so I split the query up, arriving at
this: 

SELECT DISTINCT way_nodes.way_id
  FROM way_nodes 
  JOIN bbox_nodes ON (way_nodes.node_id=bbox_nodes.id);

I was still getting bad query plans involving a sequential scan of
way_nodes, a 2.2 billion row table.

After much help from RhodiumToad from #postgresql the cause was tracked down
to a bug in the query planner where a fixed fudge factor was accounting for
almost all of the cost of fetching the random pages. The cost estimate was
about 356 of which 348 was from the fudge factor. This was causing it to
vastly overestimate the cost of the index lookups, forcing it to do a
sequential scan.

This bug has already been fixed in 9.3
(http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/u
tils/adt/selfuncs.c;h=31f38f28b00cbe2b9267205359e3cf7bafa1cb97) but not
backported to earlier versions.

The workaround was to increase cpu_tuple_cost to 0.1. This makes it use the
indexes, resulting in a 50s query for a 350k node 60k way bbox instead of a
query aborted at 10 minutes.

If you are running a query against a small bbox (50k nodes) this bug does
not matter as the incorrect fudge factor is not significant enough.

If you are running it against a very large bbox (~10-100 million nodes?) it
also does not matter, a sequential scan will be faster there.

I do not know if this bug impacts the osmosis --dataset-bounding-box task,
jxapi or pyxapi. 

I do not know if set enable_seqscan=false is an appropriate solution. If you
have an index on bbox_nodes (id) it is not as it will force index usage on
that table when it needs to fetch 100% of the table anyways.
mahmoud ghareebh | 13 Apr 12:43 2013
Picon

FW: question

hi 
i`m trying to install osmosis on windows 7 , i have jdk 6 , and i follow this instructions :
./gradlew assemble
success
Perform a complete build including unit tests:
./gradlew build
failure , and give me errors i`m attach the report , could u help me?
<!-- .ExternalClass .ecxhmmessage P { padding:0px; } .ExternalClass body.ecxhmmessage { font-size:12pt; font-family:Calibri; } -->
Attachment (tests.zip): application/zip, 36 KiB
_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Paul Norman | 7 Apr 19:44 2013
Picon

Re: Problem with Windows 7

I find no mention of a –mapfile-writer task in the usage page. Are you using a plugin?

 

From: Malky [mailto:malky-D0VgoRl4D1jNcuzrQHMqQ9BPR1lH4CV8@public.gmane.org]
Sent: Sunday, April 07, 2013 5:48 AM
To: osmosis-dev-3+rWM/WnaLOn4i5uJCXUsti2O/JbrIOy@public.gmane.org
Subject: [osmosis-dev] Problem with Windows 7

 

When I try to create a map, I get a "Files was unexpected at this time" error!

 

I know it must be a variable error with the path name Program Files as I had something similar getting osmosis to run, but I can' figure out what or where?

 

I tried this osmosis

 

--read-xml file="C:\bulgaria.osm" --mapfile-writer file="C:\varna.map" bbox=42.185,27.063,43.463,28.103

 

before getting the error.

 

Any ideas are more than welcome as it's driving me mad.

 

Malky

_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Malky | 7 Apr 14:47 2013

Problem with Windows 7

When I try to create a map, I get a "Files was unexpected at this time" error!

 

I know it must be a variable error with the path name Program Files as I had something similar getting osmosis to run, but I can' figure out what or where?

 

I tried this osmosis

 

--read-xml file="C:\bulgaria.osm" --mapfile-writer file="C:\varna.map" bbox=42.185,27.063,43.463,28.103

 

before getting the error.

 

Any ideas are more than welcome as it's driving me mad.

 

Malky

_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Brett Henderson | 5 Apr 00:51 2013

Osmosis 0.43 Released

Hi All,

I've just released Osmosis 0.43.
http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-0.43-RELEASE.tgz
http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-0.43-RELEASE.zip

From changes.txt:
  • Update command line help to point to the correct wiki location.
  • Remove idTrackerType arguments to filtering tasks, only the Dynamic option is now available.
  • Fix the --write-apidb task to use 64-bit ids.
  • Upgrade to version 1.4 of the Gradle build tool.
  • Enhance the build scripts to support publishing to Sonatype OSS Repository and Maven Central.
  • Rename all projects to use an "osmosis-" prefix.
  • Included a copy of the PBF OSM Binary project in the source tree, and eliminated the pre-compiled osmbin.jar.
  • Remove pbfmarshall project due to the same functionality being provided by the osm-binary project.
  • Removed the internal Ivy repository due to all dependencies now being available on Maven Central.
  • Rename --read-empty short name --re to --rem to avoid clash with --report-entity.
  • Rename --read-empty-change short name --rec to --remc for consistency with --read-empty.

A significant change in this release is that the artefacts have also been published to Maven Central.  This will make it easier for people to include Osmosis libraries in their own projects.  One relatively minor downside is that I can no longer perform the build from the Jenkins server because I am signing builds with my personal GPG key.  The build has been performed off my own local desktop.  I don't expect this to cause any noticeable changes.
http://search.maven.org/#search|ga|1|g%3A%22org.openstreetmap.osmosis%22

Let me know if you see any issues.

Brett

_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Martin Schafran | 3 Apr 20:52 2013

duplicate key value violates unique constraint

 

hi,

 

i'm trying to import the apidb v06 and get this unique constraint error.

my db is postgresql 9.1 and i use osmosis 0.42.

 

the database is after running rake db:migrate empty except the migration table.

 

ERROR: duplicate key value violates unique constraint "current_nodes_pkey1"

Detail: Key (id)=(21402303) already exists.

 

 

I tried the import from two different xml files.

the first node entry in the file a.osm is 21402303 and is reported as violated constraint.

the first node entry in the file b.osm is 12 and is reported as violated constraint.

it seems as the first node has been already inserted some where else or the database is buggy.

 

current_nodes are populated from nodes with this statement in ApidbWriter:

"INSERT INTO current_nodes SELECT node_id, latitude, longitude, changeset_id, visible, timestamp, tile, version"

+ " FROM nodes WHERE node_id >= ? AND node_id < ?";

 

 

and in nodes there is only one entry where id=21402303,

so duplicates are impossible.

 

any ideas, what is going on?

 

martin

_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Brett Henderson | 31 Mar 14:27 2013

OSM Binary (ie. PBF) Changes

Hi All,

I've just changed how the PBF support is provided in Osmosis.  Up until now there was a pre-compiled jar called osmpbf.jar checked into the Osmosis source tree.  It was compiled from Scott Crosby's github project here:
https://github.com/scrosby/OSM-binary

The problem with this is that it prevents me from publishing Osmosis to Maven Central because people trying to download Osmosis would be unable to use PBF without also getting a copy of that library.

To get around this I've repackaged the OSM-binary project to build as part of the Osmosis source tree.  The resultant jar is called osmosis-osm-binary, and it lives in a package called org.openstreetmap.osmosis.osmbinary.

I've tried to do this in a way that allows me to keep up to date with the upstream project.  I've forked the original repository on Github, and created an Osmosis branch:
https://github.com/brettch/OSM-binary/tree/osmosis

When changes are made to upstream, I should be able to merge master across to my osmosis branch.  The resultant tree is then checked directly into the Osmosis source tree.  On my local machine I actually have both git repositories acting on the same source files so it isn't too painful.  I have the Osmosis source checked out normally (ie. a .git directory at the root), and the osmosis-osm-binary project is also a clone of my forked OSM-binary project (ie. I have a .git directory in there as well).  So far it seems to work well enough.

If the original OSM-binary project ever gets published to Maven Central directly then I can stop these shenanigans and depend on it directly.

If anybody has any questions, issues, suggestions, etc let me know.

Brett

PS. I'm getting close to publishing to Maven Central now.  There are no major blockers left that I'm aware of.
_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev
Brett Henderson | 30 Mar 12:41 2013

Publishing Osmosis to Maven Central

Hi All,

This may be of interest to some of you.  I've just begun the process of publishing Osmosis artefacts to Maven Central (ie. http://repo1.maven.org/maven2).

My current snapshot build is available here:
https://oss.sonatype.org/content/repositories/snapshots/org/openstreetmap/osmosis/

For those who are not familiar with Maven Central practices, it is not possible to publish directly to Maven Central itself.  The simplest way is to publish via the OSS Sonatype repository which then gets sync'd with Maven Central.

A few changes have been made to the Osmosis build to allow this to work (I'm still in the process of pushing these changes).  The most noticeable change is that most projects have been renamed to have an "osmosis-" prefix.  If you have an existing Eclipse workspace, you'll need to re-run "gradle eclipse" and re-import your projects.

The main blocker to publishing release artefacts is that I have two dependencies on libraries not available in Maven Central.  These are the scrosby PBF lib, and the BZip2 library which I built manually but which is based on Apache source code.  Both should be fixable by building and publishing them along with the rest of Osmosis, but I need to find time to do so.

Brett

_______________________________________________
osmosis-dev mailing list
osmosis-dev@...
http://lists.openstreetmap.org/listinfo/osmosis-dev

Gmane