将地理转换为几何 SQL Server 2008R2 [英] Convert geography to geometry SQL Server 2008R2

问题描述

您好，我在 SQL Server 中有以下代码，为什么要计算@geog 的sTArea 失败并且@geom 成功?，我怎么能将此多边形从几何数据类型转换为地理数据类型，以获得STArea?，谢谢.

Hello, i have the following code in SQL Server, why if i want to calculate the sTArea of @geog fails and with @geom succeed?, how can i convert this polygon from geometry to geography datatype in order to get the STArea?, thank you.

DECLARE @geom geometry;
SET @geom = geometry::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
select @geom.STArea();

DECLARE @geog geography;
SET @geog = geography::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
select @geog.STArea();

推荐答案

我戳了一下，得到了这个答案 如何在 MS SQL Server 2008 中将几何数据转换为地理数据? 这或多或少只是指向http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx 引导我找到合理的解释和工作代码.

I poked around a little and this answer How can I convert Geometry data into a Geography data in MS SQL Server 2008? which more or less just points to http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx leads me to a reasonable explanation and working code.

关键:您必须确保您的几何首先可以转换为有效的地理.

The crux: You have to make sure your geometry can be translated to valid geography first.

代码(当然可以组合一些操作，但为了清楚起见，这里将它们分开.)

The code (which can certainly have some operations combined, but they are broken out here for clarity.)

DECLARE @geog GEOGRAPHY;
DECLARE @geom GEOMETRY;

SET @geom = GEOMETRY::STGeomFromText('POLYGON ((-99.213546752929688 19.448402404785156, -99.2157974243164 19.449802398681641, -99.2127456665039 19.450002670288086, -99.213546752929688 19.448402404785156))', 4326); 
SET @geom = @geom.MakeValid() --Force to valid geometry
SET @geom = @geom.STUnion(@geom.STStartPoint()); --Forces the correct the geometry ring orientation
SET @geog = GEOGRAPHY::STGeomFromText(@geom.STAsText(),4326) 

SELECT @geog.STArea();

对于那些不会一直阅读 Spatial Ed 博客文章的人来说，一个重要提示是，请记住，这种方法是幼稚的，因为它不能适应多种潜在的边缘条件.不过，这种方法应该适用于许多情况."

And for those that won't read all the way through Spatial Ed's blog post one important tip, "please bear in mind that this approach is naive in that it does not accommodate several potential edge conditions. Never-the-less, this approach should work in many cases."

这篇关于将地理转换为几何 SQL Server 2008R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！