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:
- install postgresql/postgis
- copy st_geometry dll over to postgresql lib
- run create enterprise geodatabase
- 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...
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?
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
- 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)
- run esri tool "create enterprise geodatabase" (this tool will create a new postgresql DB with the needed sde login name and supply the password)
- 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
- run esri tool "export geodatabase configuration keywords". database connection = the DB you just created
- edit the file in notepad, go to line 27 change ST_GEOMETRY to "PG_GEOMETRY", SAVE!
- 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....