locked
importing ArcInfo data RRS feed

  • Question

  • How can ArcInfo Interchange (.e00) and ArcView Shape (.shp) files be converted or extracted to populate SQL Server 2008 with spatial data?

     

    Monday, October 22, 2007 12:55 PM

Answers

  • One option would be to use Spatial ETL software such as FME from Safe Software, which was recently announced to be supporting SQL Server 2008 Spatial: http://groups.google.com/group/fmetalk/browse_thread/thread/c7e93d1b017cb49f/1f068c674df7e87b

     

    Steven

    Monday, October 22, 2007 6:32 PM
  • Manifold reads both ArcInfo .e00 as well as ESRI shapefiles and can upload the resulting drawings into SQL Server 2008 spatial.

     

    For a complete list of formats Manifold reads, see

     

    http://www.manifold.net/info/formats.shtml

     

    Manifold also connects directly to spatial DBMS products such as Oracle Spatial, IBM DB2 with IBM Spatial Extender, PostgreSQL/PostGIS and also ESRI's "SDE" geodatabases and "Personal Geodatabases" for all DBMS systems supported by ESRI.

     

    Note that one will frequently want to massage shapefiles to deal with projection issues, combine points, lines and areas into a single data set and other modernizations before uploading into Katmai.   Resolving projection ambiguities is an especially good idea.  Manifold has endless tools for dealing with all that.

     

    A caveat: to work with SQL Server 2008 spatial you will need the spatially-capable CTP.  To work with spatial DBMS in general with Manifold (including Katmai) you'll need Manifold Enterprise Edition or higher.

     

     

    Monday, October 22, 2007 8:06 PM
  • For shape files you can also use this free tool:

    http://www.sharpgis.net/2007/12/23/ShapefileToSqlServer2008ToWPF.aspx

    Saturday, January 19, 2008 9:16 AM
    Answerer
  •  

    I haven't messed with this much further since my last post.

     

    I'm not sure how the multipart polygon data looks when exported.  If you're able to convert the single polygon data to WKT then making the multipart polygons would be as simple as finding and parsing the different polygons in each shape individually and inserting them as MultiPolygons.  Of course, I say "simply", but this could be much harder than it sounds.  I haven't messed with it yet so I don't know.

    Monday, June 2, 2008 9:25 PM

All replies

  • One option would be to use Spatial ETL software such as FME from Safe Software, which was recently announced to be supporting SQL Server 2008 Spatial: http://groups.google.com/group/fmetalk/browse_thread/thread/c7e93d1b017cb49f/1f068c674df7e87b

     

    Steven

    Monday, October 22, 2007 6:32 PM
  • Manifold reads both ArcInfo .e00 as well as ESRI shapefiles and can upload the resulting drawings into SQL Server 2008 spatial.

     

    For a complete list of formats Manifold reads, see

     

    http://www.manifold.net/info/formats.shtml

     

    Manifold also connects directly to spatial DBMS products such as Oracle Spatial, IBM DB2 with IBM Spatial Extender, PostgreSQL/PostGIS and also ESRI's "SDE" geodatabases and "Personal Geodatabases" for all DBMS systems supported by ESRI.

     

    Note that one will frequently want to massage shapefiles to deal with projection issues, combine points, lines and areas into a single data set and other modernizations before uploading into Katmai.   Resolving projection ambiguities is an especially good idea.  Manifold has endless tools for dealing with all that.

     

    A caveat: to work with SQL Server 2008 spatial you will need the spatially-capable CTP.  To work with spatial DBMS in general with Manifold (including Katmai) you'll need Manifold Enterprise Edition or higher.

     

     

    Monday, October 22, 2007 8:06 PM
  • For shape files you can also use this free tool:

    http://www.sharpgis.net/2007/12/23/ShapefileToSqlServer2008ToWPF.aspx

    Saturday, January 19, 2008 9:16 AM
    Answerer
  • Sorry to reopen an old thread - I was just wondering if anybody (Isaac or Steven?) can confirm whether ETL of spatial data (particularly SHP files) will ever be natively supported by SSIS in SQL Server 2008? (Maybe not at RTM, but in a subsequent service pack?)

    I'm aware that Manifold and FME can do this, but for those of us who are familiar with SSIS and already have a large number of packages using this tool, it would be nice to be able to add spatial data into our existing ETL workflows rather than have to use a seperate third party tool.  (Or use Morten's excellent Shp2SQL convertor!)

    Importing data from shapefiles/KML seems to be a very common request on this forums, and I'm sure will only become more so after SQL 2008 ships - it would be a shame if there was no way this could be included 'out of the box'...
    Thursday, May 1, 2008 1:06 PM
    Answerer
  • Importing shape files / KML natively out of the box definitely won't be supported in SSIS for Sql Server 2008.  I'm not on the team so I can't really speculate on if they would choose to support it in a future release.  My guess would be that this type of feature would be unlikely to be introduced in a service pack due to recent announcement of how the service pack model has changed from what it was in the past.

     

    You can still integrate shape files into your SSIS workflow however using the FME plugin that extends SSIS, http://www.safe.com/solutions/BIS/serverintegration.php, without requiring another external tool. 

    Thursday, May 1, 2008 7:14 PM
  • I had a problem using the ShapefileToSqlServer2008ToWPF in the past.

     

    You can use the following tool:

    http://www.obviously.com/gis/shp2text/

     

    This will output a csv file.  For lines and polygons each pont is on a separate line.  I created a cursor to go through and stitch them all together.

     

    I'm sure this could be automated with some fancy SSIS.

     

    It's a bit convoluted.  I think someone needs to come out with a slick shape file importer for community use.  Maybe if I find the time I'll get around to it. Smile

    Wednesday, May 14, 2008 6:23 AM
  • I did almost the same thing except I created my own VBA script in arcmap that does this. It works. BTW did you figure out how to import multi-part polygons in to sql 2008 ? Please let me know.

     

    Regards

    Aditya

    Monday, June 2, 2008 8:54 PM
  •  

    I haven't messed with this much further since my last post.

     

    I'm not sure how the multipart polygon data looks when exported.  If you're able to convert the single polygon data to WKT then making the multipart polygons would be as simple as finding and parsing the different polygons in each shape individually and inserting them as MultiPolygons.  Of course, I say "simply", but this could be much harder than it sounds.  I haven't messed with it yet so I don't know.

    Monday, June 2, 2008 9:25 PM
  • ESRI will fully support SQL Server 2008's spatial features with our upcoming 9.3 release. All of the data conversion tools that have been provided over the years to the ESRI community will work, so importing a ArcInfor Interchange or an ArcView Shape file into SQL Server 2008 will not require any additional tools.

     

    Wednesday, June 4, 2008 5:49 PM