3

I am trying to find a way to store OSM Extracts (or possibly the planetfile) in a PostgreSQL database, keep it up to date with diff files and then process the data further with ArcGIS (e.g. Geocoding, Mapping).

The best idea I have come up with so far is to import the osm data into the database with common OSM tools like Osmosis or osm2pgsql. My problem is, that those tools create different schemas than SDE.

Has anyone ever tried this before, or a good idea on how to convert between the schemas? I'm open to any comments or suggestions.

Improve this question

1 Answer 1

Reset to default
1

I'm assuming that Osmosis and osm2pgsql create tables in the database with the PostGIS spatial columns. So, you should be able to either consume those tables as query layers, or register those tables as feature classes. If you register them as feature classes, applying the updates should just update the data in the table. This isn't a problem as long as you don't have any geodatabase functionality on the table (ie, topology, geometric network, relationship class, domain...). If you do, I would drop them geodatabase functionality before applying the update, or find a way to apply the updates through ArcObjects.

Improve this answer
5
  • Thanks for your hints. I've tried to register my OSM tables, but I am stuck with building up the connection via SDE command line tools (from ArcGIS 10.1 SP1 32bit to PostgreSQL 9.1 64bit on Windows). I get the follwing error message "Cannot connect to database because the database client software failed to load. Be sure the database client software is installed and configured correctly (-324)".I placed the correct client libraries in my ArcGIS directory and I can connect via ArcCatalog. The PostgreSQL path is set in the PATH dir. Do I have to set any other path dir under PATH?
    – Nicolas Caspari
    May 16, 2013 at 14:20
  • 1
    If you are using the SDE command line tools, I think you will have to put the client dlls in the %SDEHome%\bin directory as well. I'm not sure if adding those dlls to the path will be enough. Can you access the data through a query layer? Why do you want to register the OSM table?
    – travis
    May 16, 2013 at 18:01
  • Now it worked! I had placed the 32 bit client libraries in the %SDEHome%\bin, which did not work. Now it works with the 64bit dlls. I thought registering the OSM tables is what you suggested - but now I realized you probably meant the query layers. Yes, I can add the osm data as query layers, which is already a great leap forward! But I can't set the osm_id column as primary key, probably because it is bigInt and not supported in ArcGIS Desktop.
    – Nicolas Caspari
    May 17, 2013 at 13:03
  • You are right, ArcGIS can't use a bigint column as an objectid. You can try casting the column to an 32bit integer in your query layer sql statement, but that will be a problem if you have values that are larger than the 32bit int max value.
    – travis
    May 17, 2013 at 21:00
  • 1
    The workaround I used now is that I created a new column of type serial, which should update itself if new tuples are added. Now I can use the new ID as primary key in the query layer.
    – Nicolas Caspari
    May 28, 2013 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.