MapInfo Pro

 View Only
Expand all | Collapse all

Converting KML/KMZ files as a batch operation

  • 1.  Converting KML/KMZ files as a batch operation

    Posted 10-11-2020 14:38
    Edited by Octavian Breazu 10-11-2020 16:11
    Hi,

    I am looking to find the best way to convert a bunch of KML/KMX files into SQL Server geometry objects. 
    I know the latest versions of MapInfo contain some batch operation support or at least that was the plan back in 2018 from I can see on different posts.
    Could somebody point me to the best source of information on this subject, please?

    The files are grouped into their own directories, per applicant, and we have iterate through all of them to convert. If that requires writing a separate C# application and call the MAPInfo converter library using the MapInfo  Extensions, we are willing to that too.

    Background information
    Maps are grouped in directories based on client submission  and they are in KML or KMX format.

    The way I see it at this time is to organize the load in stages as follows
    • Create a shell application in C# and scan our directory/subdirectory and for each subdirectory do:
      • For each KML/KMZ file run the MUT.MBX tool to convert to MapInfo native TAB format in place them in a shadow directory structure. 
      • For each converted to TAB file:
        • run the EazyLoader tool to upload SQL Server tables;
        • Move the data from the upload temp table into our app data tables to reflect the client relationship to geo objects
        • Delete the data from the upload temp table;
      • Repeat the loop

    Question: is any better way to achieve goal?

    Again our major challenge is to load and convert to SQL Server geometry objects a few hundred KLM/KMZ files as a batch process.

    Thank you.

    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-12-2020 07:16
    Octavian,

    I have done the same for a client in the past.  However it was only a handful of KML files so I just used the front end of MapInfo rather than trying to automate batches.   Your process above is basically what I followed.  

    Three things to watch out for :
    1) object types - my data was just polygon objects but some MapInfo data types won't load into SQL Server and I don't know if some of those types may exist in your KML files.
    2) Styles - every KML files I've ever had has been imported into MapInfo with just the default styles and symbols. If you use easyloader to put the data into the DB then these style attributes will be loaded too. 
    3) check your projections match all the way through the process

    HTH

    Nick






    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 3.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-12-2020 08:20
    Use free ogr2ogr to convert the kml files into TAB or MIF. And then upload.
    I think it can even upload directly to SQL Server without MapInfo involved at all.

    Sort of related question:
    https://gis.stackexchange.com/questions/195169/kml-to-shapefile-ogr2ogr/195775

    ------------------------------
    Uffe Kousgaard
    ROUTEWARE
    Roskilde
    ------------------------------



  • 4.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-12-2020 17:38
    Yes, you are right, there is a command to load the data directly into SQL Server. I was trying to make that work a few hours now. Unfortunately, ogr2ogr documentation is not the best I've seen and somebody has to do a lot of try and error. 

    Even if I make it work, I still have to run the shell program to move or relate the data to the client master record so objects will maintain the ownership. 
    Unless I am missing something.

    Thank you
    -Octavian

    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------



  • 5.  RE: Converting KML/KMZ files as a batch operation

    Employee
    Posted 10-12-2020 08:42
    When using the FME Quick translator there is a second dropdown at far right of the Dataset input box in Set Translation Parameters UI that allows you to set multiple folders and files for input, so I'm not sure you need to create a separate application to scan directories for *.kml\kmz files.

    ------------------------------
    Bill Wemple
    Principal QA Engineer
    Pitney Bowes
    Troy, NY
    ------------------------------



  • 6.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-12-2020 17:48
    I should mention that I'll get access to a MapInfo licence only tomorrow so, for now, everything I know is from reading the documentation.
    I have seen that feature in the Easy Loader documentation however, unless I am missing something, I still need to relate the geo objects to the right client after the load. I mean that the EasyLoader can upload but to a table and it does not have knowledge of the master client record Id. If I load all the graphical files in a batch, I'll end up with a bunch of records and no ownership.
    Please correct me if I am wrong, I might miss something in this process.

    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------



  • 7.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-12-2020 19:48
    Hi Octavian,

    I use the below batch script (that uses ogr) to load MapInfo Tables into SQL Server, which you can perhaps adapt for your needs if you are still having trouble loading into SQL Server, and still need to go down this path.  I am not sure what else you need to do once the data is in SQL Server, but would carry these extra actions out in SQL Server itself (e.g. via a stored procedure) if possible.  Alternatively, you can issue SQL statements using ogr.  Example:

    ogrinfo -sql "Create Spatial Index SIndex_SP_Geometry on %DEF_DBNAME%.%DEF_SCHEMA%.%DEF_TABPREFIX%%DEF_TABNAME% (SP_GEOMETRY) WITH (BOUNDING_BOX = %DEF_BOUNDS%)" "MSSQL:server=%DEF_SVRNAME%;DATABASE=%DEF_DBNAME%;trusted_connection=%DEF_TRUSTED%;uid=%DEF_UID%;pwd=%DEF_PWD%"

    BATCH SCRIPT:
    @echo off
    date /t
    time /t

    SET DEF_MIDIR=I:\GisMaint\Cadastre\VicMapAddressProperty\MapInfo\GDA94_MGA55\Data_In\Latest
    SET DEF_SVRNAME=SQLSVR
    SET DEF_DBNAME=GISDB
    SET DEF_SCHEMA=dbo
    SET DEF_TABPREFIX=VMADD_
    SET DEF_EPSG=28355
    SET DEF_BOUNDS=(-180000, 5560000, 880000, 6345000)
    SET DEF_UID=gis
    SET DEF_PWD=gis
    SET DEF_TRUSTED=yes

    REM Set clean environment path, set OGR-required constants
    SET OSGEO4W_ROOT=I:\OSGeo4W64
    SET PATH=%OSGEO4W_ROOT%\bin;%WINDIR%\system32;%WINDIR%;%WINDIR%\WBem
    SET GDAL_DATA=%OSGEO4W_ROOT%\share\gdal
    SET GDAL_DRIVER_PATH=%OSGEO4W_ROOT%\bin\gdalplugins

    @echo SOURCE: %DEF_MIDIR%
    @echo DESTINATION: %DEF_DBNAME%

    SET DEF_TABNAME=ADDRESS
    @echo _
    @echo %DEF_TABNAME%
    ogr2ogr -f "MSSQLSpatial" -progress -overwrite -a_srs EPSG:%DEF_EPSG% "MSSQL:server=%DEF_SVRNAME%;DATABASE=%DEF_DBNAME%;trusted_connection=%DEF_TRUSTED%;uid=%DEF_UID%;pwd=%DEF_PWD%" "%DEF_MIDIR%\%DEF_TABNAME%.tab" -sql "select *, CAST(OGR_STYLE As character(254)) AS MI_STYLE from %DEF_TABNAME%" -gt 2048 -lco "GEOM_TYPE=geometry" -lco "GEOM_NAME=SP_GEOMETRY" -lco "SCHEMA=%DEF_SCHEMA%" -lco SRID=%DEF_EPSG% -nln "%DEF_SCHEMA%.%DEF_TABPREFIX%%DEF_TABNAME%"

    @echo complete
    time /t
    REM pause

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 8.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-13-2020 09:24
    Thank you James. This is a really good piece of code to inspire from.

    I am having one more question I am really struggling to find an answer to and maybe you can give an idea about what could be the cause.

    In my ogr2ogr conquest, I wanted to convert the KMX file to geoJSON format since we have code written to load that type of data. I've learned that geoJSON does not support multi-layer and you have to run the command against each eventual layer in the source KMZ file.  A lot of experts on the internet said that and some cloud-based converters do just that.
    So I have prepared the following command line:

    ogr2ogr -f "GeoJSON" -clipsrclayer "Duct" -skipfailures "C:\Test\File2.json" "C:\Test\File2.kmz"​


    The KML file looks like:


    Based on what I've read the ogr2ogr treats the "Winfield FTTH" and "Winfield PoP" and "Duct" as separate layers (?) but I might be wrong because when I've tried the command above I always get:
    Unless I am using the command switch improperly, no matter what "layer name" I select, the ogr2ogr always attempts to convert the 4 nodes showing in the picture above. In fact, I am not even sure what makes it so selective with some nodes and not all of them. 
    I might have to post the question to a different list but if you guys have some experience with this, it would be greatly appreciated. 


    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------



  • 9.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-13-2020 17:55
    Hi Octavian,

    I should have mentioned that in my script I included "CAST(OGR_STYLE As character(254)) AS MI_STYLE" which is not correct - ogr styles are different to mi styles, so this part requires further work.  However that is irrelevant to your question.

    I'm not sure why you are having trouble with the kmz conversion.  Have you tried converting to a format other than geojson?  Also, have you tried @William Wemple's suggestion of using FME Quick Translator?  He is referring to the Universal Translator mbx tool which is available by default in MapInfo.  I think you have a MapInfo licence now so you might like to give that a go.

    Another option is the full version of FME from SAFE software.​

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------



  • 10.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-14-2020 09:51
    Thank you James.

    It looks like ogr2ogr has conversion problems when the converting to GeoJSON and KML file contains a structure of nodes similar to the one in my picture. I am able to convert a KML file having relatively complicated polygons and polylines but arranged in a simple, one layer structure. 
    There are other people on the net saying that in a multilayer KML, for the geoJSON format I have to run the ogr2ogr tool against each layer. That is fine, I just don't know what exactly the "layer" means in my example or how can I identify it when exists. I've tried pretty much all combinations using the nodes presented in the tree but none worked.
    For example, the folks at "MyGeoData" are able to convert. Basically, they produce a bunch of files, not really one-to-one to the node structure but pretty close. Somehow they are able to identify the layers the client has put in and call ogr2ogr successfully.
    I ran the ogrInfo to get the names of the layers and use them to call the ogr2ogr but it did not work as I expected.

    In the meantime, I was successful in loading KML/KMZ directly into the database using ogr2ogr which gives me some relief for now. The KML to geoJSON remains on the theoretical side.

    I have to study the "styles" concepts in the geo world. I have a lot of IT experience but I am green on the gis :-). Learning every day...

    Thanks again.



    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------



  • 11.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-13-2020 05:16
    Octavian,

    EasyLoader loads the spatial and attribute info into SQL Server.  The spatial data is stored as another column of data per row.   If you have a layer of data from a KML file that has an ID in MapInfo then that ID will be loaded too.  

    Nick




    ------------------------------
    Nick Hall
    Mapchester LTD
    nick.hall@mapchester.co.uk
    ------------------------------



  • 12.  RE: Converting KML/KMZ files as a batch operation

    Posted 10-14-2020 09:57
    Thank you Nick.

    I the meantime I was bale loading the KML/KMZ directly to SQLServer using the ogr2ogr tool, so the intermediate step to mapInfo might not be necessary at this stage of the project. I have to inspect the whole set of files which is in hundreds.

    Regards,
    Octavian

    ------------------------------
    Octavian Breazu
    Knowledge Community Shared Account
    ------------------------------