Pragmatic Geographer

SOFTWARE GEOGRAPHY ECONOMICS HISTORY

Fun With SQL Spatial

This is old hat by now, but I love how much cruft can be killed by outright avoiding older APIs.
A unique location number needed to be generated for an engineering design tool. A vendor contact sent an example to work off that was about 400 lines of C# code. This little stored procedure replaced basically all of it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE [sde].[IntersectGrid]
@x FLOAT, @y FLOAT
AS SET NOCOUNT ON
BEGIN

DECLARE @g geometry;
DECLARE @grid int;
SET @g = geometry::STPointFromText('POINT ('+str(@x)+' '+ STR(@y)+')', 2);
SET @grid = (
  SELECT TOP 1 [areaName]
  FROM GridTable
  Where @g.STWithin(Shape) <> 0
 )
return @grid
END

How do you make this 400 lines? Easy, use the ArcObject API to do the intersect. Instantiating dozens of objects, checking out/in licenses, and using reflection to read a config file (not sure why they didn’t just use AppSettings) adds up fast. I could probably even do it with even less effort using Shapely, but no one else is really familiar with Python in this situation (vendors or coworkers).

Comments