2

I'm trying to import some shapefiles into SQL Server.

At first I had difficulty finding third party tools as I realized that Microsoft has not supported this out of the box. So I found Shape2Sql and Ogr2Ogr.
Then I realized that Shape2Sql is not under active development so that I had to replace some DLL files to make it work.
Then I imported some shapefiles from geonames.com.
Then I found better shapefiles in http://www.openstreetmap.org, downloaded from here
Then I tried to import them using Shaep2Sql, but encoding was not correct.

Then I tried with some different collations to see if the problem goes away or not, and no success.
Then I tried to import it using Ogr2Ogr (version 2.18), but it failed with memory failure error. I searched and found out that this is a reported bug and it's not solved yet.
Then I tried to download older version (before 2.0) and it didn't have ogr2ogr exe file. enter image description here

And now I'm stuck, spending more than 2 days trying to make it work and the more I search the less I find.

Do we have other tools for importing shape files into SQL Server? Is it possible to do it via C# code through some library? I have no idea cause I'm new to GIS and I don't need much from GIS. Only bounding boxes for a bunch of cities. That's all I want.

Update: I also tried importing dbf files into Excel by simply opening them with Excel, and again Unicode characters are not shown properly:enter image description here

4
  • Did you check what encoding the original shapefile has? Is there an .cpg file existing which is a textfile with the encoding in it and is it displayed correctly in some gis program? – Matte Jun 6 '17 at 8:25
  • @Matte, I downloaded files from download.geofabrik.de/asia/iran-latest-free.shp.zip, and the .cpg files says UTF-8. However, when I open it using QGIS, names are not shown properly. I emailed them about the possibility of their files being corrupt, but they said their files are OK. – Saeed Neamati Jun 6 '17 at 8:28
  • 1
    Qgis usually does ignore the cpg entry. Try to set it to UTF-8 in the "layer-properties - general" tab to UTF-8 manually. I just downloaded the files and they are shown correctly. Excel in the current versions does not have a option to open dbf in another encoding as your systems encoding as far as i know. What options did you use when working with Shape2Sql? – Matte Jun 6 '17 at 8:44
  • @Matte, Sharp2Sql has no option related to encoding. I simply use default options and select all columns. Do we have other tools for importing into SQL Server? Or, do we have a chain of tools, like converting shape file into a csv file and then importing that into SQL Server? After all it's simply data and we have Well-Known Text that can represent geometry and geography data. – Saeed Neamati Jun 6 '17 at 8:47
1

First you should find out the enconding of your database. This answer should help you find out.

As shape2sql does not have an option for encoding you should encode your shapefile according to your database encoding.

Or you can change the encoding of the database like mentioned in this answer.

If you want to change the encoding of your shapefile there are many ways to do so. You could use QGIS and save your layer with the new encoding. You also can use LibreOffice to save the .dbf in another encoding. The important thing is to have the correct encoding in the programs at first.

0

I have no idea why importing shape files should be such a twisted, complicated task. I ended up using SharpMap library, using only GeoAPI, NetTopologySuite, ProjNet, and SharpMap DLL files in a console application, to import it via C# code.

For future reference this is my code, and explanations are afterwards:

class Program
{
    static void Main(string[] args)
    {
        args = new string[] { @"D:\Temp\iran-latest-free.shp\gis.osm_places_free_1.shp" };
        EnsureArgs(args);

        Initialize();

        var shapeFile = new ShapeFile(args[0]);
        shapeFile.Open();
        if (shapeFile.GetFeatureCount() == 0)
        {
            throw new FrameworkException("No records found to be imported");
        }
        string tableName = CreateTable(shapeFile);
        InsertRecords(shapeFile, tableName);
    }

