agrc / open-sgid

A public repository for information, feedback, and known issues about the Open SGID and the CLI to maintain it.

Open SGID

This repository is the wiki and issue tracking location for all things opensgid.agrc.utah.gov. This is the PostGIS Cloud SQL mirror of the AGRC SGID.

If you are having trouble with the database, with the software you have chosen to use, please search the issues to see if this has been identified and solved with a work around.

Terms of Service

Please read, understand, and follow the terms of service for the Open SGID.

Version information

product	version
PostgreSQL	11.6
PostGIS	2.5.1

Connection Information

Database Driver	PostgresSQL
Instance	opensgid.agrc.utah.gov
Username	agrc
Password	agrc
Database Name	opensgid

ESRI client support

See the PostgreSQL database requirements

open-sgid's Issues

Historic Lake Bonneville layer error in ArcGIS Pro

Open SGID user:

I just added a layer from the OpenSGID connection, and it looks like there is a problem with it. It's the layer: opensgid.agrc.historic_lake_bonneville. When I drag it into a map, nothing is displayed. When I attempt to open the attribute table, I get this message in ArcGIS Pro:

drawing speed

From: Gary L. Ogborn
GIS-Natural Heritage Program Coordinator
(Greg has additional contact info for Gary, if needed)

The open sgid was slower to load a layer to the project and slower to draw/redraw the layer. I tried it in ArcMap 7.1 and in ArcPro with the same result.

