2

I have a set of polygons out of which I want to make a KML file, my table schema is as follows :

CREATE TABLE [dbo].[utilitygeo](
    [ID] [int] NOT NULL,
    [PolygonID] [varchar](8000) NULL,
    [geog] [geography] NULL
)

I tried extracting Lat/Long from each polygon and then thought of converting them into a KML file, following is the code:

declare @g geography;

select top 1 @g =  geog from MyDB..Geoterritory

declare @i int = 1
create table ##result(
    Latitude float,
    Longitude float
)

while @i < @g.STNumPoints()
BEGIN
    insert into ##result values(
        @g.STPointN(@i).Lat,
        @g.STPointN(@i).Long
    ) 
END

select * from ##result

But this takes a lot of time. Is there a better way to generate KML file from these polygons in SSMS. Thanks

1

You forgot to increment the @i

while @i < @g.STNumPoints()
BEGIN
    insert into ##result values(
        @g.STPointN(@i).Lat,
        @g.STPointN(@i).Long
    ) 

    set @i = @i + 1

END
2

If you are using QGIS. Open it and add postgis layer after loading the layer you can right click on the layer and choose Save As and select Keyhole Markup Language (KML). It uses GDAL to do the conversion to KML. you can see some interesting options regarding KML creation in gdal kml driver page

  • I am not using QGIS, but will try it. – Ashish Gaur Sep 27 '13 at 8:05
  • This worked for me. I did not have to add a postgis layer, all I had to do was go to "Save As" and select Keyhole Markup Language (KML). – richardpilgrim Jan 22 '15 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.