Shape2SQL

Back to download page

The Shape2SQL tool allows you to easily upload data contained in shapefiles to Microsoft's SQL Server 2008.

Shape types supported

  • Point
  • MultiPoint
  • (Multi)Polygon
  • (Multi)LineStrings 

Z and M values are included if available in the shapefile.

Settings

  • Replace existing table: If checked and table exists, it will be overridden. If not, data will be appended (Table schema must match).
  • Planar/Geometry geometry: Chooses between planar or spherical/geographic data type. Select planar geometry if your data is in a projected coordinate system or contains geographic features that doesn't adhere to the SqlGeography limitations, or you need spatial operators not available on the geography type.
  • Set SRID: Sets the Spatial Reference EPSG ID of the data* (Geography requires an SRID in the 4000s). NOTE: This does NOT reproject data if your data is in a different SRID. You should set the SRID to match your source data.
  • Create Spatial index: Adds a spatial index to the table** (if appending to an existing table this is ignored).
  • Table Name: Name of table in database.
  • Geometry Name: Name of geometry column in the database
  • ID Column Name: Name of ID column. Leave empty if you don't want an ID column**
  • Attribute columns: Select the columns you want to upload to the database.

*Setting the SRID on the table also creates a uniform SRID constraint on the table.
**An ID column name is required to generate a spatial index.

FAQ:

Q: Is the source code available?
A: No.

Q: Are there any commandline options?
A: There are some, but they are fairly untested. Try running "shape2sql.exe /help" to see the options.

Q: Your tool doesn't support this and that, could you add this for me?
A: I'm adding functionality as I need it (and currently it covers all of my needs). If you need a more powerful tool, I recommend you look at other far superior tools like ArcGIS or FME.

Q: The load process hangs on "creating spatial index", and no data is created in the SQL Database.
A: This usually occur when large datasets are loaded and you enabled spatial index creation. Try disabling creating the index, and then add that manually in SQL later when you're done loading data.

Q: Does the tool support reprojecting data prior to uploading?
A: No. I recommend using a 3rd party tool prior to uploading. For instance take a look at OGR2OGR. The SRID parameter merely acts as an attribute. If you set it to something different than your source data, the data is not reprojected, and you will end up with an incorrect SRID in your table.

Q: What SRID should I set my table to?
A: Ask the people who provided you with the data.

Q: When I try to upload my shapefile as a Geography type, one or more features fails.
A: Make sure your data is using a geographic coordinate system (longitude/latitudes). If that is the case, you might have to unproject your data first. Furthermore, the geography type has several limitations compared to the geometry type like not allowing individual features to self-intersect (usually a problem with features split in two along the dateline) or having features that span over more than one hemisphere. When possible the tool will try and auto-fix some specific scenarios for you.

Q: Will polygons that have been split into a MultiPolygon across the dateline be merged when being uploaded? (like for instance Russia)
A: Yes it will try (but also see previous question).

Q: When I use this tool to load a shapefile with many polygons, sometime it will stop with an "Error inserting row" message, stating that "The app domain with specified version id (7) was unloaded due to memory pressure and could not be found."
A: SQL separates memory into (essentially) two big pieces: the space for the buffer pool and "other". "Other" includes the space for the CLR, and it is possible to exhaust that without the process as a whole running out of memory.  You can tweak this with the -g startup option, and it is entirely possible this will fix the problem.  See: http://msdn.microsoft.com/en-us/library/ms190737(SQL.100).aspx (Thanks to Isaac Kunen)

Q: The tool doesn't work with Sql Server 2012
A: While the tool officially doesn't support 2012, I received this workaround from Richard Bramfit (Thanks Richard!): "I found that the programs would not work on the new SQL SERVER DENALI CPT3, because the programs were trying to find a dll named sqlSpatial.  It looks as though ( but I may be wrong) that in this new server configuration, the dll is now named SqlServerSpatial110.dll So I found that if I just copied this dll, and renamed it to SqlServerSpatial.dll, and placed the copy in the same folder as your programs, then everything works fine."
Note that this might change in future releases of Denali.

Back to download page