[GIS] Polygons to Lines SQL Server 2008/2012

sql server

Given a polygon (no multipolygons, or holes) what is the best way to explode it into lines using T-SQL. The end results is I need a record for each line that makes up the polygon.

Best Answer

It's late answer, but better late than never.

I had same problem and solved it by using STCurveN() for getting Nth line. But, this function doesn't work with polygons, so I converted polygon into LineString using STBoundary(). Now, STCurveN() works great and you can do whatever you want with it.

geometry::STGeomFromWKB([WKBGeom],0).STBoundary().STCurveN(Index)

This is an example of using all of this. You can make loop, put counter instead of 'Index' and get all lines from polygon in a result set.

Related Solutions

STDistance Unit in SQL Server 2008 – Understanding

I guess it works the same as PostGIS. If you are working in geometry type with geometry functions, the function just calculate with the unit the map has. In your case it seems to be lon lat degrees. Then your distance will not make much sense because the lat and lon degrees is of different length except on the equator.

So, what you have to do is transform your data to some meter based projection or use the geography type with geodetic functions.

HTH

Nicklas

[GIS] Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

I don't know about SDE versioning but the database can be easily used to track changes.

Your trigger as it is will only be triggered only on insert statements and will update the LastUpdated column for all rows in the table:

CREATE TRIGGER t_I_TestTable on dbo.TestTable FOR INSERT AS            
BEGIN
    UPDATE TestTable SET TestTable.LastUpdated = Getdate()
END
GO

By reading your question I think what you want is something like this:

CREATE TRIGGER t_I_TestTable on dbo.TestTable FOR UPDATE AS            
BEGIN
    UPDATE TestTable SET TestTable.LastUpdated = Getdate()
         FROM TestTable INNER JOIN deleted d
         on TestTable.id = d.id 
END
GO

The trigger above will update the LastUpdated column to the current date whenever a row is updated (assuming you have an ID column). The deleted table is a virtual table made available by sqlserver to the trigger. Handling insert statements can be done with a default value instead of a trigger like the following:

ALTER TABLE TestTable ADD 
    CONSTRAINT DF_TestTable_LastUpdated DEFAULT (getdate()) FOR LastUpdated
GO

This article has pretty good explanations and examples.

Best Answer

Related Solutions

STDistance Unit in SQL Server 2008 – Understanding

[GIS] Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

Related Question