[Tile-serving] [openstreetmap/osm2pgsql] Table output issue (#1274)

[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 a 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).


-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1274#issuecomment-696892202
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20200922/e621888c/attachment.htm>