0

I am trying to understand how to create a database view that shows a 150 foot buffer around each point in a table. The table of points changes pretty frequently and I would like to use a view instead of just buffering the points and having to update the polygon layer all the time.

I am using ArcGIS 10.2.1 and my SDE database is running on SQL Server 2012.

I have some experience building view layers in a PostgreSQL environment but that was a while ago and I'm struggling to get my head around doing things in this new space.

I started out reading the documentation for the 'Create Database View' tool but am having trouble figuring out how to put together the SQL statement. Where do I put the .ST_Buffer() part of the statement? I'm confused because .ST_Buffer() only accepts the distance as an argument, and it has a dot prefix, which makes me think that I need to use it as a function. I can't seem to find any references to doing this anywhere on the web. Is it even possible to do this in the software environment I'm using?

This is the best stab I have taken:

SELECT OBJECTID, LocationID, shape.st_buffer(47.5) FROM gisdb.DCGIS.TransformerBank

When I run that the tool errors out and reports:

ERROR 999999: Error executing function. [[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find either column "shape" or the user-defined function or aggregate "shape.st_buffer", or the name is ambiguous.] DBMS table not found Failed to execute (CreateDatabaseView).

Improve this question
7
  • For me the first hit with "sde st_buffer" was webhelp.esri.com/arcgisserver/9.3/java//index.htm#geodatabases/…. The third hit looks better resources.arcgis.com/en/help/main/10.2/index.html#//…
    – user30184
    Commented Jun 10, 2016 at 18:40
  • That page only references postgres and oracle - not SQL server. If I was back in a postgres world I'd have this nailed down. When I search for "ST_Buffer SDE SQL Server" the most relevant page is desktop.arcgis.com/en/arcmap/10.3/manage-data/… -- But even that doesn't make it clear how to form the SQL statement in the context of the 'Create Database View' page.
    – Kevin
    Commented Jun 10, 2016 at 18:42
  • I apologize. However, I thought that the idea of SDE was to make the database backends abstract. Have you tried that syntax with SQL Server? I mean SELECT OBJECTID, LocationID, sde.st_buffer(shape, 47.5) FROM gisdb.DCGIS.TransformerBank if "shape" is the geometry column?
    – user30184
    Commented Jun 10, 2016 at 18:44
  • The functions aren't the same. In the postgres/oracle documentation the function accepts two arguments - the geometry and the distance. In the SQL documentation the function only accepts ONE - distance. That's a major part of my confusion - I don't understand how to reference the geometry in the SQL space. That's why i tried 'shape.st_buffer(47.5)' in my example code. (47.5 because the documentation says the function assumes the units are meters).
    – Kevin
    Commented Jun 10, 2016 at 18:48
  • 1
    The ST_Buffer function is a Microsoft product. It has only tangential relation to the Esri SDE.* function. There are no Esri geometry functions for SQL-Server because the database doesn't support them. As such this question is very nearly a pure database issue. I would recommend creating views using the native database client.
    – Vince
    Commented Jun 10, 2016 at 19:56

1 Answer 1

Reset to default
2

I believe the error stems from the incorrect syntax for SQL Server's spatial buffer function. SQL Server's spatial functions are case sensitive and don't use the underscore like Esri's ST_Geometry, Oracle spatial or PostGIS. The query should look like:

SELECT OBJECTID, LocationID, shape.STBuffer(47.5) FROM gisdb.DCGIS.TransformerBank

I tested a similar query in the Create Database View tool in ArcGIS 10.2.1 where the tool runs successfully and the view renders in ArcMap.

SQL Server - STBuffer

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.