4

The Esri docs for PostgreSQL data types supported in ArcGIS v10.6 says in part [emphasis added]:

If you create your geodatabase using the Create Enterprise Geodatabase geoprocessing tool, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry or geography type, you must create a database manually using the PostGIS template. Then you can specify your existing database when you run the Create Enterprise Geodatabase geoprocessing tool, and the geodatabase will be created in this PostGIS-enabled database.

How exactly does one "create a db using the postgis template"?

We intend to use the postgres GEOGRAPHY type for GEOMETRY_STORAGE instead of the Esri default ST_GEOMETRY (ref).

I've installed Postgres 9.6 using the Esri supplied installer* and used Stack Builder to install PostGIS extension. I'm able to connect to Postgres with pgAdmin from a remote computer.

* PostgreSQL_DBMS_for_Windows_963_158796

update: fixed installed postgres version typos. Added ArcGIS version.

matt wilkie
  • 28,176
  • 35
  • 147
  • 280
  • 1
    PostgreSQL 9.3 is rather elderly, and best supported only by elderly versions of ArcGIS. The current level of support for PostgreSQL was only achieved with ArcGIS 10.4.1; the difference is so significant that I would not consider ArcGIS to actually support PostgreSQL until 10.4.1, and the maturity of PostgreSQL and PostGIS improved such that I can't recommend releases before 9.4 and 2.0 (respectively). It has always been wise to use the current build over the first PostgreSQL supported by Esri, no matter which version of 9.X.y was chosen, so 9.3.22 should be used over PG 9.3.5. – Vince Apr 26 '18 at 01:46
  • @vince, copy paste error. It's actually 9.6. I'll update the post – matt wilkie Apr 26 '18 at 22:31

1 Answers1

4

First start PGAdmin III and right click on Databases: enter image description here

Then in your 'new database' dialog select your PostGIS template: enter image description here

Be sure to either change your sde_dbtune GEOMETRY_STORAGE to PG_GEOMETRY or use the PG_GEOMETRY keyword when creating feature classes.

Despite being able to connect to PostgreSQL, that only means the port is open, you might have trouble retrieving a feature class/dataset if you haven't installed the PG Client on the workstation side. Read more https://community.esri.com/thread/114415 - the version is not the ArcGIS version it's the PostgreSQL version.

Another trap for beginners is that each connected user should have their own schema to store large selections, if you don't create a schema for each login user they will receive an error (with an unhelpful error message) when they select more than a handful of records (I don't know the exact number).

I hope some of this information brings you less facepalm moments.

Edit

This is what I have in PostGIS template: enter image description here

Perhaps you can make one by creating extensions in an empty database; I'd like to hear from anyone who has done so successfully.

Community
  • 1
Michael Stimson
  • 25,566
  • 2
  • 35
  • 74
  • 1
    that template isn't listed – matt wilkie Apr 25 '18 at 23:17
  • 1
    https://i.imgur.com/h5PbgSH.png (it looks like 'Tablespace' is the active field, but it isn't) – matt wilkie Apr 25 '18 at 23:19
  • If you don't have that template then your install of PostGIS isn't complete, there should be a template_postgis in your database. It looks like you've got two clusters associated with your Postgres, is that right? What happens if you type in template_postgis? It might be in one of the clusters/databases you have an option of. – Michael Stimson Apr 25 '18 at 23:20
  • 1
    hmmm. In https://gis.stackexchange.com/a/39229/108 it sounds like might be as simple as "To create a postgis template, I created a new database called template-postgis and then added these extensions [postgis, postgis_topology]." -- is that so? – matt wilkie Apr 25 '18 at 23:24
  • It might be, every time I've installed PostGIS the template has been there as part of the install process... but I do (vaguely) remember that on occasion the template isn't installed, possibly due to not having the superuser password on install or something like that. Have a go at the method in the linked thread, you can always delete the database if it doesn't work. Let me know how you get on, I may face the same dilemma in the future. – Michael Stimson Apr 25 '18 at 23:28
  • Why not just "create extension postgis" instead of dealing with that template? – Nicklas Avén Apr 26 '18 at 07:39
  • 1
    @NicklasAvén - umm, because the docs say I need to use that template. I've never seen it so don't know what's in it. Is "create db >> create extension postgis within that db" the same as thing as 'template_postgis'? – matt wilkie Apr 26 '18 at 22:37
  • @mattwilkie I've edited in a screen shot of my template. What version of PostGIS have you installed with pSQL 9.6.3? This one is from PostGIS 2.0.6 on PostgreSQL 9.2.2, a little old but it was the version that came with Server 10.2. – Michael Stimson Apr 26 '18 at 23:15
  • 1
    This worked: (1) use pgAdmin to create an empty database and name it 'template_postgis' >> open the DB, select Extensions >> Create >> select postgis and postgis_topology. (2) run Create Enterprise Geodatabase tool in ArcMap/Pro. ... However the resultant db uses st_geometry. Now to figure out the 'sde_dbtune' thing. – matt wilkie May 03 '18 at 21:20
  • In the sde_dbtune you find configuration keywords, your default will be ST_GEOMETRY, in DEFAULTS for parameter_name GEOMETRY_STORAGE, in PGAdmin find the table, open it for editing and change the value in the table next to GEOMETRY_STORAGE to PG_GEOMETRY or, you should find a keyword PG_GEOMETRY which you can use to load data as PG_GEOMETRY... this must be done before loading the data, changing it later won't change the geometries from one storage to another. That's the easy part, the hard part is performance tuning the keywords and obtaining metrics. – Michael Stimson May 03 '18 at 22:33
  • Changing default geometry is now accomplished using arcpy.ImportGeodatabaseConfigurationKeywords(), see https://gis.stackexchange.com/a/222792/108 – matt wilkie Sep 11 '18 at 22:12