question

gayathris avatar image
gayathris asked

sql query for polygon

Hello Could anyone please tell me the sql query for the following case. I have a polygon which has id,name,latitude and longitude. I used the query: select ASTEXT(polygon) AS polygon from table_name; This gives me all details but are congested. How can I seperate them? For example. In the case of a triangle, I have the points- (3.1 4.1,3.6 4.7, 4.5 5.6,3.1 4.1). I want to have a table something like this: Latitude Longitude 3.1 4.1 3.6 4.7 4.5 5.6 Is it possible to do?
sql-servergeometrylatitude-longitudepointpolygon
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Ok. I beleive here is one way of doing it. USE [tempdb] GO IF OBJECT_ID('tempdb.dbo.DelimitedSplit8K') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit8K go CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter VARCHAR(1)/*===MADE IT VARCHAR TO AVOID IMPLICIT CONVERSION WHILE COMPARING====*/ ) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) /*====FIRST ITEM HANDLED AT THE END======*/ --SELECT 1 UNION ALL -- does away with 0 base CTE, and the OR condition in one go! /*=======================================*/ SELECT N+1 FROM cteTally WHERE SUBSTRING(@pString ,N,1) COLLATE Latin1_General_BIN = @pDelimiter COLLATE Latin1_General_BIN ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN,N1),0) - N1,8000) FROM cteStart ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. /*====FIRST ITEM HANDLING===============*/ SELECT ItemNumber = 0, Item = SUBSTRING(@pString , 1, ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN),0)-1, 8000)) UNION ALL SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY N1), Item = RTRIM(LTRIM(SUBSTRING(@pString , N1, L1))) FROM cteLen ; GO DECLARE @g geometry; SET @g = geometry::STGeomFromText('POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))', 4326); SELECT @g.STAsText(); SELECT *,SUBSTRING(Item, 1, CHARINDEX(' ', Item, 2) ) Latitude, SUBSTRING(Item, CHARINDEX(' ', Item, 2), LEN(item) ) Longitude FROM [dbo].[DelimitedSplit8K](REPLACE(REPLACE(REPLACE(@g.STAsText(), 'POLYGON ', ''),'(',''),')',''),','); I have used the STAsText() and 8k Splitter to overcome this.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Here is a better approach. I have used the combination of STNumPoints() and STPointN() functions to retrieve all the Geo points and then split every X & Y co-ordinates of each point into two columns i.e. Latitude & Longitude USE [tempdb] GO IF OBJECT_ID('tempdb.dbo.[GetAllPointsFromGeometry]') IS NOT NULL DROP FUNCTION dbo.[GetAllPointsFromGeometry] go CREATE FUNCTION [dbo].[GetAllPointsFromGeometry] --===== Define I/O parameters ( @pGeometry GEOMETRY ) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(@pGeometry.STNumPoints(),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) SELECT @pGeometry.STPointN(N).STX Latitude, @pGeometry.STPointN(N).STY Longitude FROM cteTally GO DECLARE @g geometry; SET @g = geometry::STGeomFromText('POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))', 0); SELECT * FROM [dbo].[GetAllPointsFromGeometry](@g)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice thinking. +1.
0 Likes 0 ·
I have changed my code a bit. Thanks to Pavel Pawlowski for posting the STX and STY version.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Or solution like this one, which does not need any string splitting. DECLARE @tbl TABLE ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, GM geometry ) INSERT INTO @tbl(GM) SELECT geometry::STGeomFromText('POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))', 0) UNION ALL SELECT geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0); ;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), Tally(N) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM E1 a, E1 b --up to 100 points in polygon, if we need more simply add more tables or use a pre-build tally table ) SELECT tbl.ID ,tbl.GM.STNumPoints() AS NumberOfPoints ,t.N AS PointNumber ,tbl.GM.STPointN(t.N).ToString() ,tbl.GM.STPointN(t.N).STX AS X ,tbl.GM.STPointN(t.N).STY AS Y FROM @tbl tbl INNER JOIN Tally t ON tbl.GM.STNumPoints() >= t.N ORDER BY ID, t.N
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. This makes more sense to use STX and STY. It was on my mind but missed it completely. Also, I would prefer usage of TOP and CROSS APPLY instead for better performance (Only talking about me ;))
0 Likes 0 ·
Or `OUTER APPPLY` if we want to see also zero points polygons or NULL values :-) DECLARE @tbl TABLE ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, GM geometry ) INSERT INTO @tbl(GM) SELECT geometry::STGeomFromText('POLYGON ((0 0, 0 1560, 1100 1560, 1100 0, 0 0))', 0) UNION ALL SELECT geometry::STGeomFromText('LINESTRING(0 0, 2 2, 1 0)', 0) UNION ALL SELECT NULL ;WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), Tally(N) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM E1 a, E1 b --up to 100 points in polygon, if we need more simply add more tables or use a pre-build tally table ) SELECT tbl.ID ,tbl.GM.STNumPoints() AS NumberOfPoints ,t.N AS PointNumber ,tbl.GM.STPointN(t.N).ToString() ,tbl.GM.STPointN(t.N).STX AS X ,tbl.GM.STPointN(t.N).STY AS Y FROM @tbl tbl OUTER APPLY (SELECT TOP (ISNULL(tbl.GM.STNumPoints(),0)) N FROM Tally) T ORDER BY ID, t.N
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.