[Gdal-dev] Re: joining DBFs
Neil Best
nbest at speakeasy.net
Mon Sep 11 11:28:19 EDT 2006
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