Generating SQL Geometry on the Fly in View or Query Layer

Question

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

MarcoBoeringa · Answer

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

VinceAngelo · Answer

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

MeleKoneya · Answer

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