[Gdal-dev] ogr2ogr export from postgis to esri incorrectly exports
attributes
Dan Moore
mooreds at mooreds.com
Wed Feb 14 12:08:12 EST 2007
Hi Ethan,
Thanks for the pointer to that doc.
Since the ESRI driver only supports "Only Integer, Real and String field
types," does that mean that date types are not supported? If so, should
I just map those date types to strings with a view? I see some of the
date data (for example, contractdate) in the shapefile output.
I also verified that all of the data is contained in the field (ie,
there are no addresses longer than 100 characters).
The document says "Attribute names can only be up to 12 characters long.
Longer names will be silently truncated. This may result in non-unique
column names, which will definitely cause problems later."
However, I'm seeing attribute names truncated at 10 characters. I did
have some columns that were not unique in the first 10 characters, so I
renamed them, but I still see shifted and missing data.
I've attached a zip file containing a shapefile with the erroneous
output I'm seeing.
The issue begins right after garagedesc. Since ogr2ogr exports the
fields in alphabetic order, I looked at both garagedesc and heatsystem
in light of the document Ethan mentioned. I couldn't see anything
incorrect.
Here's the table I'm trying to export as an esri file.
db=> \d real_estate_data
Table "public.real_estate_data"
Column | Type | Modifiers
--------------------+-------------------------+-----------
dataset_id | integer | not null
latitude | real | not null
longitude | real | not null
precision | character varying(100) |
address | character varying(100) |
city | character varying(100) |
state | character varying(2) |
zip | character varying(10) |
subdivision | character varying(200) |
mlsnumber | character varying(20) |
liststatus | character varying(20) |
baths | character varying(4) |
bedrooms | character varying(4) |
sqfttotal | integer |
level | character varying(100) |
privatepool | character varying(1) |
remarks | character varying(2000) |
garagedesc | character varying(500) |
lotsize | character varying(100) |
coolsystem | character varying(200) |
heatsystem | character varying(200) |
construction | character varying(200) |
yearbuilt | integer |
elemschdist | integer |
highschdist | integer |
schoolelem | character varying(100) |
schoolhigh | character varying(100) |
schooljunior | character varying(100) |
listdate | date |
contractdate | date |
closeddate | date |
offmarketdate | date |
daysonmarket | integer |
listpriceorig | integer |
listprice | integer | not null
salesprice | integer |
percentoflistprice | real |
the_geom | geometry |
Thanks,
Dan
Ethan Alpert wrote:
> Dan,
>
> It may be helpful to post the schema. You're dumping from. I have in the
> past created views with data type casts in order to produce shapefiles
> with the values I expect.
>
> See the docs on the shapefile driver. There's a section that begins:
>
> "Shapefile feature attributes are stored in an associated .dbf file, and
> so attributes suffer a number of limitations:"
>
> -e
>
>
> -----Original Message-----
> From: gdal-dev-bounces at lists.maptools.org
> [mailto:gdal-dev-bounces at lists.maptools.org] On Behalf Of Dan Moore
> Sent: Saturday, February 10, 2007 6:06 PM
> To: gdal-dev at lists.maptools.org
> Subject: [Gdal-dev] ogr2ogr export from postgis to esri incorrectly
> exportsattributes
>
> Hi folks,
>
> First off, I'm pretty new to this toolset, so please forgive me if I use
>
> incorrect terminology.
>
> I'm using ogr2ogr to turn some data in a PostGIS database (8.1.4) into
> an ESRI shapefile. The data has about 25 fields, and when I open up the
>
> dbf file in excel, they are exported incorrectly. Lat/lngs are exported
>
> correctly, but the other attributes are not. All the values are there,
> but they are offset. Some are smashed together. This is also apparent
> when I view the exported ESRI file in QGIS (0.8).
>
> For example, I'll have fields (| separates fields)
> lat|lng|address|beds|bath|garage|heating|remarks|listprice
> and the values don't align:
> 2.3|3.4|222 Canyon Blvd|2|2|||nice garagecentral heating||remarks...|123
>
> Here you can see that the garage and heating sections were smashed
> together, and some extra fields were put in before the garage data.
> This happens after the twelfth column in the export. The last few
> fields are not in the export (they're pushed off by the blank columns).
>
> This happens whether or not I have a where clause on this export. I
> also tried exporting a few other tables in this database, and the
> attribute shifting didn't happen to either one. Neither of those tables
>
> had as many columns as this one, however.
>
> Here's the output of the command when I run it with --debug on:
> $ ogr2ogr --debug on -where " dataset_id = 25 " -f "ESRI Shapefile"
> out.shp PG:"user=user dbname=db password=pass" data
> OGR_PG: DBName="db"
> OGR_PG: PostSIS version string: '1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1'
> -> '1.1'
> OGR_PG: POSTGIS_VERSION=1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> OGR_PG: Layer 'data' geometry type: POINT:Point, Dim=2
> OGR_PG: Layer 'roads' geometry type: MULTILINESTRING:Multi Line String,
> Dim=2
> OGR: OGROpen(PG:user=user dbname=db password=pass/xxxxx) succeeded as
> PostgreSQL.
> OGR_PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT
> AsEWKT("the_geom"), "address", "baths", "bedrooms", "city",
> "closeddate", "construction", "contractdate", "coolsystem",
> "dataset_id", "daysonmarket", "garagedesc", "heatsystem", "latitude",
> "level", "listdate", "listprice", "listpriceorig", "liststatus",
> "longitude", "lotsize", "mlsnumber", "offmarketdate",
> "percentoflistprice", "precision", "privatepool", "remarks",
> "salesprice", "schooldistrictelem", "schooldistricthigh", "schoolelem",
> "schoolhigh", "schooljunior", "sqfttotal", "state", "subdivision",
> "yearbuilt", "zip" FROM "data" WHERE dataset_id = 25 )
> PG: 11157 features read on layer 'data'.
>
> However, an export to mapinfo seems to work (the attributes are all
> there), and an ogr2ogr from the exported mapinfo file to an ESRI file
> seems to preserve the attributes. So I can use this workaround, but
> would prefer to understand the real problem. (As an aside, the mapinfo
> export gives this error message:
> ERROR 1: IMapInfoFile::CreateField() called with unsupported field type
> 9. Note that Mapinfo files don't support list field types.)
>
> I googled against this list, but didn't find anything that referenced
> this particular problem.
>
> I couldn't figure out the version of ogr2ogr, but this is at the bottom
> of the man page:
> GDAL 13 Apr 2006 ogr2ogr(1)
>
> Has anyone seen such behavior? Can you recommend any other debugging
> steps (can I turn on debugging more)? Removing columns and seeing what
> happens? Should I try to upgrade the GDAL tools? Any other suggestions
> are welcome.
>
> Thanks,
> Dan
>
> _______________________________________________
> Gdal-dev mailing list
> Gdal-dev at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/gdal-dev
>
>
--
Regards,
Dan Moore
Moore Consulting
mooreds at mooreds.com
http://www.mooreds.com/weblog/
720 560 8545
-------------- next part --------------
A non-text attachment was scrubbed...
Name: badexport.zip
Type: application/x-zip-compressed
Size: 1681 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/gdal-dev/attachments/20070214/237db6ed/badexport.bin
More information about the Gdal-dev
mailing list