Q&A - Question

In case of using WKB or WKT a better solution could be using TGIS_LayerSqlOgis and OpenGis sql format, but there you also have two tables. Single tables are prefered in Postgis, Oracle Spatial and MsSql Spatial formats but they require a native geometry type. But you can create a view that can include a geometry column.

Artur,
Thank you for your response. When you say that I can create a view that can include a geometry column, are you suggesting a view of the goemetry features to match the OGC format, while the original table maintains the feature data? I was able to get something like this to work after manually registering the table/view in the GEOMETRY_COLUMNS table, although it felt a bit 'clunky'.
-Nate

You can replace a standard features table with a custom view by setting Features key in .ttkls file :

Features=TABLE/VIEW NAME; if provided will replace standard features table

If I understand the .ttkls approach, the geometry will still come from the Layer specified in the ttkls, and adding a Features setting will point Tatuk Editor to a different features table without needing to modify the GEOMETRY_COLUMNS table. However, when I try to load the layer I get a "File cannot be opened" error:

SQL error code = -206
Column unknown
UID
At line 1, column 40

I tried both the working Features table, and an identical features table with a different name (which put the error at column 44).  I would expect it to look for a "GID" column, rather than "UID", so I'm not really sure what to make of the error.

Artur,

Am I correct that the join between the geometry and features tables on the GID (or UID??) is an inner join rather than outer? That is how it appeared to behave when I deleted a couple feature rows and left the geometry table alone. If so, that may give me some options.

My overall goal is to create some more interoperability here in our office where we need to access a common data set with a variety of tools serving different purposes (Tatuk Editor, ArcMap, QGIS, AutoCAD, in-house apps developed with Delphi/Tatuk, Python and R scripts, etc.). I've been thinking that WKB could help address this need, perhaps someday using views of PostGIS geometries, but many of the applications interact better with single-table feature class formats and I was hoping to not need to create three views for each feature class I want to make available.

If Tatuk is loading the OGC WKB tables with an inner join on the GID then I could, in theory, have a single table of point geometries referenced as the Layer in the ttkls, and each subset feature class would then be specified in Features setting of the ttkls file as a single-table view that had a second reference to the geometry ignored by Tatuk, but readable to other software packages.

For instance:

MASTER_GEOM_TABLE:
    GID
    XMIN
    YMIN
    XMAX
    YMAX
    WKB_GEOMETRY

FEATURE_VIEW_1: (referenced in ttkls as Features=, access view directly in other software)
    GID (subset of those in master table, based on SQL where clause)
    POINT_NAME
    POINT_TYPE (this would be used in SQL where clause)
    INSTALL_DATE
    WKB_GEOM (view to WKB_GEOMETRY above, but ignored by Tatuk?)

FEATURE_VIEW_2: (referenced in ttkls as Features=, access view directly in other software)
    GID (different subset of those in master table, based on SQL where clause)
    POINT_NAME
    POINT_TYPE (this would be used in SQL where clause)
    INSTALL_DATE
    WKB_GEOM (view to WKB_GEOMETRY above, but ignored by Tatuk?)

ttkls_1:
    Layer =MASTER_GEOM_TABLE
    Features =FEATURE_VIEW_1

ttkls_2:
    Layer=MASTER_GEOM_TABLE
    Features=FEATURE_VIEW_2

Am I looking at this correctly? Thanks for your help!

-Nate

This can work. The WKB_GEOM is ignored when checking a layer fields.

Hi,

Is this confirmed that it works? 

Do you have a practical example of how you did this using a view?

Greetings Danny...

Hi Danny,

It's been a few years since I worked on that, but I did get it to work and still have some of the WKT views running. I don't recall where I was with WKB, but once the WKT worked that was all I needed at the time. I was working with points, and concatenated X and Y database fields into WKT format to form geometry. Points are also convenient because the min and max all match the point. The SQL I used for two views (geometries and features) to create an inventory feature class are below.

Let me know if this isn't what you were asking and I can try to recall what else I did.

-Nate

----

CREATE OR ALTER VIEW G_INVENTORY(
    UID,
    XMIN,
    YMIN,
    XMAX,
    YMAX,
    WKT_GEOMETRY)
AS
SELECT
                            I.INVENTORY_PK AS UID,
                            I.X_COOR AS XMIN,
                            I.Y_COOR AS YMIN,
                            I.X_COOR AS XMAX,
                            I.Y_COOR AS YMAX,
                            ('POINT (' || I.X_COOR || ' ' || I.Y_COOR || ')')
                                AS WKT_GEOMETRY
                        FROM G_INVENTORY_FEA I

 

CREATE OR ALTER VIEW G_INVENTORY_FEA(
    UID,
    INVENTORY_PK,
    INV_DESC_PK,
    PT_NAME,
    INV_NAME,
    SAMPLE_TYPE,
    MEDIA_PK,
    MEDIA,
    SD_STATUS,
    X_COOR,
    Y_COOR,
    TEST_WKT)
AS
SELECT
                I.INVENTORY_PK AS UID, -- FOR TATUK OGC WKT COMPATIBILITY
                I.INVENTORY_PK,
                D.SAMPLE_DESCRIPTION_PK,
                I.INVENTORY_NAME,
                D.SAMPLE_NAME,
                S.SAMPLE_TYPE,
                S.MEDIA_PK,
                M.MEDIA,
                ST.SD_STATUS,
                I.X_COOR,
                I.Y_COOR,
                ('POINT (' || I.X_COOR || ' ' || I.Y_COOR || ')')
                    AS WKT_GEOM
            FROM INVENTORY I
                JOIN SAMPLE_DESCRIPTION D
                    ON I.INVENTORY_PK = D.INVENTORY_PK
                JOIN SAMPLE_TYPE S
                    ON D.SAMPLE_TYPE_PK = S.SAMPLE_TYPE_PK
                JOIN MEDIA M
                    ON S.MEDIA_PK = M.MEDIA_PK
                LEFT JOIN SAMPLE_DESCRIPTION_STATUS ST
                    ON D.SD_STATUS_PK = ST.SD_STATUS_PK

            WHERE I.INVENTORY_PK >= 0 -- REMOVE DEPRECATED LOCATIONS
;

Hi Nate,

Thanks for your reply!

I created a view of a Table_A and Table_A_FEA using MSSQL.

What I read in your answer is 2 things:
- you do now know whether this will work with WKB
  I do not see a difference in using a view as did for WKT
- you talk about points and we we need all type of geometries.

So we made a view and we will test it whether this view with wkb can be opened using Autocad.
 

Hi Danny,

That sounds about right to me. If you are looking at non-point data, I expect you will need to get the bounds for the first query with STEnvelope. I mostly work in PostGIS rather than MSSQL and don't know the exact syntax, but this link may help:
https://stackoverflow.com/questions/13587553/minimum-and-maximum-points-for-a-series-of-geometry-data-types/19629000

There is also an OGC compliant STAsBinary that may help you get the geometry column in the right format.  
https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stasbinary-geometry-data-type?view=sql-server-ver15

-Nate