Table output issue

[edahillb]

Hello, I am attempting to use the osm2pgsql outputs to make editable feature layers in ESRI Arc Suite. While using the default style file, I got a very odd error, and I discovered that the tables were blank. Once I realized it was something to do with the style file, I started trying to figure out which tags were causing the issue. The tags that I have pinpointed are:

node,way   addr:housename      text  linear
node,way   addr:housenumber    text  linear
node,way   addr:interpolation  text  linear
node,way   generator:source    text  linear
node,way   natural      text         polygon
node,way   tower:type   text         linear

I am wondering if this is a current issue or if ya'll have any idea what the cause would be?

[joto]

It is unclear to me what the problem is. Can you describe exactly

• what you did (including command lines, log output, version information, etc.
• what you expected to happen and
• what happened instead

[edahillb]

Version is osm2pgsql-latest-x64 2020-07-28 09:15 from https://lonvia.dev.openstreetmap.org/osm2pgsql-winbuild/releases/

cmd prompt F:\osm2pgsql-bin\osm2pgsql.exe -S F:\osm2pgsql-bin\OSM_uk.style -C 15000 -c -d uktest -s -U postgres -W -H localhost F:\NATO\Test\georgia-latest.osm.pbf

When I look at the table/feature classes in ArcCatalog with the tags included above the tables only contained column heads. When I remove those tags I get the table I've attached.

Please let me know if you need more info.

[joto]

You wrote something about "a very odd error". What was that error? What's in the OSM_uk.style file?

[edahillb]

When using the feature class to feature class tool in arc to I got an error that you'll see attached.

The OSM_uk.style is the empty style file with every tag in the default except those in my initial post. I initially ran the script with the default file and I kept getting empty tables and the error you see below.

[lonvia]

This looks rather like an issue with the Arc Suite than with osm2pgsql.

Just a guess: is it possible that the Arc Suite cannot handle column names that contain a colon?

[mboeringa]

Just a guess: is it possible that the Arc Suite cannot handle column names that contain a colon?

@edahillb and @lonvia :

Sarah is right that there are restrictions when using database tables in ArcGIS, some of them purely related to ArcGIS itself, others PostgreSQL. Mind the following things:

• natural is a reserved keyword in PostgreSQL. To use it in SQL statements or database views, always enclose it by double quotes: e.g. "natural" IS NOT NULL

• Field names are best kept to using alphanumeric characters and underscore only, so "a-z", "0-9" and "_"

• Only start a field name with a character, not a number

• ArcGIS is still restricted to 30 character field names only for enterprise databases like PostgreSQL / Oracle / MS SQL Server (officially it is 31, but I would stick to 30 to be on the safe side). This poses some challenges with some keys in OSM, because simply replacing the semicolon in e.g. generator:output:compressed_air with for example an underscore, still leaves you with a field name of 31 characters. And there are OpenStreetMap keys that use even more namespaces and characters than this one.

• For File Geodatabases the limit for table and field names is 64 characters.

• Note that although I said field names are restricted to alphanumeric characters only, and this is certainly recommended, ArcGIS does seem to support some other characters as well, which are depended on database restrictions. However, if they require more than one byte to encode them, the limit to the maximum field name length shrinks correspondingly, as the 30 character limit is based on one byte characters. So a field name in Chinese characters will have tougher restrictions on the total number of characters.

Also see: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/enterprise-geodatabase-limits.htm

I recommend creating database views to do the translation from invalid key names to ArcGIS friendly ones. This will allow you to load the data in ArcGIS.

Also note you may need to run the https://pro.arcgis.com/en/pro-app/tool-reference/data-management/add-incrementing-id-field.htm geoprocessing tool to create a unique ID field, although you might be able to use the osm_id field created by ArcGIS in e.g. an ArcGIS Query Layer (https://pro.arcgis.com/en/pro-app/help/data/query-layers/what-is-a-query-layer-.htm).

[pnorman]

This looks rather like an issue with the Arc Suite than with osm2pgsql.

Yep, looks like all the issues are on the Arc side. If you can reproduce the issue with osm2pgsql and not Arc suite, we can re-open the issue.

[edahillb]

Thank you all so much for the help!