[postgis-users] Pipeline Data Model

[Dane Springmeyer]

Robert and Abe,

On Jan 10, 2008, at 12:48 PM, Burgholzer,Robert wrote:
> If you have set up an ODBC connection to your PostgreSQL database, the
> tables can be loaded into postgres from MSAccess by using Access's
> "Export" function.

That has also been my method for going from MS Access => Postgres. In  
my case I set up the ODBC connection on a windows machine then dump  
the non-spatial tables from postgis to then restore into postgis on a  
linux or mac os x machine. If you try to export the spatial tables  
you'll get an error like this (which I have never tried to get around):
http://archives.postgresql.org/pgsql-bugs/2002-12/msg00174.php

Abe asked:

> But are you saying you can push the table
> definitions from Access into Postgres?

I am only aware of the ability to export the table with definitions  
of field types - nothing more... since the odbc export functionality  
operates on individual tables.

mdbtools (see below) exports basic schema from mdbs with multiple  
tables but not relationships. DBcovert can export sequences and  
constraints but I am not sure what else.


>  If there are spatial columns in there, this will be
> a difficulty, but the regular data will upload. Of course, any  
> linkages
> will have to be reconstituted by hand, but it is a reasonable  
> method in
> my experience.

I bring in the spatial columns on their own shp2pgsql.

Converting both attribute and spatial columns using shp2pgql is not  
ideal because of the hassle of having all column names chopped off at  
10 characters at the shp/dbf step.

I've tried ogr2ogr with the PGEO driver but at this point bugs in the  
mbdtools dependency have made that a *real* challenge. It currently  
only feasible on a windows machine:
http://www.postgis.org/pipermail/postgis-users/2006-October/013651.html

If anyone makes progress on this front do post more ideas. I can tell  
from list archives that there is good reasons to avoid thinking of  
postgis as a file converter, but this MDB => Postgis issue is bound  
to require more and more attention as people discover postgis.

Cheers,

Dane






>
> Robert W. Burgholzer
> Surface Water Modeler
> Office of Water Supply and Planning
> Virginia Department of Environmental Quality
> rwburgholzer at deq.virginia.gov
> 804-698-4405
> Open Source Modeling Tools for NPS analysis and Water Supply Planning:
> http://sourceforge.net/projects/npsource/
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> George Silva
> Sent: Thursday, January 10, 2008 3:10 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Pipeline Data Model
>
> Abe, here is the XML file. Its a product of ArcGis Diagrammer.
>
> Note: this is APDM version 2. I cant seem to build in version 4. Ill
> look into it.
>
> If you do build that code, i would to see it very much ;)
>
> Att.
>
> George
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users