Generated KML file from SQL query save to local drive

My SQL Query generates a XML output:

         select 'TEST.kml' as name,                  (select 'TEST' as name, (                  select (                         select top 10 issue as name,                          null as description,                          null as 'Point/coordinates',                          (                               select                                          null as altitudeMode,                                         Coordinates as 'coordinates'                               for xml path('Polygon'), type)                  from Mapping for xml path('Placemark'), type))                      for xml path ('Line') , type)                  for xml path ('Doc'), root('kml')) 

I want to save the output of the query as .XML file on to local drive.Please advise.

3 Answers

Answers 1

Not the most elegant way but it is possible to use bulk copy program and xp_cmdshell to do this. Few things first, xp_cmdshell is blocked by default by SQL Server as part of the security configuration so you will need to enable that first and BCP requires you to have access to the directory that you want to create the file.

To enable xp_cmdshell you'll need run sp_configure and RECONFIGURE, use this:

EXEC sp_configure'xp_cmdshell', 1 RECONFIGURE GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO 

Then you can run the following:

EXEC xp_cmdshell 'bcp "SELECT * FROM [Database].dbo.[Table] FOR XML AUTO, ELEMENTS" queryout "C:\test.xml" -c -T' 

Just add your query into it and make sure you add [] around your table names.

The Microsoft Documents for xp_cmdshell are here and bcp can be found here

Answers 2

Using bcp is definite choice especially when working with large data sets. Alternatively, you can try using SQL Management Studio - Export Data.

1. Open the interface - Right Click on database name, then Tasks, then Export Data

2. The menu is opened. Click Next

   

3. Then choose SQL Server Native Client, sql server, database name and authentication method:

   

4. Then where to save the data:

   

5. Then how we are getting the data (in your case SQL query):

   

6. Past the query:

   

7. Then we have some settings, click finish.

   

Answers 3

To save the results of a remote query to a local file, you could use a Powershell script like this example:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI") $command = New-Object System.Data.SqlClient.SqlCommand(@("     select 'TEST.kml' as name,                  (select 'TEST' as name, (                  select (                         select top 10 issue as name,                          null as description,                          null as 'Point/coordinates',                          (                               select                                          null as altitudeMode,                                         Coordinates as 'coordinates'                               for xml path('Polygon'), type)                  from Mapping for xml path('Placemark'), type))                      for xml path ('Line') , type)                  for xml path ('Doc'), root('kml');"), $connection); $connection.Open(); $command.ExecuteScalar() | Out-File -FilePath "C:\KmlFiles\YourFile.kml"; $connection.Close(); 

Read More

How to process KML/GeoJSON in Nodejs?

I ran out of google searches and reaching out for help here. We are right now processing KML file using geoXml3 at client side. But Ideally I would want to pre-process it in server side and send the ploygons on the client side. Because KML file is 18MB file and it takes forever to download on client side and then client parses it and draws the polygon on google map.

We changed KML files to GeoJSON and reduced the size , compressed it - after all the circus the response time is still not good. I just want to know if there is a way / library in node that can do this.

2 Answers

Answers 1

here are some that might help? https://github.com/jlord/gs2geojson https://www.npmjs.com/package/geojson

also if you can please specify what are you using it for? and why do you need it? maybe I can try to suggest, some better way to optimize your app. if you give me a scenario, it will be great..

Answers 2

When you say that you are compressing the file, what do you mean? If you mean an algorithm such as zip or lha, that won't necessarily reduce the size of the file that much. What you want to do is to remove line segments from the KML file. In reducing some geographic information, I found that there were lengths of many miles that varied less than a foot from a straight line. Since the data points were spaced every few feet, that meant that the vast majority of the points in the KML file could be removed without making an appreciable change in the appearance of the geometry. Looking for straight line segments is relatively simple.

You should also keep in mind the scale of the map you are viewing and the spacing of the data points in the KML file. Even if the lines are complex curves, it may be possible to remove large numbers of points by trying to curve fit segments of the features and reduce the size of the data in this way.

You seem to be implying that downloading the data from the server to the client takes far more time than processing the data on the server. If this is correct, reducing the number of points is the most efficient method.

Read More