Friday, February 5, 2010

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.

CREATE PROCEDURE [sde].[IntersectGrid]

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

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

1 comment:

Michele said...

Good God you're a nerd. xoxoxo