locked
Import LAS files to SQL SERVER 2008 and higher RRS feed

  • Question

  • Hi!

    Im have worked with SQL SERVER for years (programmer and DBA) but never been in contact with spatial. At my new job at big autority working with land registration I've just got the answer from the programmers that the ONLY solution is POSTGIS and POSTGRESSQL for working with GIS-data (or Oracle if forced). Very well, we see about that. The game is a foot. They are perhaps right but I need to see it myself in a POC.

    My first try, I have a LAS file (2GB) that I have converted to txt. Got the x y z and intensity and gps_time. Thats ~750K rows.

    Q: Is it possible to import the LAS file directly and not convert it to txt. Shapefiles is out of question beacuse of its limitation of 2GB.

    Q: Is it even possible to create a POINT CLOUD DATATYP as in Oracle and Postgres?

    BR

    Erber

    Monday, November 18, 2013 3:18 PM

All replies

  • Hi erber,

    According to your description, when you bulk import data into a SQL Server table or bulk export data from a table, you can use a format file to store all the format information. SQL Server  supports two types of format files: XML formats and non-XML format files. You need to convert the LAS file to an supported format files then import to SQL Server table.

    About spatial data types, SQL Server supports the geometry data type and the geography data type. In Oracle, if you can create a point cloud datatype, I recommend you can post this question on the Oracle forums. It is appropriate and more experts will assist you.

    For more information, you can review the following article about format files for importing or exporting data (SQL Server).
    http://technet.microsoft.com/en-us/library/ms190393(v=sql.110).aspx
    Spatial Data (SQL Server): http://technet.microsoft.com/en-us/library/bb933790(v=sql.110).aspx

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, November 20, 2013 7:40 AM
  • Hi Sofiya!

    Thanks for your answer. I appreciate it. =)

    But I think you missunderstod my questions a little bit. What I meen is if its possible to set up the same datatyp or functionallity in MS SQL as in Oracle or Postgres when it comes to Point Cloude. I dont think any Oracle Forums can answer that. My intention is to use MS SQL, not Oracle or Postgres.

    I have also converted the the las-file to txt. XML-files often becomes to large. And after that its not any problem to import the data, BUT I have imported it as "plain" rows x,y, z etc. Its the z that i dont can find any support for in geometry data type and the geography data type.

    As a total we talks about 60 BILLION points. The proof of concept is just 750000 rows. This is just an experiment to see if we can use MS SQL for our main business.

    BR

    Erber


    Friday, November 22, 2013 12:22 PM
  • SQL Server doesn't have built-in support for importing any spatial-specific formats (like LAS), there is third party software that does this that works with SQL Server. Here's a list: http://www.fileinfo.com/extension/las that I got from a quick web search, probably not even a complete list, and, except for ESRI and SAFE, not even guaranteed to work with SQL Server, you'd need to research this.

    SQL Server spatial does support storing the Z and M fields in a spatial object, but their spatial library doesn't take it into consideration when doing it's computations.

    There is no built-in "point cloud" data type in SQL Server, only geometry and geography. You could program your own user-defined type using that implements that functionality, though.

    Hope this helps, Cheers, Bob

    • Proposed as answer by Sofiya Li Monday, December 2, 2013 9:48 AM
    Saturday, November 23, 2013 9:45 PM
  • What I want is to avoid all third party program as a middletire between our software and the database. To convert the .LAS file to txt is not any problem. Thats a one time issue. 

    If I just had an example on how to build a UDT using spatial coping .LAS files it would very helpful for my learning. What I need to prove to the architects is that MS SQL can be used instead of Postgres and Oracle. The geek factor is to high in that world giving the operation and development very vulnerable to specific persons.

    You can't disregard that MS Suit is more user friendly and more built for "normal" people, than geek programmers that want to sell consult hours. 

    And I would like to see MS taking GIS to a more user friendly place. Starting with the SQL Server.

    BR

    Erber

    Friday, November 29, 2013 11:11 AM