当前位置：文江博客话题详情

ogr2ogr 从 PG 到 SHP 更改 .DBF 中的数据类型

发布于 2024-08-20 03:30:28 字数 4296 浏览 2 评论 0原文

更新：

是的，就是这样！

正如 mloskot 所说，整个表都需要被丢弃。使用 sql 选择字段会丢失有关字段宽度的信息。

谢谢，解决了！

大家好，

我遇到了 ogr2ogr 的问题。我正在尝试将 PostgreSQL 表转储到 Shapefile。 ogr2ogr 似乎改变了整数和字符等数据类型的大小。

这是 Shapefile 集被读入的数据库结构的一部分：

Table "test"
nd_1 - numeric(8,0)
nd_2 - numeric(2,0)
nd_3 - numeric(2,0)
nd_9 - character varying(60)

并且它看起来正确。

有趣的地方就开始了：

ogr2ogr air5000.shp "PG: [...]" sql 'select
CAST(nd_1 AS INTEGER),
CAST(nd_2 AS INTEGER),
CAST(nd_3 as INTEGER),
CAST(ND_9 AS CHARACTER VARYING(60))
from test' ;

然后

dbview -e test.dbf 

Field Name Type Length Decimal Pos
nd 1 - N - 11 - 0
nd 2 - N - 11 - 0
nd 3 - N - 11 - 0
nd 9 - C - 80 - 0

num-length 从 8,2,2 变为 11，char 长度从 10 变为 80。

我读到，如果未指定宽度（长度），这些是默认值。

我如何指定宽度？

我尝试了带有或不带有强制转换的每个组合，如下所示：

select
CAST(nd_1 AS NUMERIC),
CAST(nd_2 AS NUMERIC),
CAST(nd_3 as NUMERIC),
CAST(ND_9 AS CHARACTER VARYING(60))

在dbview中给出：

Field Name Type Length Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

没有强制转换（从测试中选择*)

Field Name      Type    Length  Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

在任何情况下都给出与原始版本相差甚远的结果。

