Bulk, Automated Geomedia Editting: split a lot of polys based on a line layer

A little more than a year ago, I did a project for a left leaning political organization that had an interest in developing an automated means to create (and recreate) a national layer of election precincts to support activities like political canvasing.  Click here for a more complete case study on my company’s website.

One of the basic challenges in this project was to find an efficient way to split hundreds of thousands of polygons (census blocks) using a separate, and rather large layer of line geometry – in this case, statewide commercial street data.  Although I had the option to do this work with custom code in Oracle Spatial, I wanted to use Geomedia in order to avoid the Oracle customization time sink.  How to do it in Geomedia?  The solution is easy, but not at all obvious (at least to me).  Here is the recipe I came up with:

1. Load the feature class that you want to split (in my case, census blocks), and the features that you want to split by (in my case, streets). Also, and this is important, make sure that the “Display Properties Dialog for New Features” button is unpressed.

2. Select the census blocks and initiate the split tool in Geomedia.  Notice that a new toolbar appears with four buttons on it… split by point, split by line, split by polygon, and split by selected features.  Click on “split by selected features”.

3. Turn OFF locatability on the census blocks layer (the layer to be split).  Be sure to do this without unselecting those features.

4. Turn on locatability for the streets layer, and select the features that you want to use as a basis for splitting census blocks.

5. Double-click somewhere – anywhere – on the map window and let Geomedia do its thing.  Depending on the complexity of the features that it is analyzing and the sheer number of records, it may take a little time to complete.  If you didnt take my advice from step one and ignored the “Display Properties Dialog for New Features” button, then it might force you to click OK for every newly split feature.

The result in my case is census blocks seemlessly split based on a seperate layer of line work.  Perfect geometry, perfect topology between features, with no slivers or gaps… a thing of beauty.

Advertisement

Oracle Spatial User’s Group Conference Proceedings are Online

