[Mapserver-users] OGR Join index

[Richard Greenwood]

Frank Warmerdam wrote:

> Richard Greenwood wrote:
> 
>> Very interesting! So I could use MapInfo to create indexes and OGR 
>> would  use them? And if I wanted to use ORG's CREATE INDEX I would 
>> need to write a C or Python wrapper? i.e. There is no stand alone 
>> CREATE INDEX utility?
> 
> 
> Richard,
> 
> Well, if you create the indexes with MapInfo, they won't be recognised 
> because
> there needs to be a little xml file that associates them.
> 
> You should create the indexes with the OGR "CREATE INDEX" command.  This
> can be accomplished with the ogrinfo utility which includes the ability
> to execute arbitrary OGR SQL.
> 
> eg.
> 
> warmerda at gdal2200[214]% ogrinfo -ro . -sql "create index on polygon 
> using eas_"
> INFO: Open of `.'
> using driver `ESRI Shapefile' successful.
> warmerda at gdal2200[215]% ls -la
> total 368
> drwxr-xr-x    2 warmerda users        4096 Jun 18 16:34 .
> drwxr-xr-x   53 warmerda users        4096 Jun 18 16:32 ..
> -rw-r--r--    1 warmerda users      159752 Jun 18 16:32 polygon.dbf
> -rw-r--r--    1 warmerda users         221 Jun 18 16:34 polygon.idm
> -rw-r--r--    1 warmerda users        8704 Jun 18 16:34 polygon.ind
> -rw-r--r--    1 warmerda users      175784 Jun 18 16:32 polygon.shp
> -rw-r--r--    1 warmerda users        3892 Jun 18 16:32 polygon.shx
> 
> Now that I think about it some more, I'm pretty certain that OGR doesn't 
> even
> take advantage of the indexes for mapfinfo format ... only for 
> shapefiles right
> now.  The client in question needed it only for shapefiles.
> 
> Best regards,

Totally cool! The indexes reduced execution time from over 2 minutes to 
about 2 seconds. (Joining 12000 spatial rows to 300 attribute rows). The 
index only seemed to be required on the attribute table.

But of course, there's always one more question: I can not get ogrinfo 
to create an index on just a dbf file. It seems to require the 
associated shp and shx files also, even tho there is no spatial data 
there. Ogrinfo recognizes the dbf, but fails to create an index. e.g.

$ ogrinfo listing.DBF
   INFO: Open of `listing.DBF'
   using driver `ESRI Shapefile' successful.
   1: listing (None)

$ ogrinfo -ro . -sql "create index on listing using Pidn"
   INFO: Open of `.'
   using driver `ESRI Shapefile' successful.
   ERROR 1: CREATE INDEX ON failed, no such layer as `listing'.

I don't see what functional purpose the shp and shx files server in a 
table having no spatial data. Any insight here?

As always, thank you very much for your explanations.

Regards,
-- 
Richard Greenwood
www.greenwoodmap.com