4

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....

  • What happens with: select st_union(shape::geometry) geom from base_layers.hl_huc – HeikkiVesanto Aug 18 '17 at 14:07
  • ERROR: cannot cast type st_geometry to geometry LINE 1: select st_union(shape::geometry) geom from base_layers.hl_hu... ^ ********** Error ********** ERROR: cannot cast type st_geometry to geometry SQL state: 42846 Character: 22 – ziggy Aug 18 '17 at 14:07
  • Have you gone through: desktop.arcgis.com/en/arcmap/10.3/manage-data/… – HeikkiVesanto Aug 18 '17 at 14:08
  • 1
    What about: select st_union(st_geomfromewkb(st_asbinary(shape))) geom from base_layers.hl_huc – HeikkiVesanto Aug 18 '17 at 14:14
  • 1
    Do you have a sde schema at all, the second one should work? The first one works because you are converting it into a PostGIS compatible geometry format. ESRI have developed their own geometry format so you can't use PostGIS functions on ESRI geometry without converting it first. You would then have to convert it back into a ST_Geometry type to add it into ArcGIS. – HeikkiVesanto Aug 18 '17 at 14:45
4

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.'"

  1. 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).

  1. 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.

  • I use the database connection+query layers from arcgis to postgresql all the time but many times when I create layers in postgres/postgis bringing them into arcgis is fraught with drawing errors (while QGIS I have no problems) and the database connections are often real slow to load the layers – ziggy Aug 21 '17 at 14:48
  • that is why I wanted to use an enterprise geodatabase but still have the flexibility of using postgis. I find using postgis way more efficient and flexible than straight esri tools (although ESRI can get me the same results..albeit in more steps) – ziggy Aug 21 '17 at 14:50
  • how do you do this " 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" – ziggy Aug 21 '17 at 14:52
  • 1
  • I am accepting your answer. your last point about changing the geometry storage in the sde configurations led me to figure it out. I am going to update my question with my final results and what exactly I did – ziggy Aug 23 '17 at 19:06
0

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

  • the query does not work even with my sde schema... – ziggy Aug 18 '17 at 15:20
  • select ST_Geometry(ST_AsText(st_union(st_geomfromewkb(st_asbinary(shape)))),3424) geom from base_layers.hl_huc; this worked but i still cannot add it to arc.. I feel like I am getting closer though – ziggy Aug 18 '17 at 15:21
  • Do you get an error with the query in my answer? – HeikkiVesanto Aug 18 '17 at 15:40
  • 1
    Not great. There seems to be only about 30 or so. I would do all my analysis in PostGIS, view my data in QGIS. Then if needed in ArcGIS, convert to ST_Geometry, I'm sure the sde.ST_Geometry does work. – HeikkiVesanto Aug 18 '17 at 15:59
  • 2
    please move this discussion to chat chat.stackexchange.com/rooms/939/gis – Mapperz Aug 18 '17 at 16:20
0

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;
  • didnt work, the extension is there. I see all the postgis functions, the DB just is not recognizing them. – ziggy Aug 18 '17 at 14:02
  • 1
    I think SDE essentially replaces PostGIS as the spatial extension, or at least replaces the PostGIS geometry type. – HeikkiVesanto Aug 18 '17 at 14:03
  • but then there links like support.esri.com/en/technical-article/000010280 – ziggy Aug 18 '17 at 14:04
  • 1
    @HeikkiVesanto NO, the sde.ST_geometry functions do not "replace PostGIS". They are parallel, in their own namespace. gisnside, Your similarity is correct, but your conclusion is not -- The problem is using PostGIS functions on non-PostGIS data. – Vince Aug 19 '17 at 0:29
  • 1
    It does not replace native geometry on Oracle (aka SDO_GEOMETRY), and it does not replace native geometry on PostgreSQL (aka PostGIS). You wouldn't want to use them interchangably in the same query (that's just nuts), but you can host two departments in the same database, each using a different geometry type. You can also register PostGIS geometry and geography with geodatabase for versioning. The installations are side-by-side and non-conflicting. I've been using geodatabase and non-geodatabase PostgreSQL servers exclusively for the past four years at client sites. Trust me on this. – Vince Aug 19 '17 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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