[GIS] How to use non-native spatial data from MS SQL Server with ogr2ogr

mapinfoogr2ogrsql server

I have some tables in MS SQL Server (2012) with WKT string columns.
I want to export these data to a MapInfo tab file.

Currently I am using the following command:

ogr2ogr -f "MapInfo File" testsql.tab "MSSQL:Server=etc." -sql "select geography::STGeomFromText(FACE,4326),NAME from TABLE" -a_srs "EPSG:4326"

Where FACE is a string column containing WKT geometries.

This gives some problems, because the coordinates are switched. This is supposedly due to a change between SQL Server 2008 and 2012. Outputting it to WKB works better.

I was wondering if it is possible that ogr2ogr uses the WKT column directly without converting it to the MS SQL Spatial type first?

Best Answer

Have you tried accessing the database directly from MapInfo Professional or do you not have access to MapInfo Professional?

You could try:

create a view that selects all the columns from your table, uses the STGeomFromText to convert the WKT to a SQL Server spatial type
create an ODBC data source that connects to the database
create the MapCatalog in the database using EasyLoader (you'll find this tool in the Tool Manager in MapInfo Professional)
use MapInfo Pro to make the DBMS table in this case your new view) mappable
open the view into MapInfo Professional

I have shared the slides from a session on how to use SQL Server 2008 with MapInfo Pro. You can find the slides here: MI Pro and SQL Server 2008 or on Slideshare

Related Solutions

[GIS] Converting mixed MapInfo tab files

So what do you do with the Multi Line Strings that aren't imported? Are you just ignoring these?

You could change the Geometry type of your column to also allow MultiLineString

Peter

[GIS] How to preserve non-ASCII characters in ogr2ogr import

I have same problem, my understanding is that the stored data is correct, if you consider it a binary utf-8 encoded data, to convert this data to nvarchar i wrote this (partially tested) UDF function in sql server:

CREATE FUNCTION varbinUTF8_to_nvarchar ( @data VARBINARY(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @n INT
    DECLARE @buffer NVARCHAR(MAX)
    SET @buffer = N''
    SET @n = DATALENGTH(@data)

    DECLARE @i INT
    SET @i = 0

    DECLARE @byte INT
    DECLARE @byte2 INT
    DECLARE @byte3 INT
    DECLARE @byte4 INT

    DECLARE @decoded NCHAR

    WHILE @i < @n
        BEGIN
            SET @i = @i + 1
            SET @byte = SUBSTRING(@data, @i, 1)
            IF ( @byte < 0x7f )
                SET @decoded = NCHAR(@byte)
            ELSE
                IF @byte & 0xe0 = 0xc0
                    BEGIN
                        SET @i = @i + 1
                        SET @byte2 = SUBSTRING(@data, @i, 1)
                        SET @decoded = NCHAR(( ( @byte & 0x1F ) * 0x40 )
                                             + ( @byte2 & 0x3f ))       
                    END
                ELSE
                    IF @byte & 0xf0 = 0xe0
                        BEGIN
                            SET @i = @i + 1
                            SET @byte2 = SUBSTRING(@data, @i, 1)
                            SET @i = @i + 1
                            SET @byte3 = SUBSTRING(@data, @i, 1)
                            SET @decoded = NCHAR(( ( @byte & 0xF )
                                                   * 0x1000 ) + ( ( @byte2
                                                          & 0x3f ) * 0x40 )
                                                 + ( (@byte3 & 0x3f) ))
                        END 
                    ELSE
                        IF @byte & 0xf8 = 0xf0
                            BEGIN
                                SET @i = @i + 1
                                SET @byte2 = SUBSTRING(@data, @i, 1)
                                SET @i = @i + 1
                                SET @byte3 = SUBSTRING(@data, @i, 1)
                                SET @i = @i + 1
                                SET @byte4 = SUBSTRING(@data, @i, 1)
                                /*
                                EDIT 2014-11-27 THIS PART WERE WRONG
                                SET @decoded = NCHAR(( ( @byte & 0xF )
                                                       * 0x1000 )
                                                     + ( ( @byte2 & 0x3f )
                                                         * 0x40 )
                                                     + ( ( @byte3 & 0x3f )
                                                         * 0x40 )
                                                     + ( (@byte4 & 0x3f) ))
                                 FOLLOWS PATCH, but sql server does not support unicode chars > 16 bit
                                */
                                SET @decoded = NCHAR(( ( @byte & 0x7 )
                                                       * 0x40000)
                                                     + ( ( @byte2 & 0x3f )
                                                         * 0x1000 )
                                                     + ( ( @byte3 & 0x3f )
                                                         * 0x40 )
                                                     + ( (@byte4 & 0x3f) ))


                            END 
                        ELSE
                            BEGIN
                                return  N'BAD UTF-8 MESSAGE'
                            END 

            SET @buffer = @buffer + @decoded

        END 
    return  @buffer
END

Then i can convert the wrong formatted field using something like:

SELECT     
    dbo.varbinUTF8_to_nvarchar(CONVERT(VARBINARY(MAX), name_1) )
FROM
    dbo.che_adm1

In your specific case you should be able to get nvarchar data doing :

SELECT languagecode, dbo.varbinUTF8_to_nvarchar(CONVERT(VARBINARY(MAX), name) as name, longitude, latitude
FROM tempdb.dbo.london_points;

Best Regards Andrea

Best Answer

Related Solutions

[GIS] Converting mixed MapInfo tab files

[GIS] How to preserve non-ASCII characters in ogr2ogr import

Related Question