PostGIS and ArcSDE mixed environment

Question

I am trying to setup up a enterprise geodatabase with ArcGIS 10.4.1 and PostgreSQL 9.4 where I can use PostGIS for my SQL queries in PostgreSQL and have the sde functionality.

I have a windows computer.

My process so far was:

1. install postgresql/postgis
2. copy st_geometry dll over to postgresql lib
3. run create enterprise geodatabase
4. create sde login

then inserted data from a file GDB into that databse connection and it worked like sde but I went to use a simple st_union postgis functions like this

select st_union(shape) geom from base_layers.hl_huc

and got this error

ERROR:  function st_union(st_geometry) does not exist
LINE 1: select st_union(shape) geom from base_layers.hl_huc
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function st_union(st_geometry) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

Can someone please tell me DETAILED instructions on how I can get PostGIS to work for this database!

I came across this pdf, but it is for older versions of arc and PostgreSQL and the directions are not very clear. For example on page 15 one of the steps is Edit a SDE config file and a PostgreSQL one pretty vague...

Ran create extension PostGIS in the DB. All the postgis functions are there, but I cannot use them!

Maybe there is way to switch between the spatial type backends?

CONCLUSION

Vince's third point led me down the path to figure out how to have ArcGIS SDE versioning and editing while maintaining the PostGIS backend. Note I may have made it more complicated than it had to be but nonetheless I figured it out

steps

1. copy st_geometry from.dll C:\Program Files (x86)\ArcGIS\Desktop10.4\DatabaseSupport\PostgreSQL\9.4\Windows64 to C:\Program Files\PostgreSQL\9.4\lib (or whatever version of arc and postgresql you have)
2. run esri tool "create enterprise geodatabase" (this tool will create a new postgresql DB with the needed sde login name and supply the password)
3. I created a superuser called postgis, then created a schema in the database called postgis and ran create extension postgis *this could be bad practice to have another superuser in the DB but I did not want the postgis functions to be in the same schema as the SDE
4. run esri tool "export geodatabase configuration keywords". database connection = the DB you just created
5. edit the file in notepad, go to line 27 change ST_GEOMETRY to "PG_GEOMETRY", SAVE!
6. run esri tool "import geodatabase configuration keywords" for that database connection and any layer you bring in now should have postgis default backend with sde functionality!

now I can use the awesome power of postgis in the backend and when I create a table all I need to do is right click on the layer in the db connection, manage and register with geodatabase. I should note when I click on register with geodatabase sometimes the arcmap shuts down and give me the crazy there was a serious error, please send error report to ESRI....

Answer 1

The reason you're having so much difficulty using ArcGIS and PostgreSQL together is that you're not doing it right.

You've got three basic problems, in reverse order of severity:

1. You're trying to mix-n-match PostGIS syntax with the SDE.ST_Geometry datatype

All database products have a mechanism for distinguishing between function libraries, principally by using database schemas to keep them separate. But because that can be tiresome (wise, but tiresome), they also provide a mechanism for specifying a preference. Unfortunately, PostgreSQL's error messages around this feature don't say "No, no, to use the sde.ST_Geometry type, vice geometry, you need to prefix the function with 'sde.'"

2. You're judging the Spatial Types and Functions standard against PostGIS

Esri was one of the first organizations to have standards-compliant geometry libraries in SQL databases, starting with DB2 and Informix (these date back to the same time when "SQL" was being added to PostgreSQL, and before PG support for ANSI SQL-92). Those libraries have been ported to all databases except Microsoft SQL Server (because it doesn't support datablade/extension/custom type libraries). PostGIS is standards-compliant, but they have scores more functions than that, mostly because they wanted to, and PostgreSQL was open enough for it to happen. Esri has a basic suite of functions that meet the standards, and type support across many RDBMS engines, some which have limits on the number of functions that can exploit custom types. It would be nice if Esri supported extra functions, but it is in no way a requirement to make effective use of the type. But consider this: The majority of extra PostGIS functions are for doing things that Esri has been doing for decades, and are available within Desktop (making them available to all RDBMS platforms, equally).

3. You're not utilizing the out-of-the box capabilities of ArcGIS with PostgreSQL

In your question you state you want to use PostGIS and ArcGIS together. Well, starting at ArcGIS 10.4.0, you can, and you don't even need to enable an enterprise geodatabase to do so. You have two choices: You can use native RDBMS capabilities out of the box, with Query Layers accessing the tables for display and tables with PostGIS geometry created via SQL or read-write ArcGIS database access, or you can enable the enterprise geodatabase, and instruct ArcGIS to create PostGIS data by default by changing the GEOMETRY_STORAGE keyword parameters to PG_GEOMETRY (in fact, that's the only option in RDS instances, since Amazon doesn't allow the sde.ST_Geometry function library to be installed in the black-box database). So it all comes down to what you want to do in this instance. ArcGIS doesn't care which methodology you choose (except that you won't get multiversion geodatabases, and geodatabase replication, and archive history, etc... without an enterprise geodatabase). Other than some quirkiness with Sort (Data Management) with PostgreSQL source tables, I haven't found any issues in PG interaction with ArcGIS 10.4.x.

Answer 2

ST_Union is expecting a PostGIS geometry type.

Since you are storing it ins a ST_Geometry type (ESRI proprietary format) you would have to use the SDE specific ST_Union function:

select sde.ST_Aggr_Union(shape) as geom from base_layers.hl_huc

Note, this will only work in Oracle or SQLite SDE

Answer 3

Messages look like when I forget to activate the PostGIS extension, PostgreSQL doesn't see the PostGIS functions. Try to run the following (it works in PgAdmin 4 - I don't have access to ArcSDE) :

CREATE EXTENSION postgis;