Re: Conversion to PostgreSQL from ArcSDE

Hi Tyler, cc to the pdx-osgis list

It somewhat ironic -- I gave a short presentation on PostGIS at the
meeting last night. As soon as my blog entry is cleaned up, I will
send the link to this list.

See replies below.

> First, I would like to introduce myself.  My name is Tyler Barns and I am an
> Environmental Science graduate student at Oregon State University.
> Currently I am a student intern with the Oregon Explorer
> (www.oregonexplorer.info).  I am new to the world of Open Source GIS and
> would like to know more about PostgreSQL.  I've read some of the online
> documents and feel that Open Source GIS is where I need to be headed.

Welcome, and yes, Open Source is definitely the future.

> Second, I am researching how to move data housed in ArcSDE to PostgreSQL and
> what Open Source applications are being used as substitutes for ArcMap.  I
> would appreciate any feedback on the questions I have listed below.  Thank
> you for your time and effort.
>
> Is PostgreSQL a comparable open source GIS server to ArcSDE?

Postgresql is an SQL database, with PostGIS *extensions* (PostGIS) if
you install them. So it would be more apt to compare Oracle or
SQLServer with their respective geographic/ spatial extensions to
PostgreSQL.

Whether PostGIS is comparable to Oracle Spatial or SQLServer whatever
is a better question, and I don't have the answer to that, though I
think it is, more or less (they all meet the standards to 90%).

I *believe* that ArcSDE can now point to a PostgreSQL db with PostGIS
the same as it already can with Oracle or SQL Server. So you might be
able to use that layer of indirection in a migration plan.

> What is the process to convert services from ArcSDE to PostgreSQL?
>
> Are there any complications we should know about?
> Any shortcuts?

There is almost surely no automatic process to do this migration.
Generally a database migration starts with dumping the schema from the
old, importing it into the new, watching it break, fixing it, etc
until you have a new schema. Then you do the same with the data. Then
you set up the interface around it. There are lots of tools to help in
specific parts of this process. Do you have more specific questions?

You might also rough out fairly specific plan and then circulate it
for comments.

> What are your opinions on GeoServer?
> Which open source GIS application would you recommend and why?
> What are some benefits to open source servers and applications besides
> costs?
> Is there any advice you may have to transition/implement open source GIS
> more smoothly?

These are good questions, but at such a level of generality as to
almost impossible to answer. Hopefully someone can share a link to an
article...?

They also probably belong on a GIS mailing list, rather than a
database mailing list. The Porltand Open Source group is meeting next
week, we have a mailing list, you are invited, and there is a guy from
Philomath that you might be able to carpool with (send the list a
note). I would imagine if you buy us all beers after the meeting, we
can give you an earful of Open Source GIS wisdom (or rant, or
bull....). Or we can buy ourselves beers, and you will still get lots
and lots of advice.

http://groups.google.com/group/pdx-osgis

Once you join this list, ask about other good lists (geowankers and
the PostGIS lists would be great to start with, there is a geoserver
list too, etc).

I am not sure how much experience you have with what would be called
an Open Source GIS stack, but there are many, many applications that
go into it and a lot of complexity. What might be interesting is for
you to pick a migration and application problem and try to work
through it; if you have a specific problem, then post that because it
would be easier for us to sink our teeth into.

HTH