December 27, 2010 at 12:02 pm
Since this week is traditionally slow at my work place I decided to take the opportunity to learn about the geospatial data types in SQL Server 2008. I just happen to have some demographic data that contains lat and long geocodes. So I created two column, one for each geospatial data type since I'm learning. I took the geocodes and updated the columns using with:
geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
geography::Point([Latitude], [Longitude], 4326)
So far so good as there appears to be some sort of binary data in the columns now. What I'm trying to accomplish is to be able to pull data from the table that contains the new geospatial data types (table is called [address]) by passing in a polygon. My polygon is:
'Polygon((40.315260 -75.329130,40.308720 -75.320540,40.297590 -75.310240,40.264330 -75.357620,40.293660 -75.389210,40.327300 -75.372900,40.326910 -75.356590,40.315260 -75.329130))'
and here is the code that I used on the table:
declare @geo geometry
set @geo = Geometry::STPolyFromText('Polygon((40.315260 -75.329130,40.308720 -75.320540,40.297590 -75.310240,40.264330 -75.357620,40.293660 -75.389210,40.327300 -75.372900,40.326910 -75.356590,40.315260 -75.329130))',0)
select *
from [address]
where geomLocation.STContains(@geo) = 1
From what I've read this method (STContains) should return a 1 where the polygon exists in the data.
FYI... I know the data exists as we have an alternate way of getting the data using the same polygon and data is being returned. We're trying to replace the current process which uses some trig functions and is difficult to read to using geospatial datatypes.
Am I missing something? Any tutorials you can provide would be greatly appreciated.
Thanks
J.D.
December 27, 2010 at 1:19 pm
Got it! I had to change the SRID in my variable to match the one in the data.
set @geo = Geometry::STPolyFromText('Polygon((40.315260 -75.329130,40.308720 -75.320540,40.297590 -75.310240,40.264330 -75.357620,40.293660 -75.389210,40.327300 -75.372900,40.326910 -75.356590,40.315260 -75.329130))',4326)
In this case it's 4326. And it is way faster than the method we are currently using.
October 12, 2011 at 12:23 pm
As a geo newbie, I wonder that do I need to set SRID for geometry (not geography) data?
October 13, 2011 at 9:11 am
jun-1034733 (10/12/2011)
As a geo newbie, I wonder that do I need to set SRID for geometry (not geography) data?
See the following from BOL:
If two spatial instances do not have the same SRID, the results from a geometry or geography Data Type method used on the instances will return NULL.
The default SRID for geometry instances in SQL Server is 0. With geometry spatial data, the specific SRID of the spatial instance is not required to perform calculations; thus, instances can reside in undefined planar space. To indicate undefined planar space in the calculations of geometry data type methods, the SQL Server Database Engine uses SRID 0.
http://msdn.microsoft.com/en-us/library/bb964707.aspx
So you do have to declare the SRID for geometry instances unless you want to use the default of 0 for undefined planar space.
Jason Wolfkill
October 13, 2011 at 9:22 am
JD Gonzalez (12/27/2010)
Got it! I had to change the SRID in my variable to match the one in the data.
set @geo = Geometry::STPolyFromText('Polygon((40.315260 -75.329130,40.308720 -75.320540,40.297590 -75.310240,40.264330 -75.357620,40.293660 -75.389210,40.327300 -75.372900,40.326910 -75.356590,40.315260 -75.329130))',4326)
In this case it's 4326. And it is way faster than the method we are currently using.
Yep, methods applied to instances of a spatial data type that do not have the same SRID return NULL.
You can use the STSrid method to determine the SRID of an instance.
For geography, http://msdn.microsoft.com/en-us/library/bb933887.aspx
For geometry, http://msdn.microsoft.com/en-us/library/bb933851.aspx
Jason Wolfkill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply