Get centroid from KML in SQL server 2016

Question

0.00/5 (No votes)

See more:

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

SQL

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();

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:

SQL

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();

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.

Posted 18-Jun-17 6:28am

mzrax

Updated 18-Jun-17 9:46am

Add a Solution

2 solutions

Solution 1

It is impossible to help you.

Quote:
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).

With a careful reading of the error message, you should have seen that the error is not in SQL.

Posted 18-Jun-17 7:09am

Patrice T

Comments

mzrax 18-Jun-17 14:39pm

It's impossible to help me if you don't know what you're talking about.

Then how do you get the center point of a polygon from a query of selected points?

RedDk 18-Jun-17 14:47pm

You're absolutely right about not knowing what they're talking about and I'd add that PM is a point grubber but that might be construed as starting a flame war here on CP. All that legalese aside, I think your query is wrong because it is SELECTg many things. Try SELECTg the thing to which the "geometry" (or "geography" as the case might be) is being applied. That ALONE.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mzrax · Accepted Answer · 2017-06-18T09:46:00

Solution 2

Here's the solution.

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

SQL

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();

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

Mike

Posted 18-Jun-17 9:46am

mzrax

Comments

RedDk 20-Jun-17 18:32pm

I was looking at this today and noticed that you've declared 'geography' but set the variable as if it's potentially 'geometry' which I guess is cool because when I try working this equality I use 'geometry' but because I have treated the binary as 'geography', translating text from longitude and latitude, I can't use STCentroid() ... there's no such thing. But I have to assume that @h is indeed geography and using the SRID 4326 worked! Which is mystifying because ... only you can see what that SELECT statement is refering to in myTable. What is the data type of [NAM]?