Few Interesting Findings Today

Had a few interesting findings today when trying to do some aggregation. My end goal is to simply move a primary key from a bunch of geocoded points (geocoded to my parcels) to respective lines that touch the parcels.

My first attempt to do this was with GeoMedia Pro 6.0. This worked GREAT when working with spatial filters. However as soon as I removed the filter and tried to do the entire data sets (approx 150,000 parcels being aggregated to 225,000 lines), GeoMedia starts to crash OR the values it returns are all NULL. I’ve filed the issue as a worksheet. It’s apparently a known problem that they’re trying very hard to resolve in 6.1. Unfortunately with my beta of 6.1, it’s not yet fixed 🙁 .

So off to "Plan B". I decided to do it in Oracle using the Oracle Locator functions. To keep things more simple… at least in my mind, I decided to break this up into two steps:

1) Aggregate the geocoded points to my parcel boundary. I then created a materialized view to store the result data set. The view took maybe two or three hours to create.

2) I then aggregated of few of the attributes from the parcel boundary (from step 1) to my lines (waiting on this to complete as I write this). Again, I’m expecting this to take probably two or three hours. And again, I’m creating a materialized view to make interacting with the result much faster.

On a side note, Simon Greener wrote a FANTASTIC blog entry titled "Fast Refreshing of Oracle Materialized Views Containing SDO_GEOMETRY Columns". If you have any interest in using materialized views inside of the GeoMedia environment (or really anywhere for that matter; this is a MUST read).

Anyhow, when I first tried to do step 2, I kept getting an error about needing to create a spatial filter on my line feature. Now…it was confusing because I KNEW there was already a valid spatial index on this line feature…. So, after playing with my query some, I finally figured out the problem. Apparently you can not run spatial queries between tables that are located in two different databases. This is important to note for those looking to do this in the future.

Basically, I had created an Oracle database link to the database that contained my line feature/table. I was then referencing the linked table through my query. When doing this (at least with Oracle 10GR2), Oracle can not read the spatial index. As a result, the spatial query will not work. As a workaround, I imported the line table into the same database as my parcel table. Then…all of the sudden the query worked!

If anybody is interested, I can post the queries; just let me know.

Will be heading to BAAMA tomorrow if anybody else is going – hope to see you in Oakland, CA!