Open Source GIS Stack

In my last post, I talked about how easy the PostgreSQL/PostGIS installation was. The ArcSDE 9.3 media even includes the correct version of PostgreSQL to install for use with SDE. One note: if you want to enable PostGIS support, SDE 9.3 only supports PostGIS version 1.3.2, NOT the latest 1.3.3. Make sure you only install the 1.3.2 version if you want to use PostGIS for data storage.

Once I loaded PostgreSQL and enabled the PostGIS support, I simply change the default geometry storage type in my DBTUNE table to "PG_GEOMETRY". Then I copied/pasted my sample dataset into PostGIS using ArcCatalog. My sample dataset is a "small" electric utility dataset, with approximately 120,000 primary_oh lines, and 60,000 transformers. The copy/paste went off without a hitch, and I have a very usable dataset in PostGIS. Now, let's see what we can do in a command prompt with our coordinates:

sde=# select st_astext(shape) from primary_oh
sde-#    where objectid = 856253;              

          st_astext
------------------------------------------------- 
LINESTRING(1012108.7600746 1045855.35055982,     
    1012173.61937546 1045822.20342262,
          1012230.98826477 1045792.88789014)
(1 row)

Cool, I can get the coordinates. Now, for even more fun. Let's find if there are any Transformers attached to that piece of Primary_OH:

sde=# select objectid, st_astext(shape) from transformer  
sde-#    where st_intersects(transformer.shape, (select
sde(#    shape from primary_oh where objectid = 856253));

objectid |                st_astext
 ---------+------------------------------------------ 
529532 | POINT(1012108.7600746 1045855.35055982) 
479413 | POINT(1012230.98826477 1045792.88789014) 
(2 rows)

Checking my map, that is indeed the correct answer. Yay! It works as it should.


Now that I have my data loaded into PostgreSQL/PostGIS, it was time to find out if other (non-ESRI) software could interact with the data. First test: Quantum GIS (http://qgis.org). Quantum GIS is an open source cross-platform GIS that runs on Linux, Windows, and OS X. I was able to load my PostGIS data straight in without any problems, and was very impressed with how fast it draws my data.




Great, we have a cross platform desktop application that will read/write my PostGIS data. But how can I get this data onto the web? Let's try Geoserver (http://geoserver.org). After downloading and installing Geoserver, the steps to get Geoserver working with my data were very straightforward:

• Open the Geoserver administration webpage (http://localhost:8080/geoserver)
• Go to Config, then Data
• Create a Namespace for my data;
• Back on the Config->Data menu, now add a DataStore for my PostGIS data;
• Back on the Config->Data menu, go to FeatureTypes. Enable all the features you want to add to your published map.
• Lastly, I wanted to use WMS to publish my map. So, go to Config->WMS and set up a group layer for my new data.

I made a quick modification to the OL_DEMO.HTML sample file, and looky-looky. I can now draw my data over top of Google Earth, MS Virtual Earth or Yahoo Satellite data. Most impressive how easy it was to publish data to the web, considering I am a database guy.


ESRI, I hope you're taking notice of this stack. Because I'll bet some of your customers will be.