Loading SQL Server Spatial Data from the Command Line

Shape2SQL is an excellent little utility that can be used to load spatial data from shapefiles into SQL Server 2008. Considering that the shapefile format is pretty much ubiquitous throughout the spatial world, and SQL Server doesn’t provide any other mechanisms to load from this format (despite calls for it to be added), it’s pretty much on the toolset of any developer starting to use SQL Server spatial who doesn’t want to splash out on commercial alternatives such as Safe FME or Manifold.

The Shape2SQL front-end GUI is pretty self-explanatory, and looks like this:

image

You can also call Shape2SQL programmatically from the command line, although I’ve seen a couple of posts on the internet from people having problems with the syntax to do this. For what it’s worth, here are the command line arguments I use to load Ordnance Survey Open Data shapefiles from cmd:

shape2sql.exe -shp=”C:\TQ00\Settlement_Area.shp” -connstr=”Data Source=zangief\sqlexpress;Initial Catalog=OSVectorMap;Integrated Security=SSPI;” -table=”TQ00_Settlement_Area” -srid=”27700″ -oidname=”ID” -geomname=”geom27700″ -append=”true” -geography=false

image

I will admit, however, to having had problems calling Shape2SQL from Powershell. The problems seem to come from the semicolon character ; in the SQL connection string, which PoSH seems to treat as a statement-ending character, even when it is preceded by the escape character `. There’s also possibly issues with it not liking parameters containing “” marks (which I include in case the filepath contains a space).

I don’t know what the solution to this is yet (perhaps to ask somebody who knows more about PoSH than me, or else just continue to use good ol’ cmd.exe…)

This entry was posted in SQL Server and tagged , , . Bookmark the permalink.

2 Responses to Loading SQL Server Spatial Data from the Command Line

  1. Pingback: Exporting Spatial Data From SQL Server to ESRI Shapefile | Alastair Aitchison

  2. Michael Smith says:

    I was able to create a Powershell script that executes Shape2Sql, and then wait for the shape file to finish importing before starting the next one. Worked great!

    CLEAR
    $files = Get-ChildItem ‘C:\Temp\Shape2SQL\TIGER Data\Shapes’ -filter tl_2012_08*_edges.shp
    “Processing ” + $files.Count + ” files”
    $connstr = ‘-connstr=”Data Source=10.0.5.202;Initial Catalog=GeoCoder;Integrated Security=TRUE”‘

    foreach ($file in $files)
    {
    $filename = $file.FullName
    $filename
    $event = ‘C:\Temp\Shape2SQL\Shape2Sql.exe’
    $shp = ‘-shp=”‘ + $filename + ‘”‘
    $table = ‘-table=”TIGER_Data”‘
    $srid = ‘-srid=”4326″‘
    $oidname = ‘-oidname=”ID”‘
    $geoname = ‘-geomname=”geom”‘
    $append = “-append=true”
    $geography = ‘-geography=true’

    $allArgs = @($shp + ” ” + $connstr + ” ” + $table + ” ” + $srid + ” ” + $oidname + ” ” + $geomname + ” ” + $append + ” ” + $geography)

    ####
    # Uncomment the Write-Host line to see the generated command line for Shape2Sql
    ####
    #Write-Host $event $shp $connstr $table $srid $oidname $geomname $append $geography
    $p = Start-Process $event -ArgumentList $allArgs -NoNewWindow -Wait -PassThru

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s