[postgis-users] MySQL->PostGIS

[Stephen Woodbridge]

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
>