[Gdal-dev] Re: Problem using OGR and join-functionality: column names gone when creating a new shapefile?

[wqual]

Hi Neil,
thank you for your reply - and a good idea to use aliases. But first, the 
complete ogr2ogr-command that I used before:
ogr2ogr -f "ESRI Shapefile" testjoin5.shp ./ -sql "select 
flurstck_test.*, flurstck_nr1.GEMARKUNG_ from flurstck_test left join 
flurstck_nr1 on flurstck_test.OBJEKT_ID = flurstck_nr1.OBJEKT_ID"

The result was the column names as described earlier:
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_t
flurstck_n

After your mail, I used the alias "fl" for flurstck_test and "nr" for 
flurstck_nr:

wolfgang.qual at rgu04-200:~$ ogr2ogr -f "ESRI Shapefile" testjoin6.shp ./ 
-sql "select fl.*, nr.GEMARKUNG from flurstck_test fl left join 
flurstck_nr1 nr on fl.OBJEKT_ID = nr.OBJEKT_ID"

The new column names were then:
fl.OGR_FID
fl.OBJEKT_
fl.FOLIE
fl.OBJEKTA
fl.X
fl.Y
fl.NUTZUNG
fl.EIGENTU
fl.ADMPLAY
fl.FLUR_P_
nr.GEMARKU

This result looks much better! I guess there's no way to keep the original 
column names, but this is ok.

Best regards,
Wolfgang

---8<----------------------------------------------------------
Am Samstag 16 September 2006 pH:16:14 nachmittags/abends schrieb Neil Best:
> Hi, Wolfgang.  I think if you look at my recent posts to the list you
> will see that we are experiencing similar difficulties, you and I.  I
> wanted to do the same thing, make a join permanent, the only difference
> is that my source data is a .shp and a .dbf, rather than related tables
> in an RDBMS.
>
> Since I can't get it to work either I can't speak with much authority,
> but I think I can point out something about your problem with field
> names.  If you look at the section describing joins on the OGR SQL page
> you will see a detailed discussion of how column names in the SQL
> statement map to the output.  My guess is that OGR is trying to give you
> fully qualified column names (e.g. "flurstck_test.ich_weiss_was_nicht")
> and truncating them according to the .dbf conventions.  Try using some
> aliases in your SQL.  If you post your query and a minimal description
> of your columns to the list I can try to offer a suggestion.
>
> What's interesting is that we are both running the same version under
> Debian, albeit I am using Ubuntu.  I don't know if this is significant
> or not, especially since I got the same results when I dropped back to
> 1.2.6, but there it is . . .
>
>
> Neil
>
> wqual wrote:
> > Hi list,
> > yesterday, I found a very interesting page on the internet - manpage of
> > OGR SQL [1]. Wow, I thought - I can perfom JOINs on the command line.
> > Using the join-command as described in this manual combinded to the
> > ogrinfo-command clearly showed, that a join was performed.
> > *But could I make this permanent?*
> > Within the next step, I used ogr2ogr with the same sql-expression. A new
> > shapefile was created, the desired column of the second theme was
> > appended to the table (ogr2ogr -f "ESRI Shapefile" testfile.shp ./ -sql
> > "select ...."
> >
> > However, I realised, that all the column names were gone, or to be more
> > precise, lost their names in favour of a new one. All column names of the
> > first shapefile "Flurstck_test.shp" were (re)-named to "flurstck_t" and
> > the new column of the second theme "flurstck_nr.shp" is named
> > "flurstck_n" now. Also, the column definitions were all converted to
> > "Real" except the column that was appended (joined) to the table. This
> > column is of type "String".
> >
> > Is this a common (known problem)? I would be most grateful for some
> > comments, as I really would like to use this feature of ogr! Thanks in
> > advance!
> >
> > I am using gdal 1.3.2-2 on a Debian machine.
> >
> > Best regards,
> > Wolfgang
> >
> > [1] www.remotesensing.org/gdal/ogr/ogr_sql.html