ArcGISPro choosing an ObjectID from a view. Then losing it.

The workflow is simple:

  • Create a view to filter the data in the database
  • Add the view to catalog in ArcGIS Pro
  • Extract the data to a staging table
  • Add the table to the map (note: why cant I work on the data in the catalog without adding to the map?)
  • Perform work – lots of GIS functions add and remove columns, perform field calculations. Note: if you haven’t tried the field calculation functionality in QGIS you should, its really good.
  • Finally, join the the data back to the source and use a sql update script to update the source data.

So whats the problem?

As I am working though the process of using this table in the ESRI platform, it drops the identity field, and leaves me without a key to join back to my source data.

ESRI identifies that there is a column that is an identity with unique values and guaranteed not null. It sets this column to a type of: ObjectID. Then as we all know, OBJECTID fields are now for esri use only and no longer maintained throughout the workflow.

So what about adding my own ObjectID field like in this post: Add and object ID to your view? Turns out this still doesnt work, as the software still sees the other id field.

This means that Pro is smart enough to look for an identity column but dumb enough to screw it up. The best thing to do in this case is create another column in the view that is a duplicate of the key with a different name. Then you can join that data on that field and still have a key for your records.

pgAdmin 4 Tips

pgAdmin 4 has a few useful things I should remember:

Query History is really neat. Second tab in the Query Editor page, shows a history of all the queries made and the outcome messages. Easy to find what you have already done.

Ctrl Space triggers code completion. Not quite as good as an IDE but helpful none the less

Keep you columns lower case and you wont have to use quotes for your tables in table queries.

Adding a Foreign Data Wrapper from PostGIS to MSSQL

ogr_fdw : foreign data wrapper

I discovered this by reading the Postgres Conference Slides. I am writing this down so I dont forget, as is the case for almost everything I put here.

I was reading the presentation by Regina Obe, co-author of several wonderful books on PostGIS, Postgres, and more.
You can find links to their presentation at Planet Postgis entry for March 25, 2019, or at Postresql Online Journal entry PGCONF 2019 data slides up.

Direct link to the PDF slides look under the section Other Relational Databases, but the rest is interesting as well.

My use case here is Windows PostGIS to Windows SQL Server over an enterprise domain using is an ODBC connection.

First I need to set up a Windows DSN entry for the ODBC connection. Open the ODBC Data Source, 64 Bit in my case, and add the system data source. When the connection attempt is made, it will use this to find the remote server on the domain.

My connection looks like this, with pguser and pguser as database name.

OPTIONS (datasource 'ODBC:pguser/pguser@dnsentryname',format'ODBC');

I do notice I am not getting any Spatial Data in my Foreign Tables. This is somewhat expected as SQL server does not natively send spatial data accross linked servers. However, the presentation says it should so I might need to revisit this.

Multiple Geometries in database checking

I loaded a Geodatabase with FME into Postgis recently. The data were polygons.

After loading I noticed it was taking a long time for the connection to be made, there were a lot of errors which isn’t that unusual due to some of the data originally coming from CAD and doesn’t meet OGC requirements.

That was not all though, there was something else going on with these data. I ran check geometry and ST_IsValid which told me that there were a whole lot of Ring Self-Intersection errors as expected.

Here was the interesting part:

Using this query:

SELECT ST_GeometryType(geom) as geomtype 
FROM public."MyTable"
group by geomtype

I found that the database had stored 4 kinds of geometry:


This was surprising because the source was a File Geodatabase and should only have one type of geometry, which should store as ST_MultiPolygon.

I am not sure if FME recreated these geometries or if the File Geodatabase itself was a geometry collection.

Additionally, using the Database Manager tool in Qgis, the table would not load as there were too many errors, that I think were QGIS enforcing the single geometry datatype.

Creating a point feature class with linear referencing in ESRI wierdness

I used ESRI to create a point feature class using linear referencing, loading the data into an SDE database on SQL server. The resulting data set would not display in QGIS. I checked the SRID, validated the geometry, and narrowed the results.

I have seen issues with Line feature classes in QGIS where the line has measure values in ESRI. These wont display. Could this be the same issue with these points?

Yep. Of all the stupid things for ESRI to do, the points have measures.

Here are the properties of the point feature class:

Feature Type: Simple
Geometry Type: Point
Coordinates have Z values: No
Coordinates have measures: Yes

By including the measures in the points, ESRI is forced to provide a value for the Z when adding the records to SQL. This is due to a requirement that data is loaded in X,Y,Z,M where each value is space delineated.

So an example point looks like this:
POINT (1199100.2299588919 880237.32388755679 NULL 4.2200000000011642)

There is no way to add the measure without adding a placeholder for the Z.

As for the lines, I am going to have to say that it is a bug to not display correctly in QGIS for the same reason.

UPDATE: Thanks QGIS contributors! This is now fixed to handle this data.

Finally, to examine your data you can use these MSSQL methods:


Spatially Enabled is not a thing. Location is an attribute. This is not 1990.

Databases now days, pretty much all of them, have the ability to store and index geometry and geography. In fact, I think it would take longer to look up databases that DO NOT have some ability to store, query, and index simple feature geometry.

