topic Re: Generating SQL Geometry on the Fly in View or Query Layer in Data Management Questions

Generating SQL Geometry on the Fly in View or Query Layer

MeleKoneya — Fri, 22 Nov 2013 15:05:58 GMT

I was watching an ESRI video called "Web Enabling Databases with ArcGIS for Server" and I thought I heard them say that SQL Geomety could be created on the fly in a view or in ESRI terms with a Query Layer.

I have a couple of SQL tables that have LAT/LONG and I would like to used this information to create SQL geometry for each row rather than creating an event theme from the LAT/LONG values. Due to restrictions on the database table, I am not able to add a Geomtry field to the table itself so I thought I would try to do it via a view/query layer and bring it into ArcMap that way.

I have not been able to find any examples of doing this.

Let me know if this is possible and more importantly, if there are some examples of doing so.

Thanks,

Mele

Re: Generating SQL Geometry on the Fly in View or Query Layer

VinceAngelo — Fri, 22 Nov 2013 15:36:54 GMT

The principal problem with creating features on the fly is the inability to use a spatial index.
The draw performance is usually sufficiently awful that you'll be willing to jump through the
hoops necessary to have geometries added to the base table.

Note that "Query Layer" is not an Esri term for a view. Query Layers are queries on existing
objects, be they tables or views. If you can construct a view which returns points from
the lon/lat values, then you can make a Query Layer which queries it.

If you want help with the view construction, you'd have to specify what database you're using
(product, version, and sub-release), along with details on your ArcGIS version (including.
service pack) and s full description of the table (and your preferred rowid integer column).

- V

Re: Generating SQL Geometry on the Fly in View or Query Layer

MeleKoneya — Fri, 22 Nov 2013 16:01:10 GMT

Thanks Vince for the information and clarification of terms.

I would like to give it a try and compare the performance to that of an event theme as I am not sure that our agency is ready to change out tables to add SQL geometry.

I am using SQL 2008 R2 SP2 with ArcGIS 10.1 SP1.

The existing SQL table contains the following fields

[ATTACH=CONFIG]29289[/ATTACH]

I appreciate any further assistance you can provide.

Thanks,

Mele

Re: Generating SQL Geometry on the Fly in View or Query Layer

MarcoBoeringa — Sun, 12 Dec 2021 00:19:39 GMT

I was watching an ESRI video called "Web Enabling Databases with ArcGIS for Server" and I thought I heard them say that SQL Geomety could be created on the fly in a view or in ESRI terms with a Query Layer.
...
Let me know if this is possible and more importantly, if there are some examples of doing so.

Yes, it is possible. I have recently been testing this on a SQL Server Express 2012 geodatabase using the Shape.STCentroid() and Shape.STConvexHull() statements to dynamically generate derived shapes in a custom SQL Server view (Please note these specific commands imply already existing shapes present in another layer, they do however create new features "on the fly").

I used the following statements for defining the database views in SQL Server Management Studio:

SELECT OBJECTID, Shape.STConvexHull() AS ShpConvexHull
FROM [YOURCOMPUTERNAME\YOURDATABASENAME].YOURLAYERNAME

and:

SELECT OBJECTID, Shape.STCentroid() AS ShpCentroid
FROM [YOURCOMPUTERNAME\YOURDATABASENAME].YOURLAYERNAME

Please note the [YOURCOMPUTERNAME\YOURDATABASENAME] part is a bit depended on your SQL Server configuration. It should be auto-generated for you once you create a new view in SQL Server and choose the table that serves as the basis. The ShpConvexHull and ShpCentroid are just arbitrary names I defined for the new shape fields, you can name them anything you like.

The principal problem with creating features on the fly is the inability to use a spatial index.
The draw performance is usually sufficiently awful that you'll be willing to jump through the
hoops necessary to have geometries added to the base table.

Depends on what the purpose is and how big the original layer. I did a test based on an un-generalized height contour layer based on a 8x8 meter DEM. It contained 10270 feature records. Creating a view with Shape.STConvexHull() statement, resulted in draw times of a few seconds for the entire layer in ArcMap. Not to bad, considering a kind of "worst case" scenario using an un-generalized height contours layer. Of course, with millions of records, it is probably a different story...

This was on a quad core desktop Core i5-2320 3.00 GHz with 6 GB RAM...

Re: Generating SQL Geometry on the Fly in View or Query Layer

MeleKoneya — Fri, 22 Nov 2013 19:33:15 GMT

I was able to create the Geometry in a view as shown below and then use it as a Query Layer in ArcMap

Select [BlueStakeWOId], Geometry::STPointFromText('POINT (' + CAST([Longitude] as varchar(32)) + ' ' + CAST([Latitude] as varchar(32)) + ')', 4326) as shape
from DBSVR.WaterWO.gis.OpenTickets

There are only about 130 points in this table, so I will have to try with larger datasets and see how it performs

Mele

Re: Generating SQL Geometry on the Fly in View or Query Layer

VinceAngelo — Fri, 22 Nov 2013 23:09:00 GMT

The geometry::Point constructor is likely to be much more efficient than casting to get
WKT formatted text.

It's difficult to make 10k rows render slowly, but easy with 100k rows, and trivial with 1m.

- V

Re: Generating SQL Geometry on the Fly in View or Query Layer

MarcoBoeringa — Sat, 23 Nov 2013 11:04:37 GMT

It's difficult to make 10k rows render slowly, but easy with 100k rows, and trivial with 1m.

Just did another small test based on OpenStreetMap data. The original line layer with 61188 records stored in SQL Server Geometry, draws in less than 2 seconds in ArcMap at full extent.

A dynamic SQL view based on this layer though, using the Shape.STBuffer() command, takes 75 seconds to draw at full extent. Unsurprisingly, this confirms your remarks.

The lack of spatial index and associated full table scans, also means that zooming in on a part (e.g. 1/10th), still requires some 45 seconds to render. Making a selection of all features in that same section, takes 60 seconds...

So I guess an upper limit of about 10k records, is realistic for this kind of operation where SQL geometry is generated on the fly (please note this is not similar to a "normal" spatial view, where you just make a selection of existing shapes in a database view, and an existing spatial index on the original table can be used). Anything above that will be useless (Core i5 3 GHz).

I now also exported the generated buffers to a polygon Feature Class in the same database. That layer draws in just 4-5 seconds...