[postgis-users] MySQL->PostGIS

[Erich Schroeder]

Ah, thank you. The little bit that I tried was doing a monolithic 
structure-and-data translation using my2pg.pl, and something else. I'll 
try your method..

Erich

On Tue, 6 Sep 2005, Stephen Woodbridge wrote:

> Erich,
>
> I moved a LOT of data from MySQL to PostGIS and this is the method I used 
> which was reasonable painless.
>
> 1) dump structure of mysql tables and convert to postgres
> 2) dump mysql data into tab separated columns of data
> 3) create the tables in postgres
> 4) edit the tab separated data file so it looks like:
>
> datafile.sql
> -------------------------------
> COPY <table> FROM STDIN;
> <data from mysql stripped of any header lines>
> \.
> --------------------------------
> then load the data with psql database < datafile.sql
>
> Now you have all the data loaded, you can start manipulating it inside 
> postgres/postGIS
>
> so if you have a table with lat and long columns and you want to add a 
> geometry column based on that then do something like:
>
> (assumes the database has the postGIS extensions loaded)
>
> alter table <table> add column the_geom geometry;
> <need to add some constraints here>
> update <table> set the_geom = setSRID(makepoint(long, lat), 4326);
> create index <table>_point_gidx on <table> using gist (the_geom 
> gist_geometry_ops);
> vacuum analyze;
>
> move it all to postgres/postGIS, you will be very happy you did, I know I am 
> :)
>
> -Steve W.
>
> Erich Schroeder wrote:
>> I have a mapserver application of paleontological data that uses shapefiles 
>> to create the taxon-specific maps, and then allows the visitor to go to 
>> views of the tabular data held in MySQL. I would like to recreated it using 
>> PostGIS to allow more flexibility, etc. I looked at a couple of 
>> mysql->pgsql translators and they didn't work for me right away. Am I 
>> likely better off with one of following methods:
>> 
>> 1. going ahead and moving all 32 of the tables into postgresql? If so, do 
>> any tools work well and easily?
>> 
>> 2. just creating one table on PostGIS to hold the point locations for the 
>> paleontological site locations, and continue the rest of the data in MySQL?
>> 
>> 3. work with DBI-Link (http://pgfoundry.org/projects/dbi-link/) to create a 
>> PostGIS-enabled database of the localities, with DBI-link'ed tables to the 
>> Mysql versions? Anyone used DBI-link?
>> 
>> I have to say that I hate to lose access to the data via phpMyAdmin, as 
>> I've gotten so used to it. However, I really want to be able to allow 
>> queries such as "show me localities within 150km that also contain taxon-A 
>> specimens that date from 8000 to 15000 years ago".
>> 
>> eks
>> 
>> ---------------------------------------------------------------------
>> Erich Schroeder                    Phone: (217)785-0033
>> Curator, Learning Center           FAX:   (217)785-2857
>> Illinois State Museum GIS Lab      email:erich(at)illinois.state.museum
>> http://www.museum.state.il.us/     Linux user #18660
>> ---------------------------------------------------------------------
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

---------------------------------------------------------------------
Erich Schroeder                    Phone: (217)785-0033
Curator, Learning Center           FAX:   (217)785-2857
Illinois State Museum GIS Lab      email:erich(at)illinois.state.museum
http://www.museum.state.il.us/     Linux user #18660
---------------------------------------------------------------------