GeoMedia GIS Blog

"A picture is only worth a thousand words. A map may be worth a thousand numbers. But a GIS is worth a thousand tables."

Archive for the ‘Oracle Database’ Category

Few Interesting Findings Today

Posted by jeffhobbs on July 25, 2007

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!

Posted in GeoMedia, GeoMedia 6.1 Beta, Intergraph, Oracle Database | 2 Comments »

Some SQL for the Day

Posted by jeffhobbs on July 17, 2007

Received a parcel attribute data dump for an outside party today. Needed to clean it up so it matched my internal data. There were a few issues:

1) The parcels in the dump did not have dashes in the APN Number.

2) The address number was padded with leading zeros.

Used some SQL to clean up both data differences. Thought I’d pass it long in case others run into similar problems or have similar requirements. I should note that all of the processing was done inside of the Oracle database via SQL*Plus.

1) I figured instead of adding dashes to the dump, I write a view and in the view I’d remove the dashes from my data. Made life much easier. Instead of showing the view syntax, I’ll put the code into a simple update statement:

UPDATE parcel
SET apn_number = REPLACE (apn_number, ‘-‘, ”);

With the REPLACE function I’m telling Oracle to do the following:

  • Look at my apn_number column
  • Find all of the dashes in the column
  • Replace the dashes with  "nothing" or two single quotes (”)

Fairly straightforward and very fast (and useful).

2) To eliminate the leading zeros I used the TRIM function. Here’s the update syntax:

UPDATE parcel
SET street_number = TRIM(LEADING 0 FROM my_street_number)

With the TRIM function I’m telling Oracle to do the following:

  • Look at the start (lead) of the my_street_number column
  • Find all of the zeroes (0s) at the start of the column
  • Remove all of the leading zeroes until you get to a non-zero number, then stop the process

In the end, neither SQL statement is complex, but both are very useful. It’s also interesting to note that both statement took under 30 seconds to perform. Very nice considering that there were approximately 250,000 records in both tables.

Posted in Oracle Database | 1 Comment »

Oracle Workspace Manager in Oracle XE

Posted by jeffhobbs on July 12, 2007

Have started to play around with GeoMedia Transaction Manager 6.0 some this week. I hadn’t used it since its 5.2 days when I was with Intergraph. Anyhow, moved a bunch of data into a test Oracle XE database. Then went to secure it using the GTM Administrator. Started getting errors suggesting that something was wrong with my database…. After I did some research, it turns out that Oracle Workspace Manager is NOT in Oracle XE! Wish I had known that (or done the research) before setting up the database. 😦

Anyhow, thought I’d pass it along in case others were looking to do the same.

Posted in GeoMedia, Intergraph, Oracle Database | 2 Comments »