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.

Improve this question
2
  • 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
    Commented Apr 26, 2018 at 1:46
  • @vince, copy paste error. It's actually 9.6. I'll update the post
    – matt wilkie
    Commented Apr 26, 2018 at 22:31

1 Answer 1

Reset to default
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.

Improve this answer
11
  • 1
    that template isn't listed
    – matt wilkie
    Commented Apr 25, 2018 at 23:17
  • 1
    i.imgur.com/h5PbgSH.png (it looks like 'Tablespace' is the active field, but it isn't)
    – matt wilkie
    Commented Apr 25, 2018 at 23:19
  • 1
    hmmm. In 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
    Commented Apr 25, 2018 at 23:24
  • 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
    Commented Apr 26, 2018 at 22:37
  • 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
    Commented May 3, 2018 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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