If you are interested, I received an email yesterday. The conference proceedings from the 2008 Oracle Spatial User’s Group are available off of this web site (http://www.oracle.com/technology/products/spatial/htdocs/spatial_conf_0803_idx.html).

GITA Attendance

Last week I attended GITA’s 2008 Geospatial Infrastructure Solutions Conference in Seattle, WA. As a whole, I was really happy with the event. I attended a couple of pre-conference knowledge immersion seminars – one titled “Strategies for Enterprisewide Web Services” and one titled “Mobile Technology for the Enterprise”. I then attended the entire conference followed by the Oracle Spatial User Conference the day after the conference. I’m not going to go into great detail about the conference as I’m sure you can find a lot online that’s been covered by other users.

I must say that Oracle has done some impressive things with Oracle 11g and their support for LiDAR data, TIN exports, and overall 3D data storage. Additionally Safe Software has done some really cool things embracing Oracle 11g. At the Oracle Spatial User Conference, Safe demonstrated extracting 3D data from 11g, and transforming the data into a 3D PDF. Once in the PDF environment, you could pan, tilt, zoom, and fly around. It was really cool to be able to do a virtual flyover both around and into a building. You can see the demo on the Safe web site. Just look for the .wmv titled “FME and Oracle Spatial”.

Anyhow…I did have some time to sit down at the Intergraph booth and talk to a few people. I’ll cover some of the things I discussed in upcoming posts.

And finally, on a complete side note. I was really surprised at the size of the booth at the GITA conference. It was really great to see such a large presence at a conference. I’m used to seeing the one small table in the corner. Not this time…. Very cool.

A Few New Purchases Towards the End of Last Year

Towards the end of last year I received quite a bit of new software and hardware. On the hardware side were a couple of new Trimble GeoXH GPS units. These can deliver sub-foot GPS points once post-processed. With time, the goal is to slowly but surely adjust our maps to be GPS quality. This will definitely take at least three years and a lot of learning. So…expect some postings on GPS usage in the future….

On the software side, I purchased the following:

1. GeoMedia WebMap (finally)
2. Oracle Spatial upgrade
3. MapText Label-Web
4. MapText Label-EZ
5. MapText Label-Edit

I’ve spent time on #1 and #2 before on my blog, but don’t think I’ve ever touched on the MapText products. So, over the next couple of days I’m going to spend some time on both products. Needless to say, if you haven’t heard of the company or its products, I HIGHLY encourage you to take a look at their web site and a few of their samples. To put it simply, MapText produces the best software on the market (without a doubt) for the labeling of maps – both on the web (via Label-Web and GeoMedia WebMap) or on paper (via Label-EZ and Label-Edit).

Tomorrow I’ll go over Label-Web and then I’ll touch on Label-EZ and Label-Edit next week.

Until then…

Correction to a Previous Post — Good News

I wrote in a post a few months ago about Oracle XE and the fact that it doesn’t come with Oracle Workspace Manager. I was talking to Intergraph a few days ago and was corrected on that issue. Apparently, although Workspace Manager does NOT come pre-installed with Oracle XE, you can use Oracle Workspace Manager with XE. You just need to install it manually. For example, you can download the Oracle Workspace Manager 10.2.0.3.1 kit off of the Oracle Metalink web site, connect to XE as SYS/ AS SYSDA, and run the OWMINST.plb script. This will install OWM in the Oracle XE database and you’ll be able to use GeoMedia Transaction Manager with Oracle XE. Very cool.

Answer to a Question

A reader by the name of
Panos Elias contacted me recently asking a question about Oracle 11g support with GeoMedia. I replied to the email but the email bounced. So, I thought I’d post the reply here. The basic question was "do GeoMedia 6.0 and/or 6.0 read Oracle 11g data"? So, I talked to a contact of mine at Intergraph, and received the following answer:

11g released too late to be certified but I have been testing with the 6.1 beta for a few months. Oracle broke the ANYINTERACT spatial operator in the released version of 11g but they did fix some spatial filter problems.

So, although 6.1 will NOT be certified against 11g, it has, and will continue to be tested against 11g. I would have to guess that an upcoming 6.1 hotfix will actually be certified against 11g.

For those that are unaware, one of the biggest additions to 11g was the native support for text in the Oracle database. What does this mean? Basically, if you store your text as an Oracle text object (not sure of the proper terminology), you should be able to see that text in ANY client that will read the standard Oracle text object. GREAT for those multi-GIS/CADD client agencies that need to look at GIS data in multiple applications like Bentley Microstation and Intergraph GeoMedia.

Unfortunately, 6.1 is not taking advantage of this new functionality. However, I’m hoping that we’ll see the support for Oracle native text before the release of 6.2.

On a side note, Intergraph did recently release an expansion pack to support Oracle’s GeoRaster datatype. For those that aren’t aware, with the release of Oracle 10g, you can now store imagery (think orthophotos) in the database. Very cool if you have the storage space and Oracle Spatial. The expansion pack can be downloaded from the Intergraph web site (requires a login).

General Update – SQL Tuning

Welcome back! Sorry it’s been so long since I updated the blog. Two weeks ago I attended an Oracle SQL Tuning class at the Oracle Training Center in Belmont, CA. I had taken a number of Oracle 8i classes four or five years ago. However I never took the SQL Tuning class. Over the past couple of years I’ve found myself having to do more and more at the database level as opposed to the application level (like GeoMedia), so I thought it was about time to take the class and try and get my queries faster.

For those that aren’t aware, SQL tuning is basically the idea of writing your SQL queries in ways that will run most efficienty in the database. So in other words, look at the following two SQL queries – both will return the same results but are written in different ways:

SELECT cust_city, avg(cust_credit_limit)
FROM customers
WHERE cust_city = ‘Paris’
GROUP BY cust_city

vs

SELECT cust_city, avg(cust_credit_limit)
FROM customers
GROUP BY cust_city
having cust_city = ‘Paris’

In the two queries the WHERE clause will be evaluated before the HAVING clause. Therefore, by using WHERE as opposed to HAVING, you’re able to reduce the number of records returned faster in Query A as opposed to Query B. As a whole, this can be VERY important – especially when dealing with hundreds of thousands of records.

Anyhow, as a whole, the class covered A LOT of information and really went back into the core of Oracle and how it operates for many things. Very interesting (I think)…but also very detailed. However, if I had to outline what I learned in a single thought, it would be the following:

"Oracle 10g wants to use the cost-based optimizer. So, it’s going to do it; and most often the cost-based optimizer is correct. So, the idea of including 8i and 9i SQL hints for the most part has been elimianted. In fact, unless the complete query is written using hints, Oracle will simply IGNORE the hints you put into an SQL query."

This was very good for me to understand as I was constantly playing with SQL hints trying (for the most part unsuccessfuly) to get my SQL queries to run faster.

All in all, I did find the class to be very useful and really helped me answer a number of questions I’ve had over the years. I’m looking to test some of the Oracle SQL tuning tools against a spatial query in the near future…. Should be interesting to see how much faster I can make it.

Forcing a User to Login When Loading a Geoworkspace with an Oracle Connection

For those have connected to an MGE connection using an Oracle database, you’ll recognize this screenshot:

This comes up whenever you open/close a connection or open a geoworkspace. Personally, even though the MGE connection to an Oracle database is read-only, I think this is a valuable option. On a side note, on some machines you can save the user name and password into the ODBC DNS setup and this will eliminate the need for entering a password at this dialog. This can be done my just embedding the password with the user name like this:

user/password

Anyhow, I’ve always been surprised that Intergraph doesn’t force something similar for an Oracle Object Model connection…ESPECIALLY since this connection can be read/write! For those that don’t use Oracle with GeoMedia, by default GeoMedia will save the password (hidden) in the .gws. As a result, whenever you load the .gws, GeoMedia will connect to the Oracle database with the user name and password saved in the .gws. Yes, this is convenient. However, it’s not ideal from a security standpoint. Especially if you actually use unique logins in Oracle where User A may only have read/write to TABLE A while User B may have read-only to TABLE A. To be honest, even from a non-security standpoint, it’s still a bad idea. It’s fairly easy to make a mistake when you’re not paying attention or not expecting to be able to do something to the data.

So, there is a workaround to force a similar type of login restriction. This can be found in the "Working with GeoMedia Professional" PDF in Appendix B, page B-2. However I’ll post it here as well:

By default, GeoMedia stores the Oracle connection password in the GeoWorkspace. This is meant as a convenience and allows users to open existing GeoWorkspaces containing Oracle connections without having to re-enter connection passwords. However, this is a drawback to those users wanting higher levels of security. The option to turn off password persistence is in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\GDO\Oracle Object Read-Only\1.0\Store Password
HKEY_LOCAL_MACHINE\SOFTWARE\GDO\Oracle Object Read-Write\1.0\Store Password
The default setting is 1, which means that connection passwords will be stored. To force the user to enter a password for each Oracle connection, change the (default) setting to 0.
Password persistence is not an issue if you are using Windows Domain authentication for your connections.

When you set these registry entries, you get the following dialog when you open a .gws, or close/open the connection:

You notice that it just sets the password to a single asterisk. Usability-wise, it’s not as easy as the MGE dialog, but it’s not bad.

It’s interesting to note that you have the option to not save the password for the Oracle Object Model Read-Only data server, the Oracle Object Model Read-Write data server, or both. I would heavily recommend setting it for read-write. If you’re not concerned with other people having read-only access to your data, you can keep GeoMedia saving the user’s password for read-only connections. Incidentally, this is what I prefer.

Now, since I’m not a big fan of editing the registry manually, I wrote a small executable using AutoIt – a FANTASTIC piece of freeware that allows you to script many different mundane Windows tasks and save the scripts off as standalone executables. I’ve uploaded my registry modification script (both source and executable). You can download it from the left hand side of the screen or from here. The script will force the login screen for read-write connections but will store read-only connections. You can modify the source quite easily (just reference my comments in the script) if you’d like to force the login screen both both read-write and also read-only.

Logging the Oracle Data Server

Not sure how many are aware pf GeoMedia’s Oracle Data Server logging functionality. This is great if you ever have a problem writing to an Oracle database.

To get GeoMedia to log everything it does with the Oracle database, do the following:

1. Close your connection to the Oracle database (or quit GeoMedia)
2. Create a file named “GOracle.log” in c:\temp (minus the quotes)
   NOTE – The file MUST be in c:\temp and MUST have the correct file name
3. Open the Oracle connection (or restart GeoMedia and connect to your Oracle database)
4. Start interacting with the Oracle database

You’ll notice that from that point forward EVERYTHING you do to the Oracle database is very verbosely logged. You can then go into the log file and review it for errors. Most of the time the error is easy to find and you can pull the information from the log to feed to Google or Intergraph support.

A couple of notes:

1. The log file can get very big, very fast. In fact, after only a few minutes of logging, I’ve seen the file grow to well over 10mb in size! So, make sure to move the file from the c:\temp folder or delete it altogether. If you don’t, any interaction you have in GeoMedia with the Oracle database WILL be logged. This could obviously cause a space issue on your hard drive after a while.
2. If you’re looking to write your own Oracle Locator/Spatial spatial query, this logging functionality can be an excellent first step. You can create the log file and then run a native query (Analysis > Native Query) inside of GeoMedia. For those that haven’t used the Native Query command, it’s basically a GUI for the Oracle Locator operators.
   So, I created a native query asking Oracle (as opposed to the spatial query operators inside of GeoMedia) to identify all of the stations that fall within a pressure zone named “Cox Zone”. Since this was an Oracle query, GeoMedia created the query, but logged all of the interaction between GeoMedia and the database. Copying directly from the log:

   select a.ROWID,a.GDO_GEOMETRY from SJWC_MIG.PRESSURE_ZONE b,SJWC_MIG.STATION a where PRESSURE_ZONE_NAME = ‘Cox Zone’ and mdsys.sdo_relate(a.GDO_GEOMETRY,b.GDO_GEOMETRY,’mask=ANYINTERACT querytype=window’) = ‘TRUE’

   All in all, I find this logging tool to be extremely useful. Especially when you’re working with a data model as opposed to a bunch of independent tables in a schema. It helps you understand just what GeoMedia is doing and why Oracle doesn’t like it. You can download my log from this session using this link or the link to the left. Although it’s boring, it might help add some context to this entry. You will also see the spatial query embedded in the log on a few different occasions.

Oracle Locator vs Oracle Spatial…Do you Know the Differences?

One of my favorite blogs is written by Simon Greener at www.spatialdbadvisor.com. He doesn’t post too often, but when he does, I really enjoy his content. I’d highly recommend you subscribe – especially if you use Oracle Locator and/or Oracle Spatial. His latest post is titled "Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package" and discusses the differences between the free with any version of the Oracle database, Oracle Locator and the paid addition to Oracle Enterprise Edition only, Oracle Spatial.

He goes over licensing, what’s covered in both products, and even some great workarounds if you only have Oracle Locator but need Oracle Spatial functionality. He’s even got a number of code examples to help you implement these Oracle Spatial types of functions with ONLY Oracle Locator.

Finally, much like the free Oracle Object Model scripts and packages available on the Intergraph web site, Simon has his own free packages and code available for download on his site. I haven’t had a need for any of them yet, but definitely nice to know they’re available.

Is There Really Such a Thing as "Too Much of a Good Thing"

Last year the City of San Jose managed a LiDAR project covering the entire Santa Clara County. As part of the LiDAR deliverables, the City, County, and other agencies received 1 foot contours for the entire county valley floor and 5 foot contours for the remaining areas of the county. Let me tell you…that’s some data! One of the many deliverable formats was Microstation .dgn files. Initially I built a couple of CAD Schema Definition (.csd) files with the hope that I could view the data using spatial filters. However, even for a very small area, when I tried to load the .csd files, I received an “out of memory” error after a minute or so of processing. Not totally surprised as there are 232 total .dgn files totaling 8 gb of space and what I’m roughly guessing will be 11,500,000 records! So …needless to day, I’m not going to output this to Access.

After some playing, I’ve been able to so far get groups of 10 dgn files to work correctly inside of GeoMedia. Now I’m in the process of loading the data into my Oracle Locator database using the Schema Remodeler utility that comes with GeoMedia Fusion. I figure this will take a few days to change the .csd file each time and let the loading process.

After the contours are all loaded, I’d really like to be able to update the Z value (elevation) for each point facility (asset) in my system with the nearest elevation contour value. Then, moving forward, whenever I insert a new point asset into the GIS, I’d like a database trigger to fire that will update the elevation with the contour nearest the point.

Although I’m really not sure how well this is going to work out over time, I’ll be reporting my thoughts and what I’ve learned as the project evolves. I do have some code I thought I’d post here. This code is designed to utilize the nearest neighbor function inside of Oracle Locator and find the nearest feature to the street segment. Then the code updates the street segment with the primary key of the nearest feature. This sounds to be very similar to what I want to do with updating elevations. It will just need a little (hopefully …little) tweaking.

set serveroutput on size 1000000
declare
commit_cnt number := 0;
cursor c1 is select gdo_geometry, rowid from MY_SEGMENT;
MY_PRIMARY_KEY number;
begin
for r in c1 loop
select a.id into street_id
from MY_STREET a
where sdo_nn (a.gdo_geometry, r.gdo_geometry, ‘sdo_num_res=1’) = ‘TRUE’;

update MY_SEGMENT
set MY_PRIMARY_KEY = MY_STREET_ID
where rowid = r.rowid;

if (commit_cnt = 1000)
then
commit_cnt := 0;
commit;
DBMS_OUTPUT.PUT_LINE (‘1000 Committed’);
else
commit_cnt := commit_cnt + 1;
end if;
end loop;
end;
/

Now, the last time I looked at this code (it was written five years ago), it was designed for 9,000 records. It will be interesting to see what it does with a table 25 times larger.

If it weren’t for the sheer size of data, I’d also try the GeoMedia 6.1 nearest neighbor aggregation command. However, knowing how fast GeoMedia 6.0 crashed, even if Intergraph has greatly improved memory management in 6.1, I still don’t expect it to be able to handle this huge aggregation task. So, I’ll most definitely do it with Oracle functions but I might try and see what GeoMedia 6.1 can do as well.

Some SQL For the Day

We maintain a column in the GIS that tracks the billing number for each facility (asset) we put into the ground. Although there are a large number of billing numbers (probably several thousand at this point), they should all follow the same pattern. To ensure there are any errors when entering them into the GIS, I wrote a check constraint. For those that aren’t familiar with check constraints in Oracle, they’re basically a step below referential integrity. They allow any value to be put into a column as long as the value meets the rules of the check constraint.

So in my case the billing number is a seven character number following this format "G6-0901". So here’s the constraint that I wrote:

REGEXP_LIKE (ECM_NUMBER,'[E][6-9]-[0][8-9][0-9][0-9]’) OR REGEXP_LIKE (ECM_NUMBER,'[G][0-9]-[0][8-9][0-9][0-9]’)

REGEXP_LIKE – This is a new function release in Oracle 10g. It allows you to use regular expressions in your SQL. In this case, I’m using an Oracle function "REGEXP_LIKE" which allows you to compare a known value against a regular expressiont.

ECM_NUMBER – This is simply the column name where the constraint resides. In other words, this is the name of the column that holds the billing number.

[E] – This ensures that the first character in the number is the letter "E"

[6-9] – This ensures that the second character in the number is a number 6, 7, 8, or 9

"-" – This ensures that the third character is a "dash"

[0] – This ensures the fourth character is a 0

[8-9] – This ensures the fifth character is a 8 or 9

[0-9] – This ensures that the sixth character is a 0 – 9

[0-9] – See above

Now I’ve also placed an OR statement in there providing two different formatting options. The rules are the same, but I’m just allowing for different formatting (e.g. starts with a "G" as opposed to an "E"), etc. I suppose I could have written it into one expression, it was just easier (in my eyes) to put it into two expressions.

Overall, I think it’s also pretty cool that really almost all of this is just regular expressions. So, if you’re familiar with regular expressions and their syntax, these types of constraints (and even MUCH more advanced queries) should be very "simple".

Looking for Suggestions

I’ve been working on next year’s budget a lot lately. As a result, I haven’t had a lot of time to work with GIS. As my budget tasks wind down, I’m looking for some new things to cover here on http://jeffhobbs.net. Since I haven’t been doing a lot with GeoMedia in the last few weeks – at least not things that I haven’t already covered; I thought I ask you all what you think you’d like me to cover. So, if you can post some comments, I’ll see what I can cover over the next few weeks.

In part, I’m looking to get more into Oracle 11g and its new spatial components. So, for lack of other suggestions, I might head in that direction.

On a side note, recently purchased a book Yahoo Maps Mashups from Amazon.com as we’ve been looking at possibly jumping on the band wagon and seeing if a mashup might serve our sister company in Texas well. Anyway, I have an intern that’s been doing the majority of the leg work. She’s been working with the Yahoo Maps API in conjunction with GeoServer. Although I’m definitely not sure if anything will come out of the testing, the book is really very good for introducing you to Yahoo Maps development.

Will be off on Friday, but will checking the blog…looking for suggestions.

Until Monday….

Oracle Permission Issues

Started drawing lines today into an Oracle database and found something quite odd. The feature I’m tracing (lot lines) has read-only permission inside of the Oracle database. I’m trying to add new water mains to my database. In the water main feature class I have read/write permissions. When I go to place the main, if I snap directly on the lot line, I’m getting a permission error. It looks like GeoMedia thinks I’m trying to insert into lot lines as opposed to water mains. I’m getting the permission error as soon as I place my digitizing cursor on the lot line.

Has anybody else experienced this before?

Using Oracle Locator Data in Google Earth

I happened upon an interesting article titled “Integrating Oracle Spatial with Google Earth” yesterday on the Oracle web site. The article is authored by Justin Lokitz of Leica Geosystems (formerly of Oracle Corporation and Acquis Inc.). It discusses using the open source software GeoServer. The article has a very good discussion of using GeoServer to read the Oracle database and create KML that can be ingested dynamically (via a Network Link) by Google Earth. The entire process seems to be somewhat straightforward. It should also be noted that Justin has posted an addendum to the article on his OraGIS blog. Finally, the folks at GeoServer have also posted a six+ minute movie highlighting how the data can be ingested by Google Earth.

After looking at GeoServer, the software looks very cool. It appears to be a data broker (for lack of a better term) that allow you to transform “typical” GIS data (shapefile, Oracle Locator, ArcSDE, etc.) to standards-based formats that can be used by today’s “main stream” applications like Google Earth (via KML/KMZ), Google Maps/Yahoo Maps/Microsoft Virtual Earth (via GeoRSS), Flex/Flash (via WMS/WFS), GIS applications (via WMS/WFS), etc.

However, do keep in mind that each of these applications has different licensing agreements. For instance, Google Earth is free for
personal, non-commercial use only. Furthermore, there are some restrictions on the online mapping applications. In the end, just make sure to read the licensing agreements of the software that will be the client for your application …ideally before developing the application 🙂 .