从SQL Server 2016中的KML获取质心 [英] Get centroid from KML in SQL server 2016

问题描述

大家好，



我对SQL Server和地理功能有疑问。我想在KML文件中获取特定形状的Centroid。



这是我的小查询

Hello all,

I have a question about SQL Server and Geography functions. I'm looking to get the Centroid of a specific shape within a KML file.

Here's my little query

DECLARE @h VARCHAR(MAX);

    set @h = 'SELECT [Id]
          ,[Name]
          ,[placemark_sd_id]
          ,[ACC]
          ,[EXS]
          ,[NAM]
          ,[TXT]
          ,[UID_1]
          ,[placemark]
      FROM [dbKMLTemp].[dbo].[myTable]
      where [NAM] = "G";'

    DECLARE @g geometry;
    SET @g = geometry::STGeomFromText (@h,0)
    select @g.STCentroid().ToString();

我收到此错误。



消息6522，级别16，状态1，行6

执行用户定义的例程或聚合几何期间发生.NET Framework错误：

System.FormatException：24114：标签SELECT [Id]，[Name]，[在输入的已知文本（WKT）中无效。有效标签是POINT，LINESTRING，POLYGON，MULTIPOINT，MULTILINESTRING，MULTIPOLYGON，GEOMETRYCOLLECTION，CIRCULARSTRING，COMPOUNDCURVE，CURVEPOLYGON和FULLGLOBE（仅限地理数据类型）。

System.FormatException：

.Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel（String input）

at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText（OpenGisType type）

at Microsoft.SqlServer。 Types.WellKnownTextReader.Read（OpenGisType type，Int32 srid）

at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText（OpenGisType type，SqlChars text，Int32 srid）



我要做的是采取以下查询，但是在哪里说POLYGON我想从select语句中获取多边形：

I get this error.

Msg 6522, Level 16, State 1, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24114: The label SELECT [Id],[Name],[ in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException:
at Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)

What I'm trying to do is take the following query but where it says POLYGON I want to get the polygon from a select statement:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @g.STCentroid().ToString();

我是从 https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stcentroid-geometry-data-type



如何合并两者？我有一个KML文件的多边形，现在我需要找到它的中心。



谢谢



Mike



我的尝试：



看看我的问题中的代码。

I got this from https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stcentroid-geometry-data-type

How do I merge the two? I have a polygon from the KML file and now I need to find the center of it.

Thanks

Mike

What I have tried:

Look at my code in the question.

推荐答案

无法帮助你。

It is impossible to help you.

引用：

消息6522，级别16，状态1，行6

在执行用户定义的例程或聚合期间发生.NET Framework错误 geometry：

System.FormatException：24114：标签SELECT [Id]，[Name]，[在输入的已知文本（WKT）中无效。有效标签是POINT，LINESTRING，POLYGON，MULTIPOINT，MULTILINESTRING，MULTIPOLYGON，GEOMETRYCOLLECTION，CIRCULARSTRING，COMPOUNDCURVE，CURVEPOLYGON和FULLGLOBE（仅限地理数据类型）。

Msg 6522, Level 16, State 1, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24114: The label SELECT [Id],[Name],[ in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).

仔细阅读错误消息，您应该看到错误不在SQL 。

这是解决方案。



有点乱，但现在我开始明白了。感谢大家的帮助。

Here's the solution.

A little messed up but now I'm starting to understand it. Thanks for everyone's help.

DECLARE @h geography;

set @h = (SELECT [placemark] FROM [dbKMLTemp].[dbo].[myTable]  where [NAM] = 'G')

DECLARE @g geometry;
SET @g = geometry::STGeomFromWKB(@h.STAsBinary(),4326)
select @g.STCentroid().ToString();

最初我也在选择很多列，我没有使用STGeomFromWKB。



Mike

Initially I was selecting too many columns and I wasn't using STGeomFromWKB.

Mike

这篇关于从SQL Server 2016中的KML获取质心的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！