(Greg's Note: overall in the feedback... Bert (WFRC), Buck (DNR), Sarah (DNR), Lena (DNR), and Phoebe ("U") reported that OpenSGID speeds where fast or good.)

Database name change request

From Bert:

would you consider calling it utahsgid instead of opensgid? I get the distinction you are trying to make but I think that's an internal agrc distinction and the branding might work better if you name it with external users in mind?

update layer names

using the meta table agol table names eg: Utah Grand Staircase Escalante National Monument, update the name to grand_staircase_escalante_national_monument instead of the current situation grandstaricaseescalantenm

https://github.com/agrc/cloud-sgid/blob/f587c66d22642bc489aca61161e7c7d46a4b2402/src/cloudb/__main__.py#L249-L250

exporting a subset of the data

From: Phoebe
This may be user error, but if you need to export out a portion of a layer (ex. I am trying to export the 5000’ contour for Salt Lake County to denote the Provo shoreline) the following error pops up:
Error exporting data.
Each grid size must be at least three times larger than the preceding grid size.
The table was not found [Export_Output]

Suggest use of Layer Files

#9
I suggest we add something like the following to The workaround section:
"To avoid having to do this in the future you can utilize Save As Layer File in Esri software to store this unique identifier field and and the layers calculated extent."

SGID integer fields are generating OpenSGID decimal fields

The issue

Small, big, and integer columns are generating numeric(n) fields in the OpenSGID.

Example

Year values like 1990 are being stored as 1990.0.

Work around

We can alter the tables after import and hopefully the issue can be fixed upstream in OSGeo/gdal#3345

update geometry type

all layers have a generic geometry type.

alter the table to make it more specific to help arcgis. arcgis pro does work with the generic type though.

SELECT DISTINCT geometrytype(shape) FROM test;

-- if one row use that type, if two, chose the multi variant?

ALTER TABLE test ALTER COLUMN shape TYPE GEOMETRY(POINT);

Unable to copy and paste features from Open SGID into new layers

ArcGIS Pro v2.7.0
Source Feature Class: boundaries.county_boundaries
Destination Feature Class: local shapefile

When I attempt to copy and paste features from Open SGID layers I get the following error message:

When I attempt the exact same copy/paste from internal, I get the appropriate message:

OpenSgid layers not printing from ArcPro 2.9.2

Im on windows 10 virtual machine on a macbook....if I print or export (pdf / png) a map containing opensgid layer, only a couple of layers draw on the resulting page/graphic.

ArcMap requires selection of unique identifier fields

The problem

ArcMap shows a pop-up requesting the user to select unique identifier fields.

The reason

When ArcMap creates a query layer from an Open SGID table, it does not automatically choose xid as the primary key or unique identifier field.

The workaround

Select xid and click Finish. The xid field is the name of the primary key when we load the data into the Open SGID.

To avoid repeating the steps above, use the Save As Layer File to store the unique identifier and layer extent.

Examples

Duplicate data in location.address_points

There are thousands of addresses duplicated several times in the database.

Running the following shows the same address listed 20 times
SELECT * FROM location.address_points WHERE fulladd LIKE '19 COUGAR LN'

Error exporting data from OpenSGID to a new feature class in ArcGIS Desktop 10.8.1

About half the times I try to download data from OpenSGID, I get the following error:

It always works eventually, although it can take several tries. Any thoughts on what might cause this? In the example above, UtahParcels is the feature class I'm trying to extract data to.

Thanks for your help!

Ignore utilities.broadband_service from data updates

This dataset's polygons are "invalid" in postgis because of the differences in sql servers implementation of shape validity. Repairing the invalid geometries (40%) uses all of the time allowed by the serverless function. The shape repair is skipped for this table specifically. But the data import still happens and corrupts the shape.

The import script needs to skip this table entirely to avoid corruption and allow for another method of updating the data.

Geoprocessing tool fails for not having OID

The problem

Some ArcGIS geoprocessing tools require a real esri object id.

The reason

When using Open SGID data in ArcGIS the data is ingested as a Query Layer. Query layers do not have an esri OID while they will have a primary key that is unique.

Several ArcGIS geoprocessing tools require an OID field in order to successfully execute. Without an OID field in the layer you're processing, the tool will fail with ERROR 000339: Input does not have OIDs..

This behavior and solutions are documented in ESRI help pages and GIS StackExchange.

The workaround

Query layer's without an esri OID was resolved in ArcGIS Pro v2.5.

If you are at a version below v2.5, CopyFeatures will create a temporary feature class (either in-memory or in a file geodatabase) with OIDs. You can then run the geoprocessing tool on the temporary feature class.

Examples

This code fails with ERROR 000339:

arcpy.analysis.Buffer('opensgid.boundaries.county_boundaries', out_name, "50 Meters", "FULL", "ROUND", "ALL", "", "")

This code successfully runs to completion:

temp_features = r'in_memory\temp_features'
arcpy.management.CopyFeatures('opensgid.boundaries.county_boundaries', temp_features)
arcpy.analysis.Buffer(temp_features, out_name, "50 Meters", "FULL", "ROUND", "ALL", "", "")

Open SGID tables do not contain metadata

The problem

I cannot find more information about the table I am viewing.

The reason

The Open SGID does not store metadata in the database like an esri enterprise geodatabase.

The workaround

Table metadata is exposed through multiple channels. To learn more about the the data you are using, start by finding the dataset on the AGRC Open Data and continue to the AGRC Website data pages for all of the information we have.

Example

1. Use opensgid.location.addresspoints and have a question about a field.
2. Browse to https://opendata.gis.utah.gov/datasets/utah-address-points and read about the data
3. Browse to https://gis.utah.gov/data/location/address-data/#AddressPoints and read about the data.

remove dialect when creating tables

that might remove the creation of the empty shape field for stand alone tables.

https://github.com/agrc/cloud-sgid/blob/f587c66d22642bc489aca61161e7c7d46a4b2402/src/cloudb/__main__.py#L231-L232

Could also try sqlite dialect and specify shape explicitly

fields with domains only display codes

The problem

The MSSQL Enterprise Geodatabase has the concept of domains where data is stored as a code but the value is displayed in the software.

The reason

The Open SGID is not an enterprise geodatabase and the esri domains do not transfer in our extract transfer load process.

The workaround

Currently, there is no workaround. AGRC is in the process of updating every domain in the SGID to make the domain equal the value where possible. This will replace the nebulous codes with the descriptive values in the future.

This is a large effort, for the time being we will export lookup tables and store them in the Open SGID. Users will need to create a query that joins this table with the data they are interested in.

Examples

// TODO

Table attribute casing has changed

The problem

The Open SGID uses all lowercase attributes.

The reason

The SGID used a mix of lower, upper, snake, camel, and title case. To be more consistent, AGRC accepted the PostgreSQL lower case convention. Otherwise field names need to be quoted to persist their casing.

Example

[CountyBoundaries].[FIPS] becomes [county_boundaries].[fips]

MSSQL Server is not case sensitive when executing queries so this should not cause problems.

Cannot connect to Open SGID with `[server closed the connection unexpectedly]`

The problem

Esri clients are unable to connect to the Open SGID. The common error message from a 10.7.1 client and Pro 2.5 is

Unable to get database list

A database connection exception has occurred. Unable to connect to database server to retrieve database list; please verify your server name, user name and password info and try again.
Underlying DBMS error[server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request. SQLSTATE=].

The reason

A network firewall is blocking network traffic

The workaround

Talk to your network IT professionals to make an exception in the firewall to allow traffic to the Open SGID.

PostgreSQL NULL sorting behavior

The problem

The Open SGID sorts NULL values in the opposite direction than the SGID and esri file geodatabases.

The reason

A PostgreSQL order by ascending query will order NULL values last, while the SGID (Microsoft SQL Server) and file geodatabases order them first.

The workaround

Invert the sort to have NULL values ordered they way you would like.

Examples

The PostgreSQL language supports the NULL value ordering in the ORDER BY clause. documentation

The following will crash ArcGIS Pro version < 2.5 if used as a query layer query.

SELECT * FROM municipal_boundaries ORDER BY minname NULLS FIRST;

I cannot find the SGID table I am using in Open SGID

The problem

I have a project or code that references SGID tables and I cannot find the same table in the Open SGID.

The reason

The Open SGID uses all lower case table names; It is a PostgreSQL convention. We also snake cased words instead of camel cased. For example, DominantVegetation became dominant_vegetation.

Refer to the table mapping document to find, replace, and update data references in projects and code.

The layer you are looking for might not be in the table mapping since AGRC trimmed down what layers reside in the SGID. We shelved layers and made others static. You can read about the process in the SGID Product Relaunch Update blog post and the Where is the SGID going section of an older blog post.

Since not all tables were migrated from the SGID to the Open SGID, you may need to use our shelved or static ArcGIS Online services or replace the outdated and unmaintained data with a higher quality source.

Example

1. I have a project referencing SGID10.BIOSCIENCE.DominantVegetation. Update the table reference to use opensgid.biosciense.dominant_vegetation. Be careful to inspect the attributes as they are also lower case and snake cased.
2. I have a project referencing SGID10.BIOSCIENCE.BeaverRestorationAssessment. This dataset was shelved. Use Utah Beaver Restoration Assessment from ArcGIS Online.
3. I have a project referencing SGID10.BIOSCIENCE.Habitat_Bison. This dataset was removed since it was not current. Search for a new source. Consider the DWR sourced data.

Replace "agrc" user with "ugrc"?

I wonder if it would be worth creating a new user user and updating the docs. We wouldn't have to get rid of the agrc user. Just to help with the name confusion.