This is a response to a blog post by Ole Magnus Habberstad
First of all it is great to see someone getting around to doing these kind of experiments!
My thoughts from a non-esri perspective
I am currently involved in a web project; Single Page Application served off an ASP.Net MVC backend with several Esri ArcGis services. Esri/ArcGis is a nice tool to do the visualization (like symbolizing aggregated data or time series and overlays in the map) from the server side.
Using the user interface tools available for the web has been a mixed experience – but then the project is not (yet) a map-centric solution like the Esri components feel like they are meant to be.
EF to read data
We are reading data using EF – including features (geometry) and drawing them in graphics layers. EF supports DbGeometry which wraps the SqlGeometry (and Geography) and we can do both advanced querying (distance to, inside, overlaps, etc) and other geometric operations (like buffering, aggregation, etc.).
Currently we are using a geo-processing service to store data. Why do we do this? Probably because the model was created using ArcGIS. This means we have a SDE layer in the database. Which is nice if the data is going to be used from different type of clients (e.g. ARCGis desktop AND web) but can be very annoying… Also using geoprocessing allows us to have some sort of transaction for saving the data hierarchy which the REST API doesn’t. EF on the other hand has transactions built in.
While the arcgis python has open cursors the SDE-layer locks the database. Reading data through the ArcGIS Rest operates like doing a READ UNCOMMITTED on the main tables in the database. Reading committed will hang/wait for the python script to release its cursors, so in EF I have to include Read Uncommitted as transaction scope while reading.
Keys and IDs
Another annoyment I have noticed in our model is SDEs use of OBJECTIDs with unique indexes and no primary keys on the tables – I am not very familiar with the SDE, so this might be some (local) modeling issue (?). This affects the (generated) EF model, but as you point out one can generated these unique ids using the “next_rowid”.
More questions I have (non tested yet)
What will happen when I store different types of geometries in the same feature (column)?
In SQL Server there is no such limitation, so in contrast to SDE I can store points, polygons, multi-lines, etc., in the same feature/column. Why does SDE still have this limitation?
Can I somehow omit the SDE layer completely and effectively only use ArcGIS for delivering the visualizations of my data and the familiar user interface (tools)?
Where to go from here
Maybe we should get together, grab a beer and solve all the problems in the world 🙂