postgresql中geometry的插入测试

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');
INSERT INTO geometries VALUES	('Polygon ZM', 'POLYGON ZM ((116.67699900000002 40.31124399999999 0 -1.797693134862316e+308,116.75339499999995 40.306276000000025 0 -1.797693134862316e+308,116.75028899999995 40.303773999999976 0 -1.797693134862316e+308,116.74793799999998 40.30245200000002 0 -1.797693134862316e+308,116.74575200000004 40.300287000000026 0 -1.797693134862316e+308,116.74385400000006 40.29903899999999 0 -1.797693134862316e+308,116.74249099999997 40.298137999999994 0 -1.797693134862316e+308,116.73949400000004 40.295591 0 -1.797693134862316e+308,116.73871899999995 40.294280000000015 0 -1.797693134862316e+308,116.73794199999998 40.292463 0 -1.797693134862316e+308,116.73746600000004 40.28993800000001 0 -1.797693134862316e+308,116.70738100000005 40.291019000000006 0 -1.797693134862316e+308,116.670436 40.295345 0 -1.797693134862316e+308,116.67297099999996 40.30589900000001 0 -1.797693134862316e+308,116.67699900000002 40.31124399999999 0 -1.797693134862316e+308))')
INSERT INTO geometries VALUES	('Polygon ZM', 'POLYGON((116.67699900000002 40.31124399999999 0 -1.797693134862316e+308,116.75339499999995 40.306276000000025 0 -1.797693134862316e+308,116.75028899999995 40.303773999999976 0 -1.797693134862316e+308,116.74793799999998 40.30245200000002 0 -1.797693134862316e+308,116.74575200000004 40.300287000000026 0 -1.797693134862316e+308,116.74385400000006 40.29903899999999 0 -1.797693134862316e+308,116.74249099999997 40.298137999999994 0 -1.797693134862316e+308,116.73949400000004 40.295591 0 -1.797693134862316e+308,116.73871899999995 40.294280000000015 0 -1.797693134862316e+308,116.73794199999998 40.292463 0 -1.797693134862316e+308,116.73746600000004 40.28993800000001 0 -1.797693134862316e+308,116.70738100000005 40.291019000000006 0 -1.797693134862316e+308,116.670436 40.295345 0 -1.797693134862316e+308,116.67297099999996 40.30589900000001 0 -1.797693134862316e+308,116.67699900000002 40.31124399999999 0 -1.797693134862316e+308))')


[root@pgdb data]# cat ok.sql

BEGIN;

SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;


COPY market_grid (objectid,"ttt","tttt",total_value,value_density,value_proportion,total_value_score,value_density_proportion,high_value_score,total_score,x,y,shape,total_score_no) FROM STDIN;
43      HR-14           10053   .063    0       2.01    .4      0       3.66    116.70865       40.30065        POLYGON ZM (( 116.67699900 40.31124400 0.00000000 -1.797693134862316e+308, 116.67297100 40.30589900 0.00000000 -1.797693134862316e+308, 116.67043600 40.29534500 0.00000000 -1.797693134862316e+308, 116.70738100 40.29101900 0.00000000 -1.797693134862316e+308, 116.73746600 40.28993800 0.00000000 -1.797693134862316e+308, 116.73794200 40.29246300 0.00000000 -1.797693134862316e+308, 116.73871900 40.29428000 0.00000000 -1.797693134862316e+308, 116.73949400 40.29559100 0.00000000 -1.797693134862316e+308, 116.74249100 40.29813800 0.00000000 -1.797693134862316e+308, 116.74385400 40.29903900 0.00000000 -1.797693134862316e+308, 116.74575200 40.30028700 0.00000000 -1.797693134862316e+308, 116.74793800 40.30245200 0.00000000 -1.797693134862316e+308, 116.75028900 40.30377400 0.00000000 -1.797693134862316e+308, 116.75339500 40.30627600 0.00000000 -1.797693134862316e+308, 116.67699900 40.31124400 0.00000000 -1.797693134862316e+308))  212
\.
COMMIT;

[root@pgdb data]# export PGPASSWORD=sdexxx
[root@pgdb data]# psql --host=192.168.208.40 --port=5432 --username=sde --echo-errors jzshdb -f ok.sql -v ON_ERROR_STOP=1
psql:ok.sql:2: WARNING:  there is already a transaction in progress
BEGIN
SET
SET
psql:ok.sql:10: ERROR:  duplicate key value violates unique constraint "r157_sde_rowid_uk"
DETAIL:  Key (objectid)=(43) already exists.
CONTEXT:  COPY market_grid, line 1
psql:ok.sql:10: STATEMENT:  COPY market_grid (objectid,"ttt","tttt",total_value,value_density,value_proportion,total_value_score,value_density_proportion,high_value_score,total_score,x,y,shape,total_score_no) FROM STDIN;
ROLLBACK
WARNING:  there is no transaction in progress

处理：truncate table重导数据

select * from geometries

SELECT name, ST_AsText(geom) FROM geometries;

注意点1：ora2pg从oracle导出带geometry字段类型的表结构，在导入导postgresql库时，表结构可能需要做1个操作替换：

sed -i 's/ ST_/ /g' *.sql
psql --host=192.168.208.40 --port=5432 --username=sde --echo-errors jzshdb -f table.sql -v ON_ERROR_STOP=1

注意点2：ora2pg从oracle导出geometry字段表记录，在导入导postgresql库时，data可能需要做两个操作替换：

sed -i 's/SRID=;//g' *.sql
sed -i 's/ nan/ -1.797693134862316e+308/g' *.sql
psql --host=192.168.208.40 --port=5432 --username=sde --echo-errors jzshdb -f ok.sql -v ON_ERROR_STOP=1

相关参考：
http://postgis.net/workshops/postgis-intro/geometries.html