[GIS] tools and ETLs for migrating data benchmarking – from shapefile to SQL SERVER

shapefilespatial-etlsql server

i need to migrate my shapefiles to my database SQL SERVER, and i don't know what's the best tool or ETLs to choose, i already tried FME Desktop and i succeded to load the shapefiles on my db, but while searching i found other tools (like : artend, data migration tool kit, ETL integrator, Scriptella …). Now i am confused, i don't know what to choose amoung many open source and licenced solution. Plz help.

Best Answer

try to use open-source program brigantine to migrate shapefiles to SQL SERVER

Related Solutions

[GIS] Seeking options for Spatial ETL (Extract, Transform, Load)

For a recent project working with several GBs of spatial data, I started the data loading / reprojections with FME. It worked well, but there is a learning curve.

By the end of the project I was using Python scripts to automate the reamining processes. FME can be scripted, but if you have the Python basics why complicate things further? Python gives you complete flexibility and with each import script written your Python skills are improving.

I found the following Python packages invaluable when working with data transformations:

PyProj
GeoPy
Shapely
xlrd for importing data from Excel spreadsheets
pyobdc to connect to databases
SQLAlchemy to run SQL statements and work with databases

If you have a developer / programming background I'd recommend using Python, if you prefer working with a GUI (which can also generate nice images for documentation) I'd recommend FME.

SQL Server – How to Import Shapefile Using Only SQL Scripts

I would equate this to asking for a SQL script that imported an excel file to SQL Server. Even if it were possible, you would be better off if you did not use only SQL scripts to automate the deployment of information.

Its like asking for a screwdriver to hammer in a nail. SQL Server doesn't understand things like shapefile format and excel spreadsheets because that's not what a DBMS is for: it's what DTS and/or SSIS is for.

To answer your question, however, GDAL has been the DTS of spatial information for quite some time now. Have a look at the following command to import spatial information to SQL Server using ogr2ogr:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=spatial;trusted_connection=yes" "TG20.shp"

GDAL Binaries (Assuming Windows) are available from this list (via). You can install according to this process, but you should ignore the steps about python.

After installing GDAL, just modify the connection string and data source in the above command to suit your needs. Note that ogr2ogr also supports multiple other formats.

Best Answer

Related Solutions

[GIS] Seeking options for Spatial ETL (Extract, Transform, Load)

SQL Server – How to Import Shapefile Using Only SQL Scripts

Related Question