[OSM-dev] query to the database matching ids without certain key

[Jon Burgess]

On Sun, 2009-08-09 at 21:27 +0200, Stephan Knauss wrote:
> I imported the osm data into postgres.
> 
> How would I select all nodes from the database which do not have a 
> certain attribute? The last time I worked with SQL is long ago, I could 
> need a bit help.
> 
> I want to get for example all airport nodes that miss a name.
> 
> I can select all airports:
> select node_id from node_tags where v='aerodrome'
> 
> I can get all names:
> select v as name from node_tags as name, (select node_id from node_tags 
> where v='aerodrome') as airports
> where name.node_id=airports.node_id and name.k='name'
> 
> But how would I select all node_id for which there is no name?
> 
> Some help would be great...

The Mapnik format DB created using osm2pgsql can answer questions like
this easily:

gis=> select count(*) from planet_osm_point where aeroway='aerodrome' and name is null;
 count
-------
   454
(1 row)

A list containing all the node IDs is attached.

Going back to your original question. I don't have a local DB to test
with but I suspect you can do something using a form like: 

$ select name.node_id,count(*) as num from node_tags as name, (select
node_id from node_tags where v='aerodrome') as airports where
name.node_id=airports.node_id and name.k='name' group by name.node_id
having num=0;

	Jon


-------------- next part --------------
  osm_id   
-----------
  91537139
 269267837
 263621728
 343650051
 373757361
 415505250
 415505251
 413767666
 305605119
 296375488
 293674291
 345384329
 322562064
  27080773
 247113976
 306675354
 321424592
 321441530
 278794890
 316478237
 284007512
 259700361
 368308953
 368309186
 321497448
 290802028
  34103109
 339718763
 224636540
 249475516
 333688217
 343305297
 249478849
 319547892
 249444446
 319530925
 333655196
 333684726
 319512929
 321710158
 256153601
 293647540
 318406040
 250887046
 343316434
 319066811
 318868910
 327179366
 320200697
 336188435
 289658640
 340340607
 417916897
 289433481
 289850202
 289662260
 335650494
 342744060
 320655491
 343151679
 321174411
 243850405
 250381056
 319070757
 330521803
 281198940
 310482657
 330429396
 277933114
 434380328
 329343900
 351142556
 321543529
 324266982
 341887328
 324264554
 253988398
 288824594
 301987125
 288175151
 332063019
 288892873
 288161267
 288818549
 288152359
 287804092
 275016365
 288084286
  48978257
  49022970
  49022945
 288705071
 294918185
 288833389
 288124318
 428959245
 333930474
 318464755
 334181996
  26121200
 288106911
 264575705
 444956357
 130242313
 262123983
 287829253
 262268329
 262271093
  26608053
 262272715
 262273207
 288111985
 277879765
 288178361
 292528508
 262125851
 262391895
 456383924
  36294088
  36296708
 287830962
 122310798
 308051268
 291772813
 278542161
 287831177
 333056326
 255389325
 287832406
 320799460
 288111912
 281208095
  83663898
 288143910
 369681350
 270933697
 392308042
  33116915
 368333687
 288164297
 413973965
 345754349
 260700841
 303037583
 277200288
  60217955
 264092976
 255939749
 252558798
 315547657
 297902601
 279134938
 108386460
 282509181
 275885991
 236872295
  78038424
 256077722
 246155269
 419325445
 310585884
 262653195
 290709535
 287531159
 419494381
 279201176
 255322000
 287533173
 287551916
 277328690
 252501078
 252749094
 115926055
 263141317
 331390142
  52302054
 276739981
 331397292
  31371043
  26260626
 457356737
 356913898
 244589737
 365475784
 288466103
 262116501
 194129673
 279010071
 288731502
 350471122
 305740416
  84954261
  92289838
  60639075
 251390576
 272000355
 315233756
  21290215
 330250723
 330250164
 252910635
 315572700
 206358215
 288489773
 277355009
 293805746
 243047495
 270676433
 277402508
  34664242
 286541737
 288491566
 288494126
 277407942
  84425325
 435896046
 277522564
 315794675
 331412205
 309369585
 435960132
 257137356
 287604354
 358324649
 274684124
 436097035
 255539725
 435748208
 255540703
 287609963
 355602904
 268593635
 287610709
 288707879
 288847638
  40827210
 269659913
 310987228
 435989999
 253192475
 262091650
 262095987
 244220693
 303015909
 421281112
 421279618
 335690933
 335127212
 271000173
 270873394
 270507528
 270882768
 262111177
 248150347
 260796150
 248149366
 332526161
 294769007
 276634179
 287040132
 288998921
 339344780
 248503701
 348057063
 248206228
 286984190
 287690031
 344626566
 287798349
 313556153
 348261387
 253736552
 313212067
 348263148
 343702015
 348110203
 348269109
 296954931
 287589520
 348050973
 296848662
 348278994
 348051227
 339068220
 244319645
 253058236
 387796181
 297227347
 253076445
 348035370
 349026795
 300885441
 346787309
 289034683
 253714989
 332379410
 247302404
 247254519
 248950580
 307984384
 254484625
 279305581
 307059232
 246961311
 311100312
  81458233
 245013884
 244879257
 332399986
 271290441
 304250882
 299017809
 278746547
 278746628
 298985410
 273819916
 298953655
 166602624
 254373221
 297350088
 241873366
 256200646
 242155561
 251006897
 290263651
 290250911
 243712176
 272200405
 299463407
 242941425
 243171732
 243055479
 273335279
 243465340
 273071186
 297763134
 279552863
 244201604
 273472488
 280404934
 272748564
 317785705
 288396169
 303481276
 293915475
 273141569
 286084950
 431010938
  33072260
 291559678
 308452155
 285878271
 285914578
 317756656
 306090567
 346155959
 346838173
 264025328
 286130670
 286928423
 293970833
 300686793
 300961352
 457830023
 298725708
 297740616
 360835513
 346873828
  95730175
 293930090
 297231498
 288457554
 347579063
 288433401
 293940413
 344843965
 344843955
 273990929
 403754451
 273989712
 301790125
 301773602
 344842540
 250243357
 300802690
 279997679
 152600305
 278297319
 278299873
 430725466
 428885838
 301776748
 369915335
 427865507
 428120136
 278378671
 347653502
 348090630
 317943944
 347651749
 348087565
 343543382
 306674089
 343537118
 343536824
 343413198
 343410594
 343415116
 299625044
 264066883
 264066037
 264054972
 264065831
 264054378
 264055468
 346660476
 264059181
 264064788
 343234836
 264064067
 264059282
 301822916
 278394069
 264053338
 264060096
 264053261
 245449859
 264052261
 264060373
 304758394
 283554950
  87177837
 283571026
 279776528
 279781344
 279784662
 413888598
 415142658
 316760239
 280186383
 281895655
  29405243
 361247035
 339729805
  87143393
 436931197
 273096534
  32033246
 442301658
 443203055
 283539640
 443716331
 444273019
 134286617
(454 rows)