KML DATA TYPES

Question

Hello Experts

Our company is starting a new project, in which the KML data is loaded in compressed format into sql server. This KML data is used by a web server to give out the required info..(which is address) to the user. 

I have many questions with regards to this..

1. Is it possible to load the compressed format of the KML data into SQL Server

2. When the web server grabs the data from the sql server, how can it change the digitized data into user understandable language.

Can someone throw light on this?

Thanks

ALI DBA

Answer 1

Hi Ali,

1.) Do you want to use SQL Server simply as a method to store/retrieve KML data, or do you want to actually be able to query/manipulate it using the spatial methods provided by the geometry/geography type?

KML is simply a dialect of XML, so you can store KML data directly into any column of a SQL Server database using the XML datatype.
I'm not sure what you mean by 'compressed format KML', but if this is binary, you can either use the varbinary(max) datatype, or the filestream datatype to store your compressed KML.

So, it's very easy to store KML in SQL Server. But if you want to actually be able to interrogate that data (e.g. "show me all the features from this KML document that are within 50miles of xxx"), then you'll have to convert it from KML into the native geography datatype. For that, I would recommend you use software such as Safe FME to import it.

2.) "Change the digitized data into user understandable language" - sorry but I don't understand the question. KML is text-based XML, so it already is "user-understandable", if you know the elements of the KML format. What language did you want the data to be displayed in?

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 2

Hello tanoshimi

Thank you for your reply. 

   I want to store the KML Data in the column of my table. when the web application gets a request from the user, this KML data has to be sent to         cache of web server from our box which has the original KML data. The web server will send this data to the user. So as per your reply, I am assuming that, i need to use convert the KML into a native geographic datatype in order to send the cache to the web server. Am I correct?

Thanks

Ali

Answer 3

Also can you please tell me what are the minimum requirements that I need to follow as per the best practices of Microsoft to have this KML data in a SQL Server. 

Thank you

Ali DBA

Answer 4

I think you're a little confused...

KML is a file format for storing spatial information. It is most commonly associated with Google Earth, but there are lots of other programs that can read/write KML files.
The following is an example of a simple KML file:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Placemark>
    <name>Simple placemark</name>
    <description>Attached to the ground. Intelligently places itself 
       at the height of the underlying terrain.</description>
    <Point>
    <coordinates>-122.0822035425683,37.42228990140251,0</coordinates>
    </Point>
  </Placemark>
</kml>

Since KML information is text-based (or, to be specific, XML-based), you can store it any any database that is capable of storing text information (which is pretty much any database). If your KML files describe complex geographic features such as the outline of countries, then it might not be a good idea to store them directly in the database, but store them as files in the filesystem and them simply store a reference to that file in the database (which is essentially how the SQL Server filestream datatype works)

Any data held on a database is capable of being sent to a webserver (via ADO.Net, or similar DB connection) - it doesn't matter what datatype it is... but you don't "send" a cache to the webserver. One user makes a request to the webserver, which connects to the database and retrieves the necessary information. But then, that result is cached on the webserver so that it can be re-used if another user asks for the same information, without having to go back to the DB again.

The reason I mentioned the geography datatype is that this is the SQL Server Spatial forum, which is primarily a place for asking questions about the spatial features in SQL Server (the geometry and geography datatypes). Storing and retrieving KML documents in SQL Server isn't really a spatial application, as such.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 5

You cleared most of my confusion. I am really thank full to you. So, According to my understanding from your reply, we don't need to worry how web server is going to convert the KML data into user readable language(English) because it is just like XML Data. Is my understanding correct.

Thank you very much. 

Ali DBA

Answer 6

Yes - that's absolutely correct. You can retrieve KML data from the database to a web application, and it will be readable just like the sample code above.

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290

Answer 7

Thank you very much.. 

Answer 8

Hello Tanoshimi

I have one more question running in my mind. Now that i have decided to used XML Data type to my column, how can i load the KML data ( which is coming from Google Earth)  into my sql table

Answer 9

To insert the KML as XML data into a table, you just need a SQL insert statement:

How is it coming from Google Earth? Into a web application you wrote? If the web application is using ASP.NET for example, you'd just need a few ADO.NET calls.

You could even shred the relevent pieces of the KML document (like the geography data and placemark names) into SQL columns for better searching. You'd store the document as XML column if you needed to serve up the entire document again.

But the database API to use to insert into SQL Server depends entirely on how your application that consumes the KML document is written.

Hope this helps,
Bob Beauchemin
SQLskills

Answer 10

I really appreciate for your reply bob. Previously, I have never worked with spatial data and i am confused with few concepts in it. Actually, our project is to create the KML Data zip(.KMZ) it and send it to sql server. I guess we are using JCAP to get these files into sql server......Yesterday, I was experimenting a ssis package pretending KML File to be a XML File. There were whole lot of tables which were showing up while I was configuring the package.  Can you clear my confusion. AS DBA, I know that SQL 2008 has two spatial data types. As far as my knowledge from the research over these data types, we should not be using any of these data types. Because we are not creating the polygons right from scratch rather we are using one good KML File for every point. Correct me if I am wrong.

Thanks 

ALI

Answer 11

Hi Ali,

KML is not a recogized data type of its own in SQL Server. KML is an XML-based document format that contains spatial as well as textual data. So you have two choices here: 

1. You can store the entire KML docment as XML with SQL Server's XML data type. You could also store the entire compress KMZ document as a blob in SQL Server
2. You can decompose the KML document into its component parts (name-value pairs) and store each piece as a column in a relational table or multiple relational tables. In this way, you could extract the spatial piece of the KML document into a SQL Server spatial data type, if that would be helpful to you. SSIS will extract XML to strings, not spatial data. You could programmatically extract the spatial data in KML using the XML nodes method, but it's non-trivial (and SSIS does not support extracting spatial data from KML documents directly). If you provide a sample document, it would be easier to write an example.

I am totally unfamiliar with JCAP (a web search says it stands for "Capcha Validation in Javascript", which wouldn't be a valid way to receive any data, so there must be another meaning), but if you have a KML or KMZ file on the file system, you can store them like this (choice #1):

CREATE TABLE kmltable (
 id INT IDENTITY PRIMARY KEY, -- this column is not strictly needed
 thekml XML
 -- other columns as needed
)
go

-- this is one INSERT-SELECT statement
INSERT INTO kmltable
SELECT * FROM OPENROWSET(BULK 'C:\ge-kml_Example-1.kml', SINGLE_BLOB) as x

-- XML document
SELECT * FROM kmltable

CREATE TABLE kmztable (
 id INT IDENTITY PRIMARY KEY, -- this column is not strictly needed
 thekmz VARBINARY(MAX)
 -- other columns as needed
)
go

-- this is one INSERT-SELECT statement
INSERT INTO kmztable
SELECT * FROM OPENROWSET(BULK 'C:\ge-kml_Example-1.kmz', SINGLE_BLOB) as x

-- Zipped version of KML document in binary
SELECT * FROM kmztable

Hope this helps,
Bob Beauchemin
SQLskills