Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 13:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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