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

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.

CREATE SERVER svr_sqlserver FOREIGN DATA WRAPPER ogr_fdw 
OPTIONS (datasource 'ODBC:pguser/pguser@dnsentryname',format'ODBC');
 CREATE SCHEMA IF NOT EXISTS ss;
 IMPORT FOREIGN SCHEMA "dbo" FROM SERVER svr_sqlserver INTO ss

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.

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:

Shape.AsTextZM()
or
Shape.ToString()


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

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.

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.

Example:

SELECT x
,y
,GEOMETRY::STPointFromText
('POINT(' + CONVERT(VARCHAR(30), x)
+ ' ' + CONVERT(VARCHAR(30), y)
+ ')', 2927) AS shapegeometry