We are attempting to integrate Manifold / SQL Server (sql server native geometry) / D3.js/Leaflet web mapping. We have been using Manifold since v4.5 but this is new ground for us and I wondered if anyone can shed some light on best practices to adopt for when the SQL Server end is multi-tenanted.

On a pilot scale we have an SQL Server table with geometry column linked into Manifold for editing. We are also able to extract the data out with a stored procedure into a geoJSON file for Leaflet to display. However the scale-up is problematic due to potentially having multiple clients accessing the SQL DB with their GIS's.

So far we have tried:

1. A where clause in the Database Console window for the data linking. Does not work as the bottom text area where the query is defined seems to only allow table linking. If a Drawing is linked (File-Link-Drawing) to the linked SQL table, it is read only. Note that we would only use this method if it could be done programmatically.

2. SQL Views are sometime editable depending on the underlying query. Unfortunately client specific views wont work as a view with a geometry column is also read only.

We are considering dynamically adding client specific SQL Tables to hold only their spatial data and perhaps using triggers to ensure a central repository remains updated.

Appreciate if anyone has any other approaches or suggestions on how we might tackle this.

Considering you 3 point... "Provide geoJSON data to the javascript web maps displayed within our application." I would chose one of 2 options:

Use Server Console and setup all Manifold worksations as clients to edit (check in/out) drawings. If you need to use SQL Server geoms and create any specific logic you could create a batch job to do the ETL process of converting the drawing component into a table with you specific geom.

Use leaflet or OpenLayers as a client and create a web editor saving data inside SQL Server which could be edited back into Manifold.

By choosing the first or second option may depend on other details like how many clients you would need, what type of logic you need.

I think your Option 1 would work in a single network/ multiple client environment. However I must belatedly clarify the term 'client' here. We have a browser-based, common application platform that serves application modules to multiple client company groups.

Each company group can have multiple subsidiaries and users. Of course they can be located anywhere in the world and are possibly not even be using Manifold. Client groups and companies using Manifold can naturally set up an Manifold Enterprise Server on a locally hosted SQL instance to handle their base data i.e. data not related to our application platform modules.

However for application module related data, it must be stored on our servers and within the schema. What we were hoping to do for Manifold using client groups (including our own companies) is give them linked, RW access to their data only. Limiting data access seems to be the sticky bit.

Your Option 2 makes me think that this is not possible and that we should rather be looking to build full editing functionality into a browser based app.

I think your Option 1 would work in a single network/ multiple client environment...

In a single network would be better but if your users are spread around but have a fast internet connection it could also work. I guess it depends the amount of data to be transferred around. For this option I was considering that all of them must be using Manifold, because it would be easier to setup (specially user permissions).

Considering what you describe above, I partially agree when you say....

Option 2 makes me think that this is not possible and that we should rather be looking to build full editing functionality into a browser based app.

You are building something like this functionality no matter what option you choose. Depending the path you take, it will reflect the amount of "coding time" and/or setting up SQL Server ETL tools to provide the solution you refer.

If you need decide to build a browser based editing app it also depends on what editing features you need. To create point, likes or polygons you can use any of the existing javascript libraries that have this functionality, but much more than this it won't be an easy task and you will need a bigger budget.

Apologies for the slow reply. We had a power failure here and as it was after midnight already, I though a kip was in order.

As you say though, the construction of a full browser based editing UI is a big task and one that is certainly beyond us. There are a few more things we want to try on the Manifold end as it seem odd that it cannot be done. In principal though, I think an Sql Server based data segregation solution is doable.

Thank you for the feedback!

For anyone late to the thread, I am left with the following assumption.

"There is currently no way in Manifold to select a subset (by attribute not bounding) of SQL Server Geometry records from an SQL Server instance that will result in a linked, editable Drawing."

Cartomatic, your prod got me testing SQL views again and I found that the view is actually editable...sort of!

After I link in the Drawing from the SQL view, I can add areas etc. What I had noticed before was that the features I added in the linked view would disappear on refresh but now I know why.

