[postgis-users] MySQL->PostGIS

[Guido Lemoine]

I reckon you want to get rid of the shape files as well using shp2pgsql. 
I don't know how
you currently link shape attributes to your MySQL tables. Usually there 
are some attributes in the .dbf
files that constitute the shape file set (.shp, .shx, .dbf, etc.). You 
probably have to do some
linking (foreign keys) between your new postgresql tables as well.

But it's certainly worth the effort.

For web hosting, you should be able to find one which allows you to work 
with postgresql
(I use one in Italy). The problem may be to have it configured correctly 
for the latest postgis
version, with GEOS, JTS, etc. If you have your own Linux host, it should 
be fairly simple,
though.

GL

Erich Schroeder wrote:

> 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
> ---------------------------------------------------------------------
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>