debugfile [--debug on] 没有提供任何特殊的恕我直言，这是我尝试过的 3 ogr2ogr 命令的输出：

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.
PG: PQexec(DECLARE executeSQLCursor CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: 2 features read on layer 'sql_statement'.

我也尝试了 -dsco precision=no 但没有也有帮助。

也许 dbview 是在跟我开玩笑，但它显示了正确设置的 Shapefile 源 .dbf 的标头。

有人有什么想法吗？

更新： 嗨 mloskot，

感谢您的回复和重新格式化，非常感谢！

我使用的是 GDAL 1.6.3。

我尝试指定宽度，但没有什么区别。

ogr2ogr test.shp "PG: [...] " -sql 'select CAST(nd_1 AS INTEGER), CAST(nd_2 AS numeric(2,0)), CAST(nd_3 as NUMERIC(2,0)) from test' ;

给出：

Field Name  Type    Length  Decimal Pos
nd 1          N    11       0
nd 2          N    24      15
nd 3          N    24      15

是的，我尝试指定字段而不进行强制转换，如下所示：

ogr2ogr air5000.shp "PG: [...] "-sql 'select nd_1, nd_2, nd_3, ND_9 from test'

给出：

 Field Name Type    Length  Decimal Pos
nd 1          N    24      15
nd 2          N    24      15
nd 3          N    24      15
nd 9          C    80       0

尝试

integer(field_length)

类似

CAST(nd_2 AS INTEGER(2))

不起作用，给出错误

错误1：错误：当前事务已中止，命令忽略直到事务块结束

也许我的 Postgresql 和 PostGIS 版本已经过时了？ Postgres = 8.3.5，PostGIS = 1.3.3

我将检查Shapelib，我刚刚安装了它。

原文

UpDate:

Yes, that was it!

Like mloskot said, the whole tabel needs to be dumped. selecting fields with sql loses information about fuield widths.

Thanks, that fixed it!

Hi All,

I have a problem with ogr2ogr. I'm trying to dump a PostgreSQL tabel to a Shapefile. ogr2ogr seems to change the size of data-types like integer and char.

Here's part of the DB structure that the Shapefile set was read into:

Table "test"
nd_1 - numeric(8,0)
nd_2 - numeric(2,0)
nd_3 - numeric(2,0)
nd_9 - character varying(60)

and it looks correctly.

Here's where the fun begins:

ogr2ogr air5000.shp "PG: [...]" sql 'select
CAST(nd_1 AS INTEGER),
CAST(nd_2 AS INTEGER),
CAST(nd_3 as INTEGER),
CAST(ND_9 AS CHARACTER VARYING(60))
from test' ;

then

dbview -e test.dbf 

Field Name Type Length Decimal Pos
nd 1 - N - 11 - 0
nd 2 - N - 11 - 0
nd 3 - N - 11 - 0
nd 9 - C - 80 - 0

num-length goes form 8,2,2 to 11, char length goes from 10 to 80.

I read that these are the defaults if width (length) is not specified.

How can i specify the width?

I tried every combo with or without casting, like this:

select
CAST(nd_1 AS NUMERIC),
CAST(nd_2 AS NUMERIC),
CAST(nd_3 as NUMERIC),
CAST(ND_9 AS CHARACTER VARYING(60))

which gives in dbview:

Field Name Type Length Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

no casting (select * from test) gives

Field Name      Type    Length  Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

Nowhere near the originals on any occasion.

debugfile [--debug on] gives nothing special IMHO, here is the output of the 3 ogr2ogr command i tried:

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.
PG: PQexec(DECLARE executeSQLCursor CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: 2 features read on layer 'sql_statement'.

I also tried -dsco precision=no but that did not help either.

Maybe dbview is pulling my leg, but it showed the header of the source .dbf of the Shapefile set correctly.

Anyone any ideas?

UpDate:

hi mloskot,

thanks for your reply and the reformatting, much appreciated!

i'm on GDAL 1.6.3.

i tried specifying the width, but it does not make a difference.

ogr2ogr test.shp "PG: [...] " -sql 'select CAST(nd_1 AS INTEGER), CAST(nd_2 AS numeric(2,0)), CAST(nd_3 as NUMERIC(2,0)) from test' ;

gives:

Field Name  Type    Length  Decimal Pos
nd 1          N    11       0
nd 2          N    24      15
nd 3          N    24      15

yes i tried specifying the fields without casting, like this:

ogr2ogr air5000.shp "PG: [...] "-sql 'select nd_1, nd_2, nd_3, ND_9 from test'

which gives:

 Field Name Type    Length  Decimal Pos
nd 1          N    24      15
nd 2          N    24      15
nd 3          N    24      15
nd 9          C    80       0

trying

integer(field_length)

CAST(nd_2 AS INTEGER(2))

does not work, gives error

ERROR 1: ERROR: current transaction is aborted, commands ignored until end of transaction block

maybe my Postgresql and PostGIS version are dated?
Postgres = 8.3.5, PostGIS = 1.3.3

i will check Shapelib, i installed it just now.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自演自醉 2024-08-27 03:30:28

首先，您没有指定您使用的 GDAL/OGR 版本，因此可能很难诊断问题出在哪里。请注意，各个版本都在应用改进。

是的，整数宽度和字符宽度分别是正确的值 11 和 80，是 OGR 驱动程序设置的默认值对于 ESRI Shapefile

问题是您使用 SQL 查询仅获取表格中的某些行
这会导致 OGR PG 驱动程序驱动程序不保留字段宽度。

比较这两个输出：

查询整个表

$ ogrinfo PG:dbname=test test_ogr
Layer name: test_ogr
Geometry: Point
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
FID Column = id
Geometry Column = geom
n3: String (60.0)
n1: Integer (8.0)
n2: Integer (2.0)
OGRFeature(test_ogr):1
  n3 (String) = abcdefg
  n1 (Integer) = 12345678
  n2 (Integer) = 12
  POINT (1 2)

相同的查询，但使用手动指定的 SQL select

$ ogrinfo PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
Geometry Column = geom
n1: Real (0.0)
n2: Real (0.0)
n3: String (0.0)
OGRFeature(sql_statement):0
  n1 (Real) = 12345678
  n2 (Real) = 12
  n3 (String) = abcdefg
  POINT (1 2)

如您所见，第二个选项完全失去了字段属性，这就是 OGR 的工作方式。

这是两个输出的相同示例，但转换为 Shapefile：

转储 test_ogr 表中的所有数据

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test test_ogr
$ dbfdump -h test.dbf
Field 0: Type=C/String, Title=`n3', Width=60, Decimals=0
Field 1: Type=N/Integer, Title=`n1', Width=8, Decimals=0
Field 2: Type=N/Integer, Title=`n2', Width=2, Decimals=0
n3                                                                 n1 n2
abcdefg                                                      12345678 12

使用 -sql 选项和 SELECT 查询：

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
$ dbfdump -h test.dbf
Field 0: Type=N/Double, Title=`n1', Width=24, Decimals=15
Field 1: Type=N/Double, Title=`n2', Width=24, Decimals=15
Field 2: Type=C/String, Title=`n3', Width=80, Decimals=0
                      n1                       n2 n3
12345678.000000000000000       12.000000000000000 abcdefg

因此，为了准确保留所有属性，您需要转储所有表而不指定 SQL 查询。

顺便说一句，如果您对 dbfview 持怀疑态度，您可以尝试使用 shpdump 和 dbfdump 来自 Shapelib 的实用程序。该软件使用与 GDAL/OGR 中使用的完全相同的 C 代码来处理 Shapefile 数据。

First, you don't specify what version of GDAL/OGR you use, so it may be hard to diagnose where is the problem. Note, improvements are being applied from version to version.

Yes, you are correct values 11 and 80 for integer width and char width respectively, are default values set by OGR driver for ESRI Shapefile

The problem is that you are using SQL query to fetch only some of the rows form table
and this causes OGR PG driver driver does not preserves field widths.

Compare these two outputs:

Query the whole table

$ ogrinfo PG:dbname=test test_ogr
Layer name: test_ogr
Geometry: Point
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
FID Column = id
Geometry Column = geom
n3: String (60.0)
n1: Integer (8.0)
n2: Integer (2.0)
OGRFeature(test_ogr):1
  n3 (String) = abcdefg
  n1 (Integer) = 12345678
  n2 (Integer) = 12
  POINT (1 2)

Same query but with manually specified SQL select

$ ogrinfo PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
Geometry Column = geom
n1: Real (0.0)
n2: Real (0.0)
n3: String (0.0)
OGRFeature(sql_statement):0
  n1 (Real) = 12345678
  n2 (Real) = 12
  n3 (String) = abcdefg
  POINT (1 2)

As you can see, the second option looses field properties completely and it is how OGR works.

Here is the same example of two outputs, but with translation to Shapefile:

Dumping all data from test_ogr table

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test test_ogr
$ dbfdump -h test.dbf
Field 0: Type=C/String, Title=`n3', Width=60, Decimals=0
Field 1: Type=N/Integer, Title=`n1', Width=8, Decimals=0
Field 2: Type=N/Integer, Title=`n2', Width=2, Decimals=0
n3                                                                 n1 n2
abcdefg                                                      12345678 12

With use of -sql option and SELECT query:

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
$ dbfdump -h test.dbf
Field 0: Type=N/Double, Title=`n1', Width=24, Decimals=15
Field 1: Type=N/Double, Title=`n2', Width=24, Decimals=15
Field 2: Type=C/String, Title=`n3', Width=80, Decimals=0
                      n1                       n2 n3
12345678.000000000000000       12.000000000000000 abcdefg

So, in order to preserve all properties exactly, you need to dump all table without specifying SQL query.

By the way, if you are suspicious regardig dbfview, you may try to examine your output Shapefile files (.shp, .dbf) with shpdump and dbfdump utilities from Shapelib. This software uses exactly the same C code which is used in GDAL/OGR to handle Shapefile data.

回复收藏 0 原文

~没有更多了~