[GIS] Import non spatial data into SQL Server using ogr2ogr

csvogr2ogrsql server

I am trying to import a CSV files in to SQL Server 2012 using ogr2ogr. The spatial data imports without any problems, but I cannot work out why the non spatial tables fail. I can import the data using other tools but it would be convenient if I could import everything using ogr2ogr so I can script it more easily.

My VRT file looks like this…

    <OGRVRTDataSource>
      <OGRVRTLayer name="test">
        <SrcDataSource relativeToVRT="1">test.csv</SrcDataSource>
        <FID>id</FID>
        <Field name="id" src="Id" type="Integer"  />
        <Field name="Test" src="Attrib" type="string" width="100" />
     </OGRVRTLayer>
    </OGRVRTDataSource>

And my command line:-

   ogr2ogr -append -f MSSQLSpatial "MSSQL:server=SQL01;database=TESTDB;trusted_connection=yes" test.vrt

The first error I get is:-

ERROR 1: Column ogr_geometry requested for geometry, but it does not exist.

After that there are errors trying to create the attribute field because the table does not exist. If I manually create the table in SQL Server with a single field named "id", then this command works (still with error messages).

Best Answer

The solution was to upgrade the GDAL core libraries. Lesson learned!

Related Solutions

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

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

[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

Related Question