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.

am trying to migrate PostGIS geometry to SQL server Spatial data. which ETL i have to use. it's the first time i'll work with SQL server Spatial. is there any Data ETL model that do this migration. thanks for help

share|improve this question
    
There are multiple ETL applications mentioned here: Spatial ETL comparisons –  RyanDalton Feb 5 '13 at 16:18
add comment

3 Answers

You can try

  • pgsql2shp (shapefile intermediate) and then use the sqlspatialtools to load
  • OGR2OGR supports reading and writing at both ends of that pipe
  • part of me wants to suggest that a PG_DUMP to text would provide SQL that could then be consumed in SQL server.

Details click here

share|improve this answer
    
okay thank you for your answer –  Geographic Feb 5 '13 at 13:02
    
I found on the net the FME tools. is it the better way to transform my data ? –  Geographic Feb 5 '13 at 13:07
1  
Asma.O yes FME is worth the money - look into FME Desktop Workbench , there are plenty of youtube videos on it. example youtube.com/playlist?list=PL111C94B34DBE7364 –  Mapperz Feb 5 '13 at 14:32
    
ok thank you, yes it worth money but i'll try the trial version, waiting for it to download. @Sunil , am trying to use sqlspatialtools specially the shapetoSQL tool using the shapefile uploader for sql server, but i don't know what to choose either planar geometry or geography (spheric). what's the difference? do i have to choose planar geometric, because am not working in a continental-scale (data are limited to morocco) –  Geographic Feb 5 '13 at 16:49
1  
@Asma.O sorry for my late reply.. I am using Geography type but for more information please check following link stackoverflow.com/questions/4991827/… –  Sunil Feb 6 '13 at 5:15
show 2 more comments

try to use program brigantine to migrate PostGIS geometry to SQL server Spatial data

share|improve this answer
    
I really dont know how this piece of software can help someone to migrate PostGIS spatial data to MsSql spatial data. It looks just like a simple GIS viewer and there is no even basic manual... –  sys49152 Jul 31 '13 at 10:36
    
very cool spartan program, you just copy/paste layers between servers, and it does the rest for you –  zetah Jun 4 at 5:03
add comment

From

http://www.postgresonline.com/journal/index.php?/archives/31-GDALOGR2OGR-for-Data-Loading.html


Microsoft SQL Server (and other ODBC) to PostgreSQL

OGR has a driver that supports ODBC DSN connections. Unfortunately this will only work on Windows. Below is an example that imports all tables from a SQL Server database to PostgreSQL. In this example, the DSN I have is using NT Authentication or has the name and password encoded in the System DSN. For this you need to setup a DSN connection to the SQL Server database in ODBC Manager (we described setting up a DSN in Using MS Access with PostgreSQL, except the ODBC driver seems to only work with System DSNs not File DSNs. For more details on referencing the ODBC DSN - check http://www.gdal.org/ogr/drv_odbc.html

ogr2ogr -f "PostgreSQL" PG:"host=pghost user=pgloginname dbname=pgdbname password=pgpassword" 
"ODBC:system_dsn_goes_here" -update -lco OVERWRITE=yes -nlt NONE

As noted, geometry fields seem to be created and right field types are not necessarily created. SQL Server text come in as VARCHARS and VARCHARS come in as CHARS etc. One way to get around this is to create your table structures before hand and use OGR2OGR to just load the data.

E.g.

--This will append to a table called orders reading from the SQL Server (ODBC) table orders and customers. It will only append like-named fields.

psql -h pghost -p 5432 -U pguser -d pgdbname -c "TRUNCATE TABLE orders;TRUNCATE TABLE customers;"

ogr2ogr -append -f "PostgreSQL" PG:"host=pghost user=pgloginname dbname=pgdbname password=pgpassword port=5432" 
"ODBC:system_dsn_goes_here" orders customers

Or correct the structures afterword by dropping unnecessary fields using a script generation technique similar to what we described in DML to Generate DDL and DCL. So your script builder SQL to drop all the ogc_fid and wkb_geometry fields created by OGR2OGR would look something like this.

SELECT 'ALTER TABLE ' || t.table_schema || '.' || t.table_name || ' DROP COLUMN ogc_fid;' 
    FROM information_schema.tables t 
        INNER JOIN information_schema.columns c 
        ON (t.table_name = c.table_name AND t.table_schema = c.table_schema AND c.column_name = 'ogc_fid')
    UNION 
    SELECT 'ALTER TABLE ' || t.table_schema || '.' || t.table_name || ' DROP COLUMN wkb_geometry;' 
    FROM information_schema.tables t 
    INNER JOIN information_schema.columns c 
    ON (t.table_name = c.table_name AND t.table_schema = c.table_schema AND c.column_name = 'wkb_geometry'; 
share|improve this answer
add comment

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.