So why is it, I am still seeing (I am looking at you Governmental Agencies) people refer to databases that are “spatially enabled” and by that they usually specify ESRI geodatabases.

Are you people living under a rock?

Even ESRI stores their data in SQL server formats and more or less follows the common rules for SRID management. What’s worse is that Academia seems to be split on those who understand data and analysis, and those who just seem to learn what an ESRI toolbox does. I am really disappointed to see the continuation of bad practices that ESRI has foisted on the world by people who really should know better. Location is an attribute. Location type (point, line, polygon) is also an attribute.

If a database has a time column, we usually don’t refer to them as temporal enabled, or temporal databases….

Adding Object ID to your view

QGIS and ESRI both like to see ObjectID (or an identity) in the data returned from a SQL query.

I use two methods to accomplish this.

1. Row_number() Over

Select ROW_NUMBER() OVER ( ORDER BY anydatacolumn) as ObjectID

However, ESRI doesnt like the returned data type so I convert it to an INT like so:

Select CONVERT(int,ROW_NUMBER() OVER (ORDER BY anydatacolumn)) as ObjectID

2. Newid() or NewSequentialId()

This will return a MS UUID (GUID) or sequential GUID. Apparently ESRI doesnt mind GUIDS as a ObjectID. I have yet to test with QGIS.

Moving geometry accross linked servers with MSSQL

MSSQL implements geometry via the Common Language Runtime (CLR). This implementation doesn’t allow you to call these function across linked servers. The solution is to create a view and  convert the geometry into x,y values (or lat/long for geography).

Then convert your data back into geometry when exposing the data in a view on the destination.



+ ' ' + CONVERT(VARCHAR(30), y)
+ ')', 2927) AS shapegeometry




Spatial Reference Tables

Spatial Reference Tables are the metadata of your spatial database:

There are two tables that are defined by the OGC standard for use with spatial column databases.

  1. geometry_columns – this table stores your catalog of data, where to find the geometry column, the dimensions of the coordinate system, the geometry type, and the SRID – Spatial Reference IDentifier (internal SRID, authority is not set here)
  2. spatial_ref_sys – this table contains the SRID  used locally and associates it with an authoritative SRID (such as the EPSG) the spatial reference text and the proj4text.

These tables (with a potential for a variation on name) are used in all spatial database systems that support the OGC. Even ESRI uses this method with their SDE data model.

One can choose to use these tables or create them as views to support other views. For example I may have a table of features, and a table for locations. In my database I dont care if a feature has mixed geometries (points lines or polygons) because that is just an attribute of the thing I am interested in.  (ESRI tries to make you tie everything to a point, line or polygon, and then attributes. This is backwards, and wrong).

In any case, in my database, I can create views that organize my data into a flat file type dataset, with only one type of geometry per view. I would then want to use a view to make my spatial_reference sys tables, to catalog the views.

Spatial References help:

I have found that is really useful for working with the projects and creating these tables. There is even a link to get the SQL insert statement for the spatial reference that you are interested in.


Here is the create code for each of the tables if this helps.

CREATE TABLE [dbo].[spatial_ref_sys](
 [srid] [int] NOT NULL,
 [auth_name] [varchar](256) NULL,
 [auth_srid] [int] NULL,
 [srtext] [varchar](2048) NULL,
 [proj4text] [varchar](2048) NULL,
 [srid] ASC



 CREATE TABLE [dbo].[geometry_columns](
 [f_table_catalog] [varchar](128) NOT NULL,
 [f_table_schema] [varchar](128) NOT NULL,
 [f_table_name] [varchar](256) NOT NULL,
 [f_geometry_column] [varchar](256) NOT NULL,
 [coord_dimension] [int] NOT NULL,
 [srid] [int] NOT NULL,
 [geometry_type] [varchar](30) NOT NULL,
 [f_table_catalog] ASC,
 [f_table_schema] ASC,
 [f_table_name] ASC,
 [f_geometry_column] ASC


ogr2ogr to load a database from a shapefile

GDAL, the  Geospatial Data Abstraction Library

Is a great set of tools for managing data with a spatial data type.

I use the ogr2ogr tool to load shapefiles or other data into databases. It will automatically create the spatial metadata tables that are part of the OGC standard.

Spatial Metadata Tables

Two tables are created for letting the user (and applications) know where the spatial column is in what tables they are located, and what the projection is. This is all in another post.


You can load the database using ogr2ogr. This will handily create the spatial metadata tables for you during load.

For example, on MSSQL server, loading from a shape file in WGS84 (EPSG: 4326) to WA State Plane (EPSG 2927):

ogr2ogr -overwrite -progress -f MSSQLSpatial “MSSQL:server=SERVER1;database=GPSTrails;trusted_connection=yes” Points.shp -s_srs EPSG:4326 -t_srs EPSG:2927

Here I am overwriting the table if it exists, showing progress information, indicating that I have a trusted connection converting from 4326 to 2927.

If you look over the data types you can use  for ogr2ogr, you can see that you could load from sqlite, ESRI FileGeodatabase, csv, and doxens of other formats.