ogr2ogr - MySQL issue

[Howard Butler]

Mike,

I think that many of these issues have been fixed in CVS GDAL.  You 
might try the 1.5 MS4W build, which has an ogr2ogr with a recent 
enough CVS GDAL version, or wait until the 1.3.3 GDAL release. 
Specifically, it appears that your MySQL is old enough that it 
doesn't support GEOMETRY types.  The MySQL driver in OGR was in flux 
at the time 1.0ab2 was released, and the limitation of forcing 
geometries has been removed.

Howard

At 6:53 AM -0700 4/11/06, mike d'ambrogia wrote:
>Sent this to the FWTools list last week but got no response, thought I'd
>try here prior to bulldogging out a different solution in phpmapscript
>
>------------------------
>
>trying to use ogr2ogr from FWTools1.0ab2 to move shape data into mysql,
>and a couple of problems crop up
>
>    ogr2ogr -f MySQL MYSQL:snfv_org_-_db,user=xxx,password=xxx,port=3306
>camps.shp -append -update
>
>connection to the db works great, but it comes back with :
>
>    ERROR 1: MySQL error message:Table 'snfv_org_-_db.camps' doesn't
>exist Description: DESCRIBE Failed
>
>it appears that it then tries to create a new table but only with 2
>fields which is incorrect.  my shape file, when converted to GML,
>contains:
>
><gml:featureMember>
><ogr:camps fid="F2">
><ogr:geometryProperty><gml:Point><gml:coordinates>789136.68985666858,422
>5743.1304081939,0</gml:coordinates></gml:Point></ogr:geometryProperty>
><ogr:ID>0</ogr:ID>
><ogr:SITE__>hucklk2</ogr:SITE__>
><ogr:DESTINATIO>Huckleberry Lk.</ogr:DESTINATIO>
></ogr:camps>
></gml:featureMember>
>
>I get this error when ogr2ogr tries to create the new table:
>
>ERROR 1: MySQL error message:You have an error in your SQL syntax. Check
>the manual that corresponds to your MySQL server version for the right
>syntax to use near 'GEOMETRY NOT NULL )' at line 1
>   Description: CREATE TABLE camps (OGR_FID INT UNIQUE NOT NULL
>AUTO_INCREMENT,SHAPE GEOMETRY NOT NULL)
>   ERROR 1: Terminating translation prematurely after failed translation
>of layer camps
>
>the field type "GEOMETRY" is not a valid data type in mysql
>
>so i create a table for camps more closely modeled after the GML layout:
>
>CREATE TABLE `camps` (
>   `fid` varchar(200) NOT NULL default '',
>   `coordinates` varchar(200) default '',
>   `ID` varchar(11) default NULL,
>   `SITE__` varchar(50) default NULL,
>   `DESTINATIO` varchar(50) default NULL,
>   `infra_id` varchar(11) default NULL,
>   PRIMARY KEY  (`fid`)
>) TYPE=MyISAM;
>
>now when I run the ogr2ogr command above it dr watsons on me with
>"ogr2ogr.exe has generated errors and will be closed by windows."  dr
>watson log doesn't reveal anything that helps
>
>in checking the archives for this list it appears that mysql support is
>somewhat new, can't seem to find a clue on the next direction to try,
>any suggestions? 
>
>also, is there an IRC/chat channel for FWTools?