Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm a QGIS users but not a database person and I need some help on the DB side. I'm currently in the need to import several shape files into MSSQL 2008 and I have no clue how to do that and my IT guy either. Can anyone explain to me the components needed,and the step by step how to proceed, or point me towards an online tutorial for unexperienced DB person like me please.

Furthermore, lets assume that all my shape files are imported into MSSQL 2008 that is installed on an internal secured server, Do I need to install another component to have access to my shape files from outside my organization (ie: from a remote location via the web ) ? or will I be able to access my data easily using the QGIS Browser ?. If yes, what do I need to do (or tell my IT guy to do !!) ?

As you can tell, I'm a real rookie when it comes to DB servers...I just hope soemone can point me towards the right direction so I can help my IT guy in the process.

Thanks for all your help. Yann

share|improve this question
    
Super ! I'll look into the Shp2SQL feature and test it out. If it doesn't work as well as I think It should, I'll venture into the GDAL world !!! Thanks for the information. Cheers –  user10380 Sep 21 '12 at 13:00

3 Answers 3

After trying a whole host of them I came across one (FME Workbench) that could handle large file sizes (over 1GB) and had lots of options to manipulate the data as it was imported. It's not free, but I just used the fully functional trial which met my needs. I did have to watch the video on that page to confirm I was doing it right, but it worked very well. Only problem I had with it was that it didn't like parenthesis in the SQL Server password. Other than that, it worked great. It actually does a lot more than just converting to SQL Server and can convert it into pretty much anything.

share|improve this answer

I have written a simple code in arcobjects so if you have installed ArcGIS Basic (ArcView) or ArcGIS Engine you can create a console application using this code c#. Change in code the enum for your license (esriLicenseProductCode).

    //author: nicogis
    private static LicenseInitializer m_AOLicenseInitializer = new shape2SQL.LicenseInitializer();

    [STAThread()]
    static void Main(string[] args)
    {
        //ESRI License Initializer generated code.
        if (!m_AOLicenseInitializer.InitializeApplication(new esriLicenseProductCode[] { esriLicenseProductCode.esriLicenseProductCodeBasic },
        new esriLicenseExtensionCode[] { }))
        {
            System.Console.WriteLine(m_AOLicenseInitializer.LicenseMessage());
            System.Console.WriteLine("This application could not initialize with the correct ArcGIS license and will shutdown.");
            m_AOLicenseInitializer.ShutdownApplication();
            return;
        }

        try
        {
            //append the shapefile in a table of sql server
            //with a field type sqlGeometry
            //in this section set your parameters
            /******************************************************************************/

            //path full of shapefile import in sql server
            string shapefileLocation = @"C:\Temp\Varie\shape2SQL\ImportShape.shp";

            //name table in sql server
            string nameTable = "TestImport";

            //name field geometry in sql server
            string nameFieldShape = "Geom";

            //insert the fields of shapefile without add shape field. These fields also must be exists in the table of sql server
            List<string> lstFields = new List<string>();
            lstFields.Add("fieldName1");
            lstFields.Add("fieldName2");
            lstFields.Add("fieldName3");
            //..

            /******************************************************************************/

            lstFields.Add(nameFieldShape);
            //ShapefileWorkspaceFactory is singleton so use activator
            Type t = Type.GetTypeFromProgID("esriDataSourcesFile.ShapefileWorkspaceFactory");
            System.Object obj = Activator.CreateInstance(t);
            IWorkspaceFactory workspaceFactory = obj as IWorkspaceFactory;

            IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspaceFactory.OpenFromFile(System.IO.Path.GetDirectoryName(shapefileLocation), 0);
            IFeatureClass featureClass = featureWorkspace.OpenFeatureClass(System.IO.Path.GetFileNameWithoutExtension(shapefileLocation));

            using (ComReleaser comReleaser = new ComReleaser())
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlServerConnectionString"].ConnectionString))
            {
                conn.Open();

                string sqlCommandText = string.Format("insert into [{0}]({1}) Values({2})", nameTable, string.Join(",", lstFields.ToArray()), string.Join(",", lstFields.ConvertAll(s => "@p" + s).ToArray()));

                IFeatureCursor featureCursor = featureClass.Search(null, true);
                comReleaser.ManageLifetime(featureCursor);

                Dictionary<string, int> dctIndexFields = new Dictionary<string, int>();
                foreach (string s in lstFields)
                {
                    if (s == nameFieldShape)
                    {
                        continue;
                    }

                    dctIndexFields.Add(s, featureClass.FindField(s));
                }

                IFeature feature = null;
                while ((feature = featureCursor.NextFeature()) != null)
                {
                    IGeometry geometry = feature.ShapeCopy as IGeometry;
                    IGeometryFactory3 geometryFactory = new GeometryEnvironment() as IGeometryFactory3;
                    byte[] wkb = geometryFactory.CreateWkbVariantFromGeometry(geometry) as byte[];
                    SqlBytes polygonWKB = new SqlBytes(wkb);
                    SqlGeometry sqlGeometry = SqlGeometry.STGeomFromWKB(polygonWKB, geometry.SpatialReference.FactoryCode);

                    using (SqlCommand sqlCommand = new SqlCommand(sqlCommandText, conn))
                    {
                        sqlCommand.CommandType = CommandType.Text;
                        foreach (string i in dctIndexFields.Keys)
                        {
                            sqlCommand.Parameters.Add(new SqlParameter("@p" + i, feature.get_Value(dctIndexFields[i])));
                        }

                        sqlCommand.Parameters.Add(new SqlParameter("@p" + nameFieldShape, sqlGeometry) { UdtTypeName = "Geometry" });
                        sqlCommand.ExecuteNonQuery();
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message + System.Environment.NewLine + ex.StackTrace);
        }
        finally
        {
            //ESRI License Initializer generated code.
            //Do not make any call to ArcObjects after ShutDownApplication()
            m_AOLicenseInitializer.ShutdownApplication();
        }
    }
}

}

share|improve this answer

I'm a QGIS users but not a database person and I need some help on the DB side. I'm currently in the need to import several shape files into MSSQL 2008 and I have no clue how to do that and my IT guy either. Can anyone explain to me the components needed,and the step by step how to proceed, or point me towards an online tutorial for unexperienced DB person like me please.

To import shape files into SQL Server, you have to convert it first to SQL. One free tool for this is Shp2SQL. You can also do this using GDAL.


Furthermore, lets assume that all my shape files are imported into MSSQL 2008 that is installed on an internal secured server, Do I need to install another component to have access to my shape files from outside my organization (ie: from a remote location via the web ) ? or will I be able to access my data easily using the QGIS Browser ?. If yes, what do I need to do (or tell my IT guy to do !!) ?

Viewing your data is a bit easier. According to this Nathan's blog post:

QGIS now has a native MS SQL 2008 provider. The provider can found using the new toolbar button (purple icon) or in the MS SQL node in the QBrowser tree. The provider also supports drag and drop import.

So you should be able to add it just like any other layer.

share|improve this answer
    
Thanks for your help. These steps worked great for points and lines shape files but won't work for polygons shape files. Any idea why ? –  user12913 Nov 19 '12 at 15:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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