Both MS SQL and PostgreSQL support spatial data types: geometry and geography (PostgreSQL requires that PostGIS extension is installed for this purpose). However, internal format of storing data is distinguished in the two DBMS. Therefor, spatial data must be migrated via special text format called "well-known text" (WKT). Assume, we have the MS SQL table defined as follows:
CREATE TABLE test_spatial(geom_flg geometry, geog_flg geography)
Then we can use STAsText function to extract geometry and geography
data as text:
SELECT geom_flg.STAsText(), geog_flg.STAsText() FROM test_spatial
You will see something like this:
| POLYGON ((50 50, 100 50, 100 100, 50 50)) | LINESTRING (1.5 2.45,3.21 4) |
| MULTILINESTRING((0 0,-1 -2,-3 -4),(2 3,3 4,6 7)) | POINT(1 -2) |
| ... | |
In PostgreSQL the equivalent table is creates using the same CREATE TABLE
statement. Then the spatial data is inserted in the table through WKT-representation
as follows:
INSERT INTO test_spatial VALUES
('POLYGON ((50 50, 100 50, 100 100, 50 50))', 'LINESTRING (1.5 2.45,3.21 4)'),
('MULTILINESTRING((0 0,-1 -2,-3 -4),(2 3,3 4,6 7))', 'POINT(1 -2)');