Using OpenStreetMap Data in PostGIS database from ArcMap?

Question

I've been trying for over a week now, and I'm not sure what I'm doing wrong and I haven't found the answer yet.

Target. I am trying to display and symbolize the OSM dataset over Jordan in ArcGIS for Desktop 10.3.

Software. Currently running PostgreSQL 9.3.5 and PostGIS 2.1 on a Windows 2008 R2 Server. ArcMap 10.3 is also installed on a Windows 2008 R2 Server. Along with ArcGIS for Server 10.3.

Steps after installation.

1. Use ArcGIS tool "Create Enterprise Geodatabase" and Name it jordan. (Works)
2. In PostgreSQL use CREATE EXTENSION postgis; (Works)
3. Use osm2pgsql using - osm2pgsql -s -c -H servername -d jordan -U sde -W -M -S .\default.style .\jordan-latest.osm.pbf to add the data to the PostGIS database (Works)
4. Connect to Database in ArcGIS (Works)
5. Register *_line database record with ArcGIS and Add OID (Works)
6. Add the feature in ArcMap (Works - Feature Geometry shows up)
7. Open Attribute table (Failed with error Could not load data from the data source. If you can correct the problem, press the refresh button to reload data. Possible problems can include bad network, invalid field, etc. Invalid SQL syntax [ERROR: syntax error at or near":" Line 1: select osm_id, access, addr:housename, addr:housenumber,... ::SQL state: 42602][jordan.sde.planet_osm_line])

I can not symbolize the data using the OpenStreetMap toolbox as well because of the same error.

I have tried changing the field names from ":" to "_" and the error goes away, but the next error states [ERROR: syntax error at or near","] and there is no comma in the field names so it must be inheriting it from somewhere else, and I have no clue where.

My ultimate goal of this project is to bring in the planet file from osm and symbolize it correctly.

What am I doing wrong, what other things can I try?

Answer 1

A couple of points:

1) - Although you should technically be able to connect to a PostGIS database filled with OSM data processed by osm2pgsql using ArcGIS for Server, Direct Connect or using Query Layers, I do strongly recommend you to have a look at ESRI's "ArcGIS Editor for OpenStreetMap".

This free extension allows a much smoother workflow for you to both build your PostGIS database (there is a special version of the tool dedicated to PostGIS as PostGIS uses the word "natural" as a reserved keyword, clashing with OpenStreetMap's usage of the key "natural").

The main reason for the smoother workflow is that the tool will do the necessary clean up of OSM data, such as correcting geometries with geometric errors (incorrect ring direction for usage in the ArcGIS framework).

It will save you a lot of headaches using OpenStreetMap data in ArcGIS...

You can find the 10.3 version here:

ArcGIS Editor for OSM, 10.3.x Desktop

2) - You don't state it literally, but you suggest you may actually have tried the above mentioned toolbox. If so, you should be aware that the ArcGIS Editor for OpenStreetMap toolbox converts any ":" character to the following string in a database field name:

"_58_"

Also other non allowed special characters are converted, e.g., a "-" is replaced by "_45_". I think the editor uses the ASCII index number for this.

In addition, each field name, except the default ones already added by the toolbox (highway, railway etc.), is prepended with the string:

"osm_"

This means that you need to rewrite your SQL queries, as ArcGIS does not properly support the use of aliases in SQL AFAIK (and a test with an OSM field showed) that may contain these special characters. The ArcGIS Help also says about field names:

"You also cannot use spaces or special characters in the field name"

As an example, your SQL statement excerpt:

select osm_id, access, addr:housename, addr:housenumber,...

should in fact read:

select osm_id, access, osm_addr_58_housename, osm_addr_58_housenumber,...

to work with a PostGIS database created by the ArcGIS Editor for OpenStreetMap...

I don't think the comma "," error you mention is "inherited", but merely some consequence of the problem parsing the SQL statement, and it ending somewhere in the middle of the statement and ending up in the error message due to the error handling of the code...

Marco

Answer 2

The issue (after removing all the ":" from column names) is the column named "natural", which apparently causes problems because it is a reserved word. I changed the column name to "nat" and now can view the data in ArcGIS without errors. The ArcGIS OSM extension recommended above seems to have a way around this (see https://github.com/Esri/arcgis-osm-editor/wiki/Tools#PostgreSQL_Note), but changing the column name manually fixed it for me if that tool isn't do what you need.