SQL 2008 R2 Geometry Question

Question

Post reply

SQL 2008 R2 Geometry Question

jgabler

SSC Enthusiast

Points: 136
More actions
January 31, 2013 at 4:24 pm

#273651

Trying to take a Lat/Long value and determine if it's within a polygon that's in a table (we're using ESRI so we're working in the GIS_Vector database). Eventually, I need to check it against a multi-polygon in the database but keeping it simple until I get this working since I'm a newb with GIS data.
I can get this code to work with a hard-coded polygon (yes, it's a box we're using for testing):
DECLARE @x float;
DECLARE @y float;
SET @x = -122.33317565917969;
SET @y = 42.009316762288414;
DECLARE @g geometry;
SET @g = geometry::Point(@x,@y,0);
DECLARE @h geometry;
SET @h = geometry::STGeomFromText('POLYGON((-125.37123023799995 60.111279971000044, -125.37123023799995 40.361753622000037, -50.418208553999932 39.647915321000028, -50.418208553999932 60.349226071000032, -125.37123023799995 60.111279971000044))', 0);
SELECT @g.STWithin(@h);
But I'm not able to make it work when trying to use the polygon in the database (to replace @h).
Our GIS person has created a table with a geometry field (column is titled "Shape") using the coordinates from @h and titled the table "Box". But I can't simply query the table to get the "Shape" field coordinates into @h.
This does not work (I can guess sort of why, but I don't know the syntax to make it work):
SET @h = (SELECT Shape FROM GIS_VECTOR.GIS.Box)
Anyone know what the proper syntax is (assuming I can actually do this)?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Wouldn't it be the normal:

SELECT @h = Shape FROM GIS_VECTOR.GIS.Box

And then again, I might be wrong ...
David Webb

jgabler SSC Enthusiast Points: 136 More actions · Answer 2

David Webb-200187 (1/31/2013)
Wouldn't it be the normal:
SELECT @h = Shape FROM GIS_VECTOR.GIS.Box

You'd think that would work, but it doesn't.

EDIT: Just to clarify. SQL does not object to the syntax, but it returns NULL instead of 1 (which it does if the polygon is hard coded).

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Hmmmmm... This return 1 for me:

create table box (

shape geometry null)

insert box(shape) values(geometry::STGeomFromText('POLYGON((-125.37123023799995 60.111279971000044,

-125.37123023799995 40.361753622000037, -50.418208553999932 39.647915321000028,

-50.418208553999932 60.349226071000032, -125.37123023799995 60.111279971000044))', 0))

DECLARE @x float;

DECLARE @y float;

DECLARE @h geometry;

SET @x = -122.33317565917969;

SET @y = 42.009316762288414;

DECLARE @g geometry;

SET @g = geometry::Point(@x,@y,0);

SELECT @h = Shape FROM Box

SELECT @g.STWithin(@h);

And then again, I might be wrong ...
David Webb

jgabler SSC Enthusiast Points: 136 More actions · Answer 4

You're right, that does work. Hmm. That makes me think then that creating the table and/or populating it via the ESRI tools may be the problem (or it's user error since both she and I are learning this as we go). Maybe I need to create the table in SQL and then just populate it with the ESRI data. At least it's narrowed down the possible causes.

Thank you David. Good to know for sure that your syntax does work (worked for me too).

jgabler SSC Enthusiast Points: 136 More actions · Answer 5

Got it working once I knew that it wasn't the syntax on the query itself. It was the SRID.

I had tried numerous SRIDs with no success, but discovered that there was a constraint on the table when I tried to update the Shape field from SQL. Gave me a constraint error, and I scripted out the constraint to see it was SRID 4269.

Solution was to change this line to use 4269 for the SRID:

SET @g = geometry::Point(@x,@y,4269);

Thanks for the help.