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
USE [GeoTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ProjectQuery]
@latitude float,
@longitude float,
@BufferSize float
AS
BEGIN
SET NOCOUNT ON;
Declare @Point geography
SET @Point = geography::Point(@latitude,@longitude, 4326)
Declare @SearchArea geometry
Declare @G geography
Set @G = @Point.STBuffer(@BufferSize *.3048)
Set @SearchArea = geometry::STGeomFromWKB((Select @G.STAsBinary()), 4326)
Select
Distinct ProjectNumber, ProjectName,
CONCAT(ProjectDescription,' ',ProjectDesc2Part) as Description,
ProjectType,Status,CompletionDate
From
PROJECTS
Where
Shape.STIntersects(@SearchArea) = 1
END
This procedure returns the Well Known Text geometries for any Project:
USE [GeoTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ProjectLimit]
@pnumber nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Select
ObjectID,
Shape.STGeometryType() as GeometryType,
Shape.STAsText() as WKT,
'ProjectNumber: ' + ProjectNumber as Title,
concat(ProjectDescription,' ',ProjectDesc2Part) as Description
From
PROJECTS
Where
ProjectNumber = @pnumber
END
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
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
'Options:
' 0 - Draw Project Boundary on Drawing Layer
' 1 - Draw Project Boundary based on user input of Project Number
'Declare the global script variables
Dim Output As String = "" 'The JavaScript response sent back to the Map API
Dim options As Integer = context.Request.Params("opt")
Dim pnum As String = context.Request.Params("pnum")
'Set up a connection to SQL server
Dim conString = ConfigurationManager.ConnectionStrings("SpatialDB").ConnectionString
Dim myConn = New SqlConnection(conString)
'Dim myConn = New SqlConnection("Data Source=HQ-BW0G5V1\SQLExpress12;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False; database=GeoTest")
'Open the connection
myConn.Open()
'Define the stored procedure to execute
Dim myQuery As String
Select Case options
Case 0
myQuery = "dbo.usp_Projectlimit"
Case Else
myQuery = "dbo.usp_Projectlimit" 'Default
End Select
Dim myCMD As New SqlCommand(myQuery, myConn)
myCMD.CommandType = Data.CommandType.StoredProcedure
If options >= 0 Then 'Need to pass user clicked lat long to database
myCMD.Parameters.Add("@pnumber", Data.SqlDbType.NVarChar)
myCMD.Parameters("@pnumber").Value = context.Request.Params("pnum")
End If
'Create a reader for the result set
Dim myReader As SqlDataReader = myCMD.ExecuteReader()
'Go through the results
While myReader.Read()
Output += myReader("WKT") + "^" + myReader("GeometryType") + "^" + myReader("Title") + "^" + myReader("Description") + "@"
End While
'Close the reader
myReader.Close()
'Close Connection
myConn.Close()
'return the constructed string to Javascript
context.Response.Write(Output)
End Sub
Javascript Client that parses the returned recordset string from SQL Server via Web handler above:
function buildRecord(record, splitChar) {
splitChar = splitChar || "^";
record = record.split(splitChar);
var ObjRec = {};
ObjRec.wkt = record[0];
ObjRec.type = record[1];
ObjRec.title = record[2];
ObjRec.description = record[3];
var numField = record.length;
switch (numField){
case 5:
ObjRec.attr = record[4];
break;
case 6:
ObjRec.attr = record[4];
ObjRec.attr1 = record[5];
break;
}
return ObjRec;
}