    private static void InsertRecords(ShapeFile shapeFile, string tableName)
    {
        var count = shapeFile.GetFeatureCount();
        int counter = 0;
        for (int i = 0; i < count; i++)
        {
            var feature = shapeFile.GetFeature((uint)i);
            var columns = shapeFile.GetFeature((uint)i).Table.Columns;
            var columnsScript = "";
            foreach (DataColumn column in columns)
            {
                columnsScript += "[{0}],".Fill(column.Caption);
            }
            columnsScript = columnsScript.Trim().Trim(',');
            columnsScript += ", [Geo]";
            var valuesScript = "";
            var values = feature.ItemArray.Select(x => x.ToString());
            foreach (var value in values)
            {
                valuesScript += "N'{0}', ".Fill(value.Replace("'", "''"));
            }
            valuesScript = valuesScript.Trim().Trim(',');
            valuesScript += ", geometry::STGeomFromText('{0}', 4326)".Fill(feature.Geometry.AsText());
            var query = @"
                insert into {0} ({1})
                values ({2})
            ".Fill(tableName, columnsScript, valuesScript);
            Database.Open(RepositoryFactory.ConfigurationRepository.Context.Database.Connection.ConnectionString).Run(query);
            Logger.Count(counter++);
        }
    }

    private static string CreateTable(ShapeFile shapeFile)
    {
        var columns = shapeFile.GetFeature(0).Table.Columns;
        var columnsScript = "";
        foreach (DataColumn column in columns)
        {
            columnsScript += "\r\n" + column.Caption + " nvarchar(max),";
        }
        //columnsScript = columnsScript.Trim().Trim(',');
        string tableName = "Temp" + RandomHelper.GenerateAlphanumericToken(10);
        var tableScript = @"
            create table {0}
            (
                {1}
                Geo geometry
            )
        ".Fill(tableName, columnsScript);
        Database.Open(RepositoryFactory.ConfigurationRepository.Context.Database.Connection.ConnectionString).Run(tableScript);
        return tableName;
    }

    private static void Initialize()
    {
        var gss = new NtsGeometryServices();
        var css = new SharpMap.CoordinateSystems.CoordinateSystemServices(
            new CoordinateSystemFactory(),
            new CoordinateTransformationFactory(),
            SharpMap.Converters.WellKnownText.SpatialReference.GetAllReferenceSystems());

        GeoAPI.GeometryServiceProvider.Instance = gss;
        SharpMap.Session.Instance
            .SetGeometryServices(gss)
            .SetCoordinateSystemServices(css)
            .SetCoordinateSystemRepository(css);
    }

    private static void EnsureArgs(string[] args)
    {
        if (args.Length == 0)
        {
            throw new FrameworkException("Arguments are not provided");
        }
        var file = args[0];
        if (!File.Exists(file))
        {
            throw new FrameworkException("File {0} doesn't exist".Fill(file));
        }
    }
}

Disclaimer: This code is done by me, without truly knowing what I'm doing. I just know that GIS is based on data, and I need to insert some data for each point in SQL Server. Database is my own utility wrapping ADO.NET's functionality. Fill is a utility used instead of string.Format. This code might break for you. It's just here to help people like me know more about tools available to them. Also all fields are imported as nvarchar(max) for the least possibility of any data type problems. Table is created on the fly via a RandomHelper class which is also mine, and just creates a random token.

0

Although the import of shapefiles into SQL Server can be frustrating, there are two simple processes that can be used assuming you have the shp2sql application.

  1. Purchase ArcMap for Desktop which cost around $100US per licence per annum. Load the shapefile (eg roads.shp) into ArcMap and then save it as a data export giving it another name such as roadsA.shp, then using shp2sql import the later file into SQL.
  2. Using QGIS (free) load the shapefile into QGIS, then using Layer/Save As... Type in a file name that you want to save the original shp to. Make sure your CRS is set to the correct zone. For some reason, I selected latin1 for Encoding. In Layer options select POLYGON as the SHPT type, or other options depending on what the shapefile is - polygon, line, point, etc. Select OK then use the shp2sql application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.