Spatial Performance

[ChrisHunt]

I'm building a system which stores Geographical data, and trying to use the spatial functions in the Oracle database. Amongst the tables there are:

blpu - one row per property in the county, each row has a spatial point object indicating its position. Has 480,000 rows.

lcc_boundary - one row per region in the county (parishes, wards, districts, etc.), each row has a spatial polygon indicating its boundary. Has 537 rows.

Now, if I want to determine which parish (say) a given property is in, I do this:

SELECT b.uprn,
       lb.name area
FROM   blpu b,
       osmm.lcc_boundary lb
WHERE  MDSYS.SDO_GEOM.RELATE(lb.geoloc,
                             'CONTAINS',
                             b.geometry,
                             0.005) = 'CONTAINS'
AND    lb.area_code = 'CPC'
AND    b.uprn = 100030723225

So far so good - it comes back with the right answer - but it takes 1 second to do so. If I changed the last line to bring back 100 properties, it takes about 100 seconds. Clearly performance is going to be extremely sluggish when doing real queries.

Is there anything I can do - e.g. indexes that can be built - that will speed up these spatial functions; or am I just going to have to "denormalise" the spatial data into a table that cross-references each row in blpu with the rows in lcc_boundaries that enclose it?

I find it hard to believe that Oracle would be selling a product that's so unusably slow.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd