SELECT * INTO sde.max FROM sde.data Where y in (SELECT max(y) FROM sde.data GROUP BY x); ALTER TABLE sde.max OWNER TO sde; GRANT ALL ON TABLE sde.max TO sde;
. Notice the use of the ‘sde.’ schema prefix and the permissions queries, which we found necessary (see stumbling blocks below). We operationalized this by semi-colon delimiting and changing table names where necessary. This could be made much more efficient by getting a list of tables with the name format given to the tables that we wished to subset, but we were more interested in getting a result than more completely operationalizing at this time.
sdelayer -o register -l max,shape -e npc -C objectid,sde -i sde:postgresql:localhost -D sde -s localhost -u sde -p sde -t st_geometry
. We operationalized this by repeating this in a .bat file and changing tables names. Again, this could be made more efficient by using an sde command to get a table list and looping through all tables that had names which matched the max subset table name format.
- The most difficult problem we ran into was the “DBMS table not found (-37)” error, which apparently is quite common for a variety of reasons with SDE. This ultimate came down to needing to make sure the table was stored under the sde database, in the sde schema, and that the table was owned by sde. I had assumed that making the owner of the database sde would cause new tables to be sde owned by default, but this was not the case.
- You may need to update your SDE/ArcGIS Desktop to 10 SP3. This was mentioned in some forums with the above error and with other errors around not being able to see tables in an SDE DB. To do the update, you need to download/install SP3 on desktop/sde and then run “update database” from the database properties under ArcCatalog. However, you must create a direct connection to do the update … update does not work under the normal ArcCatalog Spatial DB connection.
- General SDE recommendations: My rule of thumb is“anything you can do in arcgis/catalog, do there … all else do in pgadmin”. Also remember to refresh after all steps if there’s some result you’re trying to see. pgAdmin and ArcCatalog both need refreshes before showing updates.