Point-In-Polygon Stored Procedure: Using Parameters and MIsmatched Data Types

Question

I'm trying to write a stored procedure that will accept a long/lat, locate it in a polygon layer, and return an attribute value for the polygon.  I have been able to write T-SQL which does the trick, but I can get a stored procedure that will work correctly.

I've narrowed down the culprit to this inconsistency.  In T-SQL, this code:

 

 

select geography::Parse('POINT(-122.362 47.533)')

gives me a different result from:

declare @longitude numeric=-122.362
declare @latitude numeric= 47.533
select geography::Parse('POINT(' + CAST(@longitude AS VARCHAR) + ' ' + CAST(@latitude AS VARCHAR) + ')')

I'm not sure how to overcome this issue.  I thought that local parameters would behave well, but it looks liek CASTing isn't the answer.  Do I need to do a workaround for this?  Please help!

                                                                             ...pete..

Answer 1

Hi Pete,

The inconsistancy comes from not specifying a precision and scale in your numeric declaration. Without a precision and scale numeric defaults to numeric(18,0). This should do the trick.

declare @longitude numeric(6,3) =-122.362
declare @latitude numeric(6,3) = 47.533
select geography::Parse('POINT(' + CAST(@longitude AS VARCHAR(10)) + ' ' + CAST(@latitude AS VARCHAR(10)) + ')').ToString()

The geography and geometry data types are defined as float in the database itelf, so you may see some other artifacts of conversion of numeric (an exact numeric data type) to float (an inexact numeric data type), but that should take are of your current issue.

Why not just use select geography::Point(@latitude, @longitude), it's shorter than either of those.

Cheers,
Bob Beauchemin
SQLskills

Answer 2

Hello, Bob!

My sincere apologies for not getting back to you sooner!  Thank you for providing me a solution to my problem, your fix really "did the trick"!

One comment that I wanted to make was that I wanted to make sure that the representation of data in the ArcSDE environment which was my source matched to the SS08 environment.  My data started off in ArcSDE on the SQL Server as State Plane, so I had to unproject it, turn it into a shapefile, use the great little Cumberland utility to make it an SS08 spatial layer, and reference it using SS08 Spatial.  (This whole exercise was because ArcGIS Server 9.3 didn't implement the WFS spec for GetFeature well at all!)

When I did a comparison between ArcGIS Desktop's view and what SS08 was seeing, I was off by 150' east-west and thus the wrong polygon would be chosen.  I then realized that I needed to change the ArcGIS viewer to show more than 3 digits behind the decimal in defining the point.  It was then that I discovered that I would get better results if I coded:

declare @longitude numeric(9,6) =-122.362
declare @latitude numeric(9,6) = 47.533
select geography::Point(@latitude, @longitude)

Sure enough, by raising the precision, I now get no difference between the two points in picking the right polygon. 

One more comment is that I really appreciate the shorthand, such as geography:Point, since it is more direct.  I didn't see too many occurrences of this shorthand example on-line.  I'm just getting into SQL Spatial and I'm thinking of getting the Aitchison book as a start.  Your blog is also great - thank you again for your support!

                                                                                                            ...pete...