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
------------------------------
Original Message:
Sent: 10-13-2020 17:55
From: James Nolet
Subject: Converting KML/KMZ files as a batch operation
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
Original Message:
Sent: 10-13-2020 09:23
From: Octavian Breazu
Subject: Converting KML/KMZ files as a batch operation
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:
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
Original Message:
Sent: 10-12-2020 19:48
From: James Nolet
Subject: Converting KML/KMZ files as a batch operation
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
Original Message:
Sent: 10-12-2020 17:47
From: Octavian Breazu
Subject: Converting KML/KMZ files as a batch operation
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
Original Message:
Sent: 10-12-2020 08:42
From: Bill Wemple
Subject: Converting KML/KMZ files as a batch operation
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
Original Message:
Sent: 10-11-2020 14:37
From: Octavian Breazu
Subject: Converting KML/KMZ files as a batch operation
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
------------------------------