sql syntax arcSDE query layer points within polygon

[timdunlevie]

Hi all,

We have a Microsoft sql server which our ArcSDE is located on.

In the database are a collection of feature classes.

I want to add a query layer using 2 feature classes.

It's a simple query involving a points FC & a polygon FC.

I want to query points which are within a polygon based on an attribute.

for example:

select * from FC_Points, FC_Polygon

where FC_Points are within FC_Polygon and FC_Points.ID = FC_Polygon.ID

could someone point me in the right direction to write the syntax?

Many thanks

[VinceAngelo]

First off, there's no such thing as an "ArcSDE Query Layer" -- Query Layers are based on the assumption that you do not have an enterprise geodatabase (ArcSDE no longer exists), and use the native spatial type capability of the database to perform queries.  The exception here is that Query Layers understand how to interact with the SDE.ST_GEOMETRY datatype (on PostgreSQL, Oracle, DB2, and Informix, where that type exists)..

Therefore, your query on SQL Server will only work if you are using one of the Microsoft spatial types (Geometry or Geography) for geometry storage, and will only work with the spatial functions defined to support those types.  The Microsoft documentation is a good place to start looking for the within operator​ and proper syntax for using it.  Be sure to organize your query so the polygons are used to find points, since that will require fewer comparisons than looking for points contained by polygons.

- V

[timdunlevie]

Hi Vince,

Righto – thanks for the info….might not have structured that post correctly…

What I was attempting to do is “add query layer” from our enterprise geodatabase via ArcMap.

And was hoping to add a fairly detailed SQL expression to get the result I wanted and wanted some help in the sql commands that would work.

If you think it’s best to do this on the actual Microsoft sql database then I’ll do it that way.

Thanks,

Tim

[VinceAngelo]

You can make the Query Layer SQL expression as complex as you like, but you may be better off prototyping the SQL using Management Studio first, since it can give you more syntax information.

- V

[timdunlevie]

Gotcha…thanks.

Regards,

Tim