[GIS] How to force sql server to use the spatial index

sql server

I'm beginner with SQL Server (2012). I created a spatial index, and I'm trying to force SQL Server to use that index to solve the query.

Here's what I have:

DECLARE @g geography

SET @g = geography::STPointFromText('POINT(-79.25161 43.735965)', 4326)

SELECT *

FROM CUSTOMER

WITH(INDEX(idx_Geography))

where HomeGeog.STDistance(@g)

The spatial index (idex_Geography) is created for HomeGeog.

I'm getting this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

I'm not sure where the error is…

Thanks for you help!

Best Answer

The issue is with your use of STDistance rather than the index. This function calculates the distance between two points so you need to replace HomeGeog with a feature rather than the name of the table.

E.g. @g.STDistance(@g) should work (but always return 0)

What are you trying to return from your query?

See http://msdn.microsoft.com/en-us/library/bb933952.aspx for more details on how to use the STDistance function.

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

There seems to be a lack of a clear overview on the OGR Virtual Format help page. The phrase it can "provide an anchor file for access to non-file oriented datasources" seems to imply any connection type can be used, but I'm not sure this is the case.

Its original function was to allow spatial data to be created from non-spatial sources, for example by combining an X and Y column in a database table to create point features.

However there is no reason why a spatial database can't also be used for this purpose, but you should then use a standard ODBC connection rather than the MSSQL driver.

To do this create a DSN (Data Source Name) in Windows. Then use the ODBC connection and the DSN in the virtual layer configuration. So if your DSN is named OSVectorMap:

<OGRVRTDataSource>
  <OGRVRTLayer name="AASQLlayer">
    <SrcDataSource>ODBC:username/password@OSVectorMap</SrcDataSource>
    <SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryType>wkbPolygon</GeometryType>
  </OGRVRTLayer>
</OGRVRTDataSource>

There are a few more details on creation of Virtual Layers in the MapServer wiki and Virtual Spatial Data page.

Great blog post by the way. I wouldn't worry too much about using Python 3.x - nearly all the useful GIS libraries are still in 2.x And the SQL Server Geometry and Geography types are great. The only downsides are that SQL Server unfortunately seems a second class citizen in the opensource world in terms of blog posts, and integration with other tools.

[GIS] How to create and use a SQL Server spatial view in ArcMap

Response from the ArcGIS forum post:

"the problem is that your view does not have something ArcMap recognizes as an ObjectID field. ArcMap must have a field that it can use as a unique identifier. It is a little tricky to generate a field using something like a RowID in SQL Server when you are aggregating a single column, but you can do something inelegant like this:

SELECT 1 as ObjectID, GEOMETRY::UnionAggregate(shape) FROM CENSUS_TRACTS_2010_2877 WHERE PCT_ASIAN > 5

This will obviously only work in this case when your view is resulting in a single record.

-Shannon "

http://forums.arcgis.com/threads/87428-Spatial-views-from-SQL-Server-2012?p=309422&posted=1#post309422

UPDATE:

The best way to give ArcMap the ability to use a spatial view is to use a ROW_NUMBER() function to generate OBJECTID's on the features returned in the view.

A good thread that describes this process can be found here:

How to set up an ArcMap Relate with a table from MS SQL Server 2012

Best Answer

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

[GIS] How to create and use a SQL Server spatial view in ArcMap

Related Question