In my previous question, I asked for help concerning why edited features were missing after applying a daily changefile to my OSM postgis dataset. It was determined that I used the flat nodes option upon initial planet.pbf import to postgres (--flat-nodes D:\nodes.cache), and this option was also required in the osm2pgsql --append command.

I was able to successfully append a daily changefile to my database using the flat nodes option in the append command. However, after viewing the data through a localhost Geoserver service, all of the database geometry is corrupted. Specifically, what were once smooth curves in the osm_line layer are now jagged and blocky. Polygons are incorrectly shaped as well. Rectangular building footprints are now often triangular. It looks like some sort of generalization process is happening to the nodes.

Graphical examples:

Lines -
Red is the original linework (before the append command). Thin purple is what the osm_line layer looks like after the append command.

Polygons -
Blue is the original linework (before append). Thin blue is what osm_polygon looks like after the append command.

Here are my commands used:

1) Initial planet import
osm2pgsql -c -d planet -U postgres -W -H localhost -S c:\osm\planet.pbf -E 4326 -C 8000 -s --flat-nodes D:\osm_updates\nodes.cache --number-processes 6

2) Osmosis pull changefile based on local db state id
osmosis --read-replication-interval workingDirectory=d:\osm_updates --simplify-change --write-xml-change d:\osm_updates\changes.osc.gz

3) Osm2pgql append changefile to local postgres db
osm2pgsql --append d:\osm_updates\changes.osc.gz -d planet -U postgres -S -E 4326 -C 8000 --slim --flat-nodes d:\osm_updates\nodes.cache --number-processes 6

asked 06 Oct, 18:54

mykol404's gravatar image

accept rate: 0%

This looks interesting. Can you tell us which version of osm2pgsql and which versions of PostgreSQL/PostGIS you are using? I am not sure if it is going to be relevant but someone who wants to debug this would likely want to use the same versions. Also, are you sure that between the original import and the update, you have not updated osm2pgsql, postgres, postgis, or the GEOS library on your system? Again, this would not necessarily be a problem but it could be.

(06 Oct, 21:38) Frederik Ramm ♦

What if you look at the actual coordinates of an affected feature through a database IDE like DBeaver? Does it indeed show simplication for e.g. for a building now displayed as triangle. In DBeaver you can easily view this information by opening the table and looking at the "way" column. You can even copy the contents to some text editor to view the full coordinate info.

(06 Oct, 21:57) mboeringa

I had a suspicion that there might be a flatnode precision issue with -E 4326 which is not widely used, but I ran an import followed by an update using the same setup as you (albeit for a small area) and I saw no problems. This was using the latest osm2pgsql from github. My theory would also not explain how in your setup all nodes seem to be affected by an update, not only those whose coordinates have actually changed.

(06 Oct, 22:14) Frederik Ramm ♦

I am running the latest version of osm2pgsql (0.96.0).
PostgreSQL version 10.4, and PostGIS 2.4.4 Not sure what the GEOS library is.

(08 Oct, 18:39) mykol404

Have you looked at the actual coordinates of affected features at the database level as I suggested? This is crucial. E.g. if the buildings now having become triangular in display, turn out to be unaffected in the database, than the problem is not with osm2pgsql, osmosis or your append workflow, but can solely be in the Geoserver service.

(08 Oct, 19:00) mboeringa

Thanks for the help everyone. It ended up being duplicate Java environment variables in Windows causing conflict for Geoserver to serve out the data. I guess it was jumping the math around when calling upon Geoserver to feed the data? Who knows.

I tried DBeaver, as suggested by a member. I was able to query a feature but couldn't figure out how to view it or export the coords in this utility.

I then came across a release note for PostgreSQL's pgAdmin4 database management console. They came out with a new version in September that allows you to directly view geometric features through the PostGIS extension, displaying the queried feature in a map window (overlaid on OSM, too!).

I jumped to a given jagged road or triangular building and they were fine!

alt text

This is when we learned that Geoserver was likely the culprit. Looks like it was two conflicting Java path variables (not sure how that happened, but other people share the machine).

Thanks for everyone's help! Cheers

permanent link

answered 12 Oct, 20:17

mykol404's gravatar image

accept rate: 0%

edited 12 Oct, 20:21

Good you managed to solve your issues.

As to DBeaver: yes, it takes a little bit of time to get used to, however, once you have used it a bit longer, there is much to like about the application. I have also found the developer very responsive and pro-active regarding bug reports and enhancement suggestions. At least it is a much better application than pgAdmin3. I haven't yet looked at pgAdmin4, so can't compare them yet.

As to coordinate read-out in DBeaver, simply switch to Data view and select the way column and a record, then choose "Copy" from the right-click context menu. You can subsequently paste in Notepad, as I did in this screenshot.

alt text

permanent link

answered 13 Oct, 14:40

mboeringa's gravatar image

accept rate: 10%

edited 13 Oct, 14:47

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 06 Oct, 18:54

question was seen: 111 times

last updated: 13 Oct, 14:47

powered by OSQA