sql server spatial query index

                

    

                                

<cffunction name="get_tblGeocoding" access="remote" returnType="any" returnFormat="json" output="false">

                                    

<cfargument name="bbox" required="false">

                                    <cfargument name="geotype" required="false">

                                    <cfargument name="sid" required="false">

<cfargument name="limit" required="false">

                                    <cfquery name="getJSON" datasource="#request.sqlconn#">

                                 SELECT

<cfif isDefined("limit")>

                                            top #limit#

</cfif>

                                             OBJECTID

                                                ,Location_No

                                                ,Geotype

                                                

                                      

,Shape.STSrid as srid

                                                ,Shape.STGeometryType() as spatialType

                                                ,Shape.STAsText() as wkt

                                            FROM tblGeocoding

<cfif isDefined("bbox")>

                                        

where Shape.STIntersects(geometry::STGeomFromText('#bbox#', 2229)) = 1

</cfif>

<cfif isDefined("geotype")>

                                            where Geotype = '#geotype#'

</cfif>

<cfif isDefined("sid")>

                                            where Location_No = #sid#

</cfif>

                                    </cfquery>

                                    <cfreturn getJSON>

                                    

                                </cffunction>

                                <cffunction name="getGeocoding" access="remote" returnType="any" returnFormat="json" output="false">

                                    

<cfargument name="bbox" required="false">

                                    

                                    <cfargument name="sid" required="false">

<cfargument name="limit" required="false">

                                    <cfquery name="getJSON" datasource="#request.sqlconn#">

                                     SELECT

<cfif isDefined("limit")>

                                            top #limit#

</cfif>

                                             OBJECTID

                                                ,Location_No

                                                

                                                

                                                

                                                    

                                                

                                                ,Name

                                                ,Address

                                                ,Reported_By

                                                ,Council_District

                                                ,Construction_Start_Date

                                                ,Construction_Completed_Date

                                                ,Status

                                                ,Photos

                                                

                                      

,Shape.STSrid as srid

                                                ,Shape.STGeometryType() as spatialType

                                                

                                                ,Shape.STAsText() as wkt

                                            FROM vwGeocoding

<cfif isDefined("bbox")>

                                        

where Shape.STIntersects(geometry::STGeomFromText('#bbox#', 2229)) = 1

</cfif>

<cfif isDefined("sid")>

                                            where Location_No = #sid#

</cfif>

                                    </cfquery>

                                    <cfreturn getJSON>

                                    

                                </cffunction>