[Gdal-dev] Re: joining DBFs
Neil Best
nbest at speakeasy.net
Fri Sep 15 12:25:35 EDT 2006
Am I barking up the wrong tree? Is ogr2ogr supposed to be able to take
a .shp and a .dbf of a different name and join them one-to-one by a
given key? Examples such as
http://article.gmane.org/gmane.comp.gis.mapserver.user/8278 suggest so.
I have been following the examples on the OGR SQL page, but the fact
that there are no ogr2ogr examples there worries me that the utility
does not support this. Aside from the seg faults, ogr2ogr keeps
complaining that it is unable to open name_of_dbf.shp or
name_of_dbf.SHP. It's not a shapefile!
The latest iteration of my command:
neil at gandalf:~/gis/kb$ ogr2ogr -sql "SELECT * FROM mpo2 LEFT JOIN
'ses/2000HH_poverty_UNIVERSAL.dbf'.2000HH_poverty_UNIVERSAL pov ON
mpo2.geo_identi=pov.geo_identi" -nln mpo2_poverty -s_srs EPSG:26771 ses ses
ERROR 4: Unable to open ses/TAZ_SocioEcon_IN_r1.shp or
ses/TAZ_SocioEcon_IN_r1.SHP.
ERROR 4: Unable to open ses/2000HH_poverty_UNIVERSAL.shp or
ses/2000HH_poverty_UNIVERSAL.SHP.
ERROR 4: Unable to open ses/TAZ_SocioEcon_IN_r1.shp or
ses/TAZ_SocioEcon_IN_r1.SHP.
ERROR 4: Unable to open ses/2000HH_poverty_UNIVERSAL.shp or
ses/2000HH_poverty_UNIVERSAL.SHP.
ERROR 4: Unable to open ses/2000HH_poverty_UNIVERSAL.shp or
ses/2000HH_poverty_UNIVERSAL.SHP.
Segmentation fault
I tried cp'ing my dbf to pov.dbf in case the filename was causing
problems, so the alias became the literal -- same result.
I tried what I thought would be a trivial join by renaming the .dbf from
a shapefile and joining them back together by FID. Is this a valid test?
Originally I was using gdal 1.3.2 compiled from source on my Ubuntu
Dapper system, then I dropped back to the 1.2.6 package from the repos
-- same behavior, seg faults.
I have seen other mention of seg faults or other problems when working
with .DBFs and .SHPs in my web searches. Whether my troubles are
realted to others', I can't be sure.
http://article.gmane.org/gmane.comp.gis.gdal.devel/8595
http://article.gmane.org/gmane.comp.gis.gdal.devel/8603
http://article.gmane.org/gmane.comp.gis.grass.user/11735
http://bugzilla.remotesensing.org/show_bug.cgi?id=641
Anyone still reading? Please let me know if at least I am asking a
valid question and composing appropriate articles. Thanks.
Neil Best wrote:
> Something is going wrong. I have created indexes on both tables even
> though it wasn't clear if that is strictly necessary for the primary
> table. Having an unknown SRS wouldn't cause this, would it? I should
> just look up my EPSG -- that's what I'll do next. It appears to do
> something before seg-faulting -- maybe there is a corrupt polygon?
>
> I see that the key of the secondary table also gets added as a column,
> which Arc also does in a join operation. I find this unnecessary and
> annoying. Is there an argument in favor? I guess it can be dropped
> easily enough, but notice that DBF truncates that qualified column name.
>
> Also, there appears to be a problem with the -overwrite option that I
> show at the end of my output that I don't understand, requiring an rm
> step. Is this a limitation of the SHP driver or something I am doing
> wrong?
>
> I thought better of attaching my data. Drop me a line if you would like
> to have a look at it. Thanks for any insight.
>
>
> neil at gandalf:~/gis/kb$ ogr2ogr -sql "SELECT * FROM mpo2 LEFT JOIN
> 2000HH_poverty_UNIVERSAL ON
> geo_identi=2000HH_poverty_UNIVERSAL.geo_identi" -nln mpo2_poverty test ses
> Segmentation fault
> neil at gandalf:~/gis/kb$ ogrinfo test mpo2_poverty INFO: Open of `test'
> using driver `ESRI Shapefile' successful.
>
> Layer name: mpo2_poverty
> Geometry: Polygon
> Feature Count: 0
> Extent: (0.000000, 0.000000) - (0.000000, 0.000000)
> Layer SRS WKT:
> (unknown)
> SHAPECODE: String (16.0)
> ZONE_AREA: Real (16.0)
> OID_: Integer (9.0)
> GEO_IDENTI: String (14.0)
> TOTPOP: Real (10.2)
> ONE_RACE: Real (10.2)
> WHITE: Real (10.2)
> BLACK: Real (10.2)
> AMERINDIAN: Real (10.2)
> ASIAN: Real (10.2)
> PACIFIC: Real (10.2)
> OTHER: Real (10.2)
> TWO_PLUS_R: Real (10.2)
> HISPANIC: Real (10.2)
> NOT_HISPAN: Real (10.2)
> NHISP_WHIT: Real (10.2)
> 2000HH_pov: String (14.0)
> HOUSEHOLDS: Real (8.2)
> HOUSEHOL_A: Real (8.2)
> neil at gandalf:~/gis/kb$ ls test
> mpo2_poverty.dbf mpo2_poverty.shp mpo2_poverty.shx
> neil at gandalf:~/gis/kb$ ogrinfo -sql "SELECT * FROM mpo2 LEFT JOIN
> 2000HH_poverty_UNIVERSAL ON
> geo_identi=2000HH_poverty_UNIVERSAL.geo_identi" ses INFO: Open of `ses'
> using driver `ESRI Shapefile' successful.
>
> Layer name: mpo2
> Geometry: Polygon
> Feature Count: 17466
> Extent: (211950.731904, 1576286.159537) - (885960.120408, 2252334.267779)
> Layer SRS WKT:
> (unknown)
> SHAPECODE: String (16.0)
> ZONE_AREA: Real (16.0)
> OID_: Integer (9.0)
> GEO_IDENTI: String (14.0)
> TOTPOP: Real (10.2)
> ONE_RACE: Real (10.2)
> WHITE: Real (10.2)
> BLACK: Real (10.2)
> AMERINDIAN: Real (10.2)
> ASIAN: Real (10.2)
> PACIFIC: Real (10.2)
> OTHER: Real (10.2)
> TWO_PLUS_R: Real (10.2)
> HISPANIC: Real (10.2)
> NOT_HISPAN: Real (10.2)
> NHISP_WHIT: Real (10.2)
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI: String (14.0)
> HOUSEHOLDS: Real (8.2)
> HOUSEHOL_A: Real (8.2)
> OGRFeature(mpo2):0
> SHAPECODE (String) = 4610051
> ZONE_AREA (Real) = 415737
> OID_ (Integer) = 17100
> GEO_IDENTI (String) = 4610051
> TOTPOP (Real) = 26.73
> ONE_RACE (Real) = 26.73
> WHITE (Real) = 26.34
> BLACK (Real) = 0.10
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.29
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 26.73
> NHISP_WHIT (Real) = 26.34
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610051
> HOUSEHOLDS (Real) = 8.75
> HOUSEHOL_A (Real) = 0.42
> POLYGON ((565168.49996185512282
> 2123297.036226258147508,567837.380521122599021
> 2123311.42119784001261,567836.905601977137849
> 2123150.250083384104073,567836.875 2123150.25,566502.812442400609143
> 2123148.437627284321934,565168.658338983776048
> 2123146.625130200758576,565168.49996185512282 2123297.036226258147508))
>
> OGRFeature(mpo2):1
> SHAPECODE (String) = 4610042
> ZONE_AREA (Real) = 405230
> OID_ (Integer) = 17097
> GEO_IDENTI (String) = 4610042
> TOTPOP (Real) = 12.42
> ONE_RACE (Real) = 12.42
> WHITE (Real) = 12.30
> BLACK (Real) = 0.00
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.11
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 12.42
> NHISP_WHIT (Real) = 12.30
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610042
> HOUSEHOLDS (Real) = 6.87
> HOUSEHOL_A (Real) = 0.33
> POLYGON ((567837.380521122599021
> 2123311.42119784001261,570497.874934128252789
> 2123300.937598436605185,570497.723520182771608
> 2123157.500268447212875,569167.374749097623862
> 2123153.875339915510267,567836.905601977137849
> 2123150.250083384104073,567837.380521122599021 2123311.42119784001261))
>
> OGRFeature(mpo2):2
> SHAPECODE (String) = 4610041
> ZONE_AREA (Real) = 358029
> OID_ (Integer) = 17096
> GEO_IDENTI (String) = 4610041
> TOTPOP (Real) = 0.48
> ONE_RACE (Real) = 0.48
> WHITE (Real) = 0.48
> BLACK (Real) = 0.00
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 0.48
> NHISP_WHIT (Real) = 0.48
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610041
> HOUSEHOLDS (Real) = 5.00
> HOUSEHOL_A (Real) = 0.24
> POLYGON ((570497.874934128252789
> 2123300.937598436605185,573158.271560193505138
> 2123290.454384359996766,573158.381680462392978
> 2123164.750001248437911,573158.375 2123164.75,571828.062485220958479
> 2123161.125170259270817,570497.723520182771608
> 2123157.500268447212875,570497.874934128252789 2123300.937598436605185))
>
> OGRFeature(mpo2):3
> SHAPECODE (String) = 4610052
> ZONE_AREA (Real) = 387044
> OID_ (Integer) = 17101
> GEO_IDENTI (String) = 4610052
> TOTPOP (Real) = 12.21
> ONE_RACE (Real) = 12.21
> WHITE (Real) = 12.06
> BLACK (Real) = 0.14
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.02
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.21
> NOT_HISPAN (Real) = 12.00
> NHISP_WHIT (Real) = 11.84
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610052
> HOUSEHOLDS (Real) = 11.30
> HOUSEHOL_A (Real) = 0.67
> POLYGON ((562499.551199327223003
> 2123282.650887067895383,565168.49996185512282
> 2123297.036226258147508,565168.658338983776048
> 2123146.625130200758576,563834.437308961409144
> 2123144.812542194500566,562500.262920987908728
> 2123143.000017553567886,562499.551199327223003 2123282.650887067895383))
>
> OGRFeature(mpo2):4
> SHAPECODE (String) = 4610032
> ZONE_AREA (Real) = 301133
> OID_ (Integer) = 17093
> GEO_IDENTI (String) = 4610032
> TOTPOP (Real) = 0.56
> ONE_RACE (Real) = 0.56
> WHITE (Real) = 0.56
> BLACK (Real) = 0.00
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 0.56
> NHISP_WHIT (Real) = 0.56
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610032
> HOUSEHOLDS (Real) = 0.47
> HOUSEHOL_A (Real) = 0.04
> POLYGON ((573158.271560193505138
> 2123290.454384359996766,575833.499946153140627
> 2123264.680594602134079,575833.422804853646085
> 2123165.249997253529727,574495.937499998952262
> 2123165.000005841255188,573158.381680462392978
> 2123164.750001248437911,573158.271560193505138 2123290.454384359996766))
>
> OGRFeature(mpo2):5
> SHAPECODE (String) = 4610061
> ZONE_AREA (Real) = 373248
> OID_ (Integer) = 17104
> GEO_IDENTI (String) = 4610061
> TOTPOP (Real) = 3.76
> ONE_RACE (Real) = 3.70
> WHITE (Real) = 3.59
> BLACK (Real) = 0.01
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.04
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.06
> TWO_PLUS_R (Real) = 0.06
> HISPANIC (Real) = 0.12
> NOT_HISPAN (Real) = 3.64
> NHISP_WHIT (Real) = 3.53
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610061
> HOUSEHOLDS (Real) = 12.52
> HOUSEHOL_A (Real) = 0.80
> POLYGON ((559860.124304226133972
> 2123272.137048966716975,562499.551199327223003
> 2123282.650887067895383,562500.262920987908728
> 2123143.000017553567886,562500.25 2123143.0,561180.563844703836367
> 2123135.984603217802942,559860.851143212290481
> 2123128.969065317418426,559860.124304226133972 2123272.137048966716975))
>
> OGRFeature(mpo2):6
> SHAPECODE (String) = 4610062
> ZONE_AREA (Real) = 387825
> OID_ (Integer) = 17105
> GEO_IDENTI (String) = 4610062
> TOTPOP (Real) = 3.46
> ONE_RACE (Real) = 3.46
> WHITE (Real) = 3.46
> BLACK (Real) = 0.00
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 3.46
> NHISP_WHIT (Real) = 3.46
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610062
> HOUSEHOLDS (Real) = 5.65
> HOUSEHOL_A (Real) = 0.19
> POLYGON ((557183.877244946546853
> 2123261.476542191114277,559860.124304226133972
> 2123272.137048966716975,559860.851143212290481
> 2123128.969065317418426,558541.187734182458371
> 2123121.953789453487843,557186.092565013910644
> 2123114.75015982426703,557185.866814971901476
> 2123129.702169452793896,557183.877244946546853 2123261.476542191114277))
>
> OGRFeature(mpo2):7
> SHAPECODE (String) = 4610031
> ZONE_AREA (Real) = 230844
> OID_ (Integer) = 17092
> GEO_IDENTI (String) = 4610031
> TOTPOP (Real) = 0.07
> ONE_RACE (Real) = 0.07
> WHITE (Real) = 0.07
> BLACK (Real) = 0.00
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.00
> TWO_PLUS_R (Real) = 0.00
> HISPANIC (Real) = 0.00
> NOT_HISPAN (Real) = 0.07
> NHISP_WHIT (Real) = 0.07
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4610031
> HOUSEHOLDS (Real) = 0.07
> HOUSEHOL_A (Real) = 0.01
> POLYGON ((575833.499946153140627
> 2123264.680594602134079,578508.610308142262511
> 2123238.907941915560514,578508.432067663408816
> 2123165.749987302813679,577170.9375 2123165.5,575833.422804853646085
> 2123165.249997253529727,575833.499946153140627 2123264.680594602134079))
>
> OGRFeature(mpo2):8
> SHAPECODE (String) = 4609011
> ZONE_AREA (Real) = 229076
> OID_ (Integer) = 16955
> GEO_IDENTI (String) = 4609011
> TOTPOP (Real) = 6.63
> ONE_RACE (Real) = 6.44
> WHITE (Real) = 6.36
> BLACK (Real) = 0.02
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 0.06
> TWO_PLUS_R (Real) = 0.19
> HISPANIC (Real) = 0.04
> NOT_HISPAN (Real) = 6.59
> NHISP_WHIT (Real) = 6.34
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4609011
> HOUSEHOLDS (Real) = 2.55
> HOUSEHOL_A (Real) = 0.15
> POLYGON ((554590.91588362515904
> 2123241.632391711231321,557183.877244946546853
> 2123261.476542191114277,557185.866814971901476
> 2123129.702169452793896,555888.410371425678022
> 2123163.290501976385713,554584.285233602509834
> 2123197.051472527906299,554590.91588362515904 2123241.632391711231321))
>
> OGRFeature(mpo2):9
> SHAPECODE (String) = 4609014
> ZONE_AREA (Real) = 6951748
> OID_ (Integer) = 16957
> GEO_IDENTI (String) = 4609014
> TOTPOP (Real) = 155.95
> ONE_RACE (Real) = 152.29
> WHITE (Real) = 150.60
> BLACK (Real) = 0.42
> AMERINDIAN (Real) = 0.00
> ASIAN (Real) = 0.00
> PACIFIC (Real) = 0.00
> OTHER (Real) = 1.27
> TWO_PLUS_R (Real) = 3.66
> HISPANIC (Real) = 1.66
> NOT_HISPAN (Real) = 154.29
> NHISP_WHIT (Real) = 150.18
> 2000HH_poverty_UNIVERSAL.GEO_IDENTI (String) = 4609014
> HOUSEHOLDS (Real) = 66.53
> HOUSEHOL_A (Real) = 5.12
> POLYGON ((551953.502148762461729
> 2123221.448043493088335,554590.91588362515904
> 2123241.632391711231321,554584.285233602509834
> 2123197.051472527906299,554600.954803448054008
> 2121870.175678761675954,554617.624373293714598
> 2120543.29988499591127,553313.499235470429994
> 2120577.060855546966195,552009.364189005456865
> 2120610.822082611266524,551980.995648902608082
> 2121936.581881972495466,551980.965271481196396
> 2121938.001523731742054,551953.502148762461729 2123221.448043493088335))
>
> Segmentation fault
>
> neil at gandalf:~/gis/kb$ ogr2ogr -sql "SELECT * FROM mpo2 LEFT JOIN
> 2000HH_poverty_UNIVERSAL ON
> geo_identi=2000HH_poverty_UNIVERSAL.geo_identi" -nln mpo2_poverty test ses
> FAILED: Layer mpo2_poverty already exists, and -append not specified.
> Consider using -append, or -overwrite.
> ERROR 1: Terminating translation prematurely after failed
> translation from sql statement.
> neil at gandalf:~/gis/kb$ ogr2ogr -overwrite -sql "SELECT * FROM mpo2 LEFT
> JOIN 2000HH_poverty_UNIVERSAL ON
> geo_identi=2000HH_poverty_UNIVERSAL.geo_identi" -nln mpo2_poverty test ses
> ERROR 6: DeleteLayer() not supported by this data source.
> DeleteLayer() failed when overwrite requested.
> ERROR 1: Terminating translation prematurely after failed
> translation from sql statement.
>
> Neil Best wrote:
>> Sorry, I just looked at the OGR SQL page again for the first time in a
>> while. Is the JOIN support new? Maybe I just conflated the GRASS and
>> OGR SQL features. This looks like it will do the trick. As penance I
>> will submit my query as an example.
>>
>> Neil
>>
>>
>> Neil Best wrote:
>>> Can someone point me to a straightforward way to join attributes from
>>> an additional dbf table using either OGR or GRASS, or even Perl?
>>> It's so easy in Arc that I would think this has been solved in our
>>> world also. So far my impression is that I would have to script a
>>> row-by-row UPDATE since the SQL drivers don't support any type of
>>> JOIN afaik. It seems like I have seen an example somewhere using a
>>> Perl DBF module but I can't put my finger on it. I'll keep digging
>>> and hopefully this wheel has been invented.
>>>
>>> Neil
>>>
More information about the Gdal-dev
mailing list