nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Obtain Polygon Geometry Points for SQL STIntersect

WalterDziuba1

Morning,

I have a polygon geometry that I need to read the points and then be able to feed those values into a SQL STIntersect function.

For example

DECLARE @Geom geometry::polygon()

Then how do I read(parse) the point values from the polygon?

Walter

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

JoshuaBixby

Can you elaborate on what you are trying to accomplish? On first read, decomposing a polygon into its vertices to find intersections doesn't seem very efficient, but I can't say for sure because I am not sure what your data looks like and what answers you are after.

TedKowal

I am assuming that you are using SQL Server to perform a spatial intersect. I have a "Bing" Map application in which a user clicks on a road. That click is turned into a point. This point is then passed through a web handler (in my case a VB ASHX file) to SQL Server. The lat and long is passed. SQL Server then creates a buffered area around that point and intersects the buffer against a Project polygon layer. The intersecting geometries and attribute data is then passed back to the web handler as a recordset stream. The geometries in my case are expressed as Well Known Text. The web handler then reads the recordset and processes the data (in my case converting the recordset into a formated string to send to my client javascript (which has no natural capacity to read a recordset). Then in javascript I parse the formated string back into psuedo records I can display on a Bing map.

Although there are a lot of moving parts; this may give you some ideas to try --

SQL Server Procedure

<SPAN class="keyword token">USE</SPAN> <SPAN class="punctuation token">[</SPAN>GeoTest<SPAN class="punctuation token">]</SPAN>
GO
<SPAN class="comment token">/****** Object:&nbsp; StoredProcedure [dbo].[usp_ProjectQuery]&nbsp;&nbsp;&nbsp; Script Date: 8/17/2017 10:21:55 AM ******/</SPAN>
<SPAN class="keyword token">SET</SPAN> ANSI_NULLS <SPAN class="keyword token">ON</SPAN>
GO
<SPAN class="keyword token">SET</SPAN> QUOTED_IDENTIFIER <SPAN class="keyword token">ON</SPAN>
GO
<SPAN class="comment token">-- =============================================</SPAN>
<SPAN class="comment token">-- Author:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Ted Kowal></SPAN>
<SPAN class="comment token">-- Create date: <7/2013></SPAN>
<SPAN class="comment token">-- Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Return the Projects found withing a user defined</SPAN>
<SPAN class="comment token">--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; radius click on the map (lat/long)></SPAN>
<SPAN class="comment token">-- =============================================</SPAN>
<SPAN class="keyword token">ALTER</SPAN> <SPAN class="keyword token">PROCEDURE</SPAN> <SPAN class="punctuation token">[</SPAN>dbo<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>usp_ProjectQuery<SPAN class="punctuation token">]</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- Add the parameters for the stored procedure here</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="variable token">@latitude</SPAN> <SPAN class="keyword token">float</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="variable token">@longitude</SPAN> <SPAN class="keyword token">float</SPAN><SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="variable token">@BufferSize</SPAN> <SPAN class="keyword token">float</SPAN>
<SPAN class="keyword token">AS</SPAN>
<SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- SET NOCOUNT ON added to prevent extra result sets from</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- interfering with SELECT statements.</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">SET</SPAN> NOCOUNT <SPAN class="keyword token">ON</SPAN><SPAN class="punctuation token">;</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">Declare</SPAN> <SPAN class="variable token">@Point</SPAN> geography
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">SET</SPAN> <SPAN class="variable token">@Point</SPAN> <SPAN class="operator token">=</SPAN> geography::<SPAN class="keyword token">Point</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="variable token">@latitude</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="variable token">@longitude</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">4326</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- Use STBuffer() to greate a buffer around the point to BufferSize in Feet</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Declare</SPAN> <SPAN class="variable token">@SearchArea</SPAN> <SPAN class="keyword token">geometry</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Declare</SPAN> <SPAN class="variable token">@G</SPAN> geography

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- This should be done usine Geography (Have not been able to get it working using</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Geometry)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Set</SPAN> <SPAN class="variable token">@G</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@Point.STBuffer</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="variable token">@BufferSize</SPAN> <SPAN class="operator token">*</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="number token">3048</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- Convert Search Area to Geometry from Geography</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Set</SPAN> <SPAN class="variable token">@SearchArea</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">geometry</SPAN>::STGeomFromWKB<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">Select</SPAN> <SPAN class="variable token">@G.STAsBinary</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">4326</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- Select any lights that intersect the search area</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Select</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">Distinct</SPAN> ProjectNumber<SPAN class="punctuation token">,</SPAN> ProjectName<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONCAT<SPAN class="punctuation token">(</SPAN>ProjectDescription<SPAN class="punctuation token">,</SPAN><SPAN class="string token">' '</SPAN><SPAN class="punctuation token">,</SPAN>ProjectDesc2Part<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> Description<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProjectType<SPAN class="punctuation token">,</SPAN><SPAN class="keyword token">Status</SPAN><SPAN class="punctuation token">,</SPAN>CompletionDate
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">From</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PROJECTS
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Where</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Shape<SPAN class="punctuation token">.</SPAN>STIntersects<SPAN class="punctuation token">(</SPAN><SPAN class="variable token">@SearchArea</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>

<SPAN class="keyword token">END</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This procedure returns the Well Known Text geometries for any Project:

<SPAN class="keyword token">USE</SPAN> <SPAN class="punctuation token">[</SPAN>GeoTest<SPAN class="punctuation token">]</SPAN>
GO
<SPAN class="comment token">/****** Object:&nbsp; StoredProcedure [dbo].[usp_ProjectLimit]&nbsp;&nbsp;&nbsp; Script Date: 8/17/2017 10:40:02 AM ******/</SPAN>
<SPAN class="keyword token">SET</SPAN> ANSI_NULLS <SPAN class="keyword token">ON</SPAN>
GO
<SPAN class="keyword token">SET</SPAN> QUOTED_IDENTIFIER <SPAN class="keyword token">ON</SPAN>
GO
<SPAN class="comment token">-- =============================================</SPAN>
<SPAN class="comment token">-- Author:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Ted Kowal></SPAN>
<SPAN class="comment token">-- Create date: <6/2013></SPAN>
<SPAN class="comment token">-- Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Return the Geometry for a specific Project Limit></SPAN>
<SPAN class="comment token">-- =============================================</SPAN>
<SPAN class="keyword token">ALTER</SPAN> <SPAN class="keyword token">PROCEDURE</SPAN> <SPAN class="punctuation token">[</SPAN>dbo<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">[</SPAN>usp_ProjectLimit<SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- Add the parameters for the stored procedure here</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="variable token">@pnumber</SPAN> nvarchar<SPAN class="punctuation token">(</SPAN><SPAN class="number token">50</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<SPAN class="keyword token">AS</SPAN>
<SPAN class="keyword token">BEGIN</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- SET NOCOUNT ON added to prevent extra result sets from</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="comment token">-- interfering with SELECT statements.</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">SET</SPAN> NOCOUNT <SPAN class="keyword token">ON</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Select</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ObjectID<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Shape<SPAN class="punctuation token">.</SPAN>STGeometryType<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> GeometryType<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Shape<SPAN class="punctuation token">.</SPAN>STAsText<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> WKT<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="string token">'ProjectNumber: '</SPAN> <SPAN class="operator token">+</SPAN> ProjectNumber&nbsp; <SPAN class="keyword token">as</SPAN> Title<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; concat<SPAN class="punctuation token">(</SPAN>ProjectDescription<SPAN class="punctuation token">,</SPAN><SPAN class="string token">' '</SPAN><SPAN class="punctuation token">,</SPAN>ProjectDesc2Part<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> Description
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">From</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PROJECTS
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<SPAN class="keyword token">Where</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProjectNumber <SPAN class="operator token">=</SPAN> <SPAN class="variable token">@pnumber</SPAN>
<SPAN class="keyword token">END</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Web handler ashx file

<%@ WebHandler Language="VB" Class="MDXProjects" %>

Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text

Public Class MDXProjects : Implements IHttpHandler

&nbsp;&nbsp;&nbsp; Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Options:
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 - Draw Project Boundary on Drawing Layer
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 - Draw Project Boundary based on user input of Project Number


&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Declare the global script variables
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim Output As String = "" 'The JavaScript response sent back to the Map API

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim options As Integer = context.Request.Params("opt")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim pnum As String = context.Request.Params("pnum")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Set up a connection to SQL server
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim conString = ConfigurationManager.ConnectionStrings("SpatialDB").ConnectionString

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim myConn = New SqlConnection(conString)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Dim myConn = New SqlConnection("Data Source=HQ-BW0G5V1\SQLExpress12;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False; database=GeoTest")

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Open the connection
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myConn.Open()
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Define the stored procedure to execute
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim myQuery As String
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Select Case options
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case 0
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myQuery = "dbo.usp_Projectlimit"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case Else
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myQuery = "dbo.usp_Projectlimit" 'Default
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End Select

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim myCMD As New SqlCommand(myQuery, myConn)

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myCMD.CommandType = Data.CommandType.StoredProcedure

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If options >= 0 Then&nbsp;&nbsp; 'Need to pass user clicked lat long to database
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myCMD.Parameters.Add("@pnumber", Data.SqlDbType.NVarChar)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myCMD.Parameters("@pnumber").Value = context.Request.Params("pnum")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Create a reader for the result set
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim myReader As SqlDataReader = myCMD.ExecuteReader()

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Go through the results
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; While myReader.Read()
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output += myReader("WKT") + "^" + myReader("GeometryType") + "^" + myReader("Title") + "^" + myReader("Description") + "@"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End While
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Close the reader
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myReader.Close()
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Close Connection
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myConn.Close()


&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'return the constructed string to Javascript
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; context.Response.Write(Output)
&nbsp;&nbsp;&nbsp; End Sub<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Javascript Client that parses the returned recordset string from SQL Server via Web handler above:

<SPAN class="keyword token">function</SPAN> <SPAN class="token function">buildRecord</SPAN><SPAN class="punctuation token">(</SPAN>record<SPAN class="punctuation token">,</SPAN> splitChar<SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// <summary></SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// parses the list into various fields</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// </summary></SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// <param name="record" type="list">single List item of the xmlHttp response</param></SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// <param name="splitChar" type="string"> Character on which to seperate the various fields</param></SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">/// <returns type="User Variable">Returns a Class/User type variable containg the various fields</returns></SPAN>
&nbsp;&nbsp;&nbsp; splitChar <SPAN class="operator token">=</SPAN> splitChar <SPAN class="operator token">||</SPAN> <SPAN class="string token">"^"</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; record <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">.</SPAN><SPAN class="token function">split</SPAN><SPAN class="punctuation token">(</SPAN>splitChar<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">var</SPAN> ObjRec <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>wkt <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>type <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>title <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>description <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token">//optional classification objects if any</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">var</SPAN> numField <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">.</SPAN>length<SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">switch</SPAN> <SPAN class="punctuation token">(</SPAN>numField<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">{</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">case</SPAN> <SPAN class="number token">5</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>attr <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">break</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">case</SPAN> <SPAN class="number token">6</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>attr <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ObjRec<SPAN class="punctuation token">.</SPAN>attr1 <SPAN class="operator token">=</SPAN> record<SPAN class="punctuation token">[</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">break</SPAN><SPAN class="punctuation token">;</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">}</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">return</SPAN> ObjRec<SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

WalterDziuba1

Afternoon,

Initially, my client draws a polygon around the monitoring stations that he's interested in. That polygon geometry is what I'm trying to use the points that make up this polygon and use these points for a STIntersect function.

DECLARE @geom geometry
set @geom = geometry::STPolyFromText('POLYGON ((-8613551.46522 5789056.62846,-8613290.62390 5789056.62846, -8613275.33651 5788811.07446,-8613626.94680 5788823.49551, -8613551.46522 5789056.62846))', 102100)
select @geom

The numbers above were keyed in, just to test out the logic. But these are the values that I need to read from the actual polygon itself. All the samples I've seen using the STIntersect command all have values within the parentheses, but I'm trying to read them on the fly.

Or this there another method that I have overlooked?

Can I not just read the polygon itself?

Walter

TedKowal

Given what you told and you are not using SDE (how your are accessing sql server?), your methodology is correct. To make it less painful you could create parameter string to pass the wkt (well known text string). There are tools both in Arcgis and opensource which would convert various geometries to WKT string.. OGR2OGR one of which comes to mind. I have in the past used an opensource library called Sharpmap to convert wkt/b from/to esri compatible formats/sql server. Using this library, you could make your queries more efficient by using the well known binaries saving the overhead of converting from binary - to strings - and back again.

Or you could code with arc-objects and use esri built in object to send to sql server.... for example:

SqlGeometry sqlGeo = (SqlGeometry)myReader["SHAPE"];
IGeometryFactory factory = new GeometryEnvironmentClass();
IGeometry geom = new PolygonClass();
int countout;
factory.CreateGeometryFromWkbVariant(sqlGeo.STAsBinary().Value, out geom, out countout);

WalterDziuba1

Ted,

Our SDE is in SQL Server. All of our map/feature services are in SQL, so I was attempting to use the SQL STIntersect function to use the polygon and determine which monitoring sites fell inside that polygon.

I don't have my exposure to using opensource tools to accomplish these tasks. I was hoping that SQL itself had the ability to read this polygon and use it in the STIntersect function.

Walter

TedKowal

If you are using SQL Server then there is no other way....... Other database Oracle, postGIS .... can use ST_Geometry which will accept a polygon as you wish to send it.

https://community.esri.com/thread/186602-sde-with-sql-server

WalterDziuba1

Ted,

Appreciate your feedback.

Walter

Nimm an der Diskussion teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.