topic Obtain Polygon Geometry Points for SQL STIntersect in ArcGIS Enterprise Questions

Obtain Polygon Geometry Points for SQL STIntersect

WalterDziuba1 — Thu, 17 Aug 2017 12:42:26 GMT

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

Re: Obtain Polygon Geometry Points for SQL STIntersect

JoshuaBixby — Thu, 17 Aug 2017 14:25:01 GMT

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.

Re: Obtain Polygon Geometry Points for SQL STIntersect

TedKowal — Sat, 11 Dec 2021 18:36:27 GMT

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

    Declare @Point geography
     SET @Point = geography::Point(@latitude,@longitude, 4326)

     -- Use STBuffer() to greate a buffer around the point to BufferSize in Feet

     Declare @SearchArea geometry
     Declare @G geography

     -- This should be done usine Geography (Have not been able to get it working using
     --                                      Geometry)
     Set @G = @Point.STBuffer(@BufferSize *.3048)

     -- Convert Search Area to Geometry from Geography
     Set @SearchArea = geometry::STGeomFromWKB((Select @G.STAsBinary()), 4326)

     -- Select any lights that intersect the search area
     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
/****** Object:  StoredProcedure [dbo].[usp_ProjectLimit]    Script Date: 8/17/2017 10:40:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:          <Ted Kowal>
-- Create date: <6/2013>
-- Description:     <Return the Geometry for a specific Project Limit>
-- =============================================
ALTER PROCEDURE [dbo].[usp_ProjectLimit]
     -- Add the parameters for the stored procedure here
     @pnumber nvarchar(50)
     
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     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) {
    /// <summary>
    /// parses the list into various fields
    /// </summary>
    /// <param name="record" type="list">single List item of the xmlHttp response</param>
    /// <param name="splitChar" type="string"> Character on which to seperate the various fields</param>
    /// <returns type="User Variable">Returns a Class/User type variable containg the various fields</returns>
    splitChar = splitChar || "^";
    record = record.split(splitChar);
    var ObjRec = {};
    ObjRec.wkt = record[0];
    ObjRec.type = record[1];
    ObjRec.title = record[2];
    ObjRec.description = record[3];

    //optional classification objects if any
    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;
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Obtain Polygon Geometry Points for SQL STIntersect

WalterDziuba1 — Thu, 17 Aug 2017 16:48:14 GMT

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

Re: Obtain Polygon Geometry Points for SQL STIntersect

TedKowal — Thu, 17 Aug 2017 17:33:32 GMT

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);

Re: Obtain Polygon Geometry Points for SQL STIntersect

WalterDziuba1 — Thu, 17 Aug 2017 18:31:33 GMT

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

Re: Obtain Polygon Geometry Points for SQL STIntersect

TedKowal — Thu, 17 Aug 2017 19:06:54 GMT

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

Re: Obtain Polygon Geometry Points for SQL STIntersect

WalterDziuba1 — Fri, 18 Aug 2017 18:58:43 GMT

Ted,

Appreciate your feedback.

Walter