Although I can add features ie. edit the geom, the other fields cant take a value - possibly because the record - has not yet been committed to SQL version. Even if I edit the attribution using 'Instant Data' it wont stick.

The problem this creates is that without attribution, the filtered view query will not return the new data upon refresh.

Still checking if there is a way around this but any thoughts appreciated.

i have looked at https://msdn.microsoft.com/en-us/library/ms180800(v=sql.110).aspx and https://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx and it looks like the views should be updateable when you either create it wirth 'WITH VIEW_METADATA' or use INSTEAD OF triggers. The latter would be handy for joined tables, computed values, etc.

I would use sql server profiler to see what manifold sends out. Not sure why the objects are not editable (some kind of invalid where clause perhaps?). In such case a view with triggers could potentially account for this.

Not sure if we are talking cross purposes now. So to clarify, the geoms and their attributes linked in from SQL server are editable. But when I add a new feature, the attribute edits dont get saved back into SQL Server. The result being that the view query does not return the data on refresh.

I also thought of an instead of trigger but importantly the attribute edits cant even be made inside Manifold, so I dont think they are being passed in to SQL at all. Will try though.

I'd back up what cartomatic mentioned, crack out the Sql Server Profiler and see exactly what is being sent down the wire. If a trigger fails, then no error is bubbled up, which could be causing some issues, I know this has manifested for me in the past as geom getting committed, but attributes not.

I am using Express so had to find a 3rd party profiler addin (Express Profiler v2.0). Below is the code from a sequence of tests. Note the the view is filtered by company code 'co_code' and I have removed the instead insert trigger.

What I noted:

- The 'co_code' is correctly blank in the initial insert as I have not edited the attributes yet

- The OID, which is an identity column in SQL, is correctly captured back into in Manifold immediatly upon geom insert.

- Despite the OID being correct in the SQL raised by the 'instant data - pre refresh' edit, the sql fails to update the master table.

- Despite the code in the 'after refresh' update test being identical to the code in the 'instant data - pre refresh' update, this time it works.

I am starting to think that the issue is:

- Although the data is inserted through the view, the view query excludes it immediately as there is no company code. Consequently the update statement, which is run against the view, is trying to act on a record not in the view record set.

The only way this can/should work is for Manifold to only submit the GEOM to SQL Server when manually triggered to do so. To do this requires Manifold to cache the Drawings locally which it does not seem to do. For the record I have all local caching options turned on.

--- 1. The following SQL is captured immediately on mouse-up in the Manifold Drawing after adding an area

you might be right that the update does not work as the view filters on the code and the newly inserted record cannot be updated as it simply does not exist in the view's data subset.

when adding a new object (even with the instant data on) manifold first inserts a geom with all the other fields empty and then tries to update them in a separate go. This has been mentioned as problematic many times when you have not null constraints.

I tried to get clever with having an update trigger on the view that collects the values from 'inserted' and then performs updates directly on the underlying table. It works for UPDATEs but not during the insert/update sequence. Note sure why though because Manifold already has the correct id as evidenced by the update code caught by the profiler.

The problem was what you have observed before - it could not update the newly inserted record as the view was filtering it out. In theINSTEAD OF insert trigger it is therefore important to populate the data that is used for filtering. Then everything is works like a charm ;)

Try the code below (tested with sqlserver 2k12, but it should work ok for 2k8, 2k14 too). Does exactly what you need. Provides the user / user group encapsulation you are after and is ok with inserts updates and deletes. It creates 2 views that read from one source table and use INSTEAD OF triggers to manage CRUD.

Wow excellent code Cartomatic, thank you it works well. Running it multiple times, we twice saw an odd effect where a new area feature disappeared but could not replicate the key sequence that led to it.

I tried this same trigger technique earlier but it failed and I believe the key difference is that you are not passing the filtering value from Manifold but rather hard-coding it into the trigger. Logical in retrospect!