OGR2OGR MySQL to Shapefile Truncating Decimal Places

[mthompson]

While attempting to create a ESRI shapefile from the results of a SQL query on a MySQL DB using OGR2OGR included with FWTools 2.2.8 (GDAL 1.6.0dev) the operation completes without reporting any errors and the shapefile is created as expected, but when I load the shapefile in ArcMap? all the floating point columns in the attribute table have no decimal places. If I run the query in MySQL Query Browser the results contain the decimal places that I expect. I’ve tried rounding the selected columns in the query to force the results to have a set number of decimal places but this does not seem to change the output. I was able to work around the problem by converting the double columns to chars in the query so that OGR2OGR treated them as strings.

[warmerdam]

Michael,

Could you include the basic information to reproduce the problem? For instance, if you could provide the SQL you use to declare and a table in MySQL, and populate it with a row. And then show the ogr2ogr command you use to translate to shapefile and then the ogrinfo output produced against that shapefile we should have enough to dig into this more deeply.

The specifics of how the mysql columns are declared is likely to be quite important so we particularly need to know that.

Turning over to Chaitanya to investigate once information is available.

[mthompson]

Here are the table creation statements and data to populate the tables with enough rows to generate at least one entry in the resulting shapefile. I've also included the ogr2ogr command line call that I'm making when I see the problem. I've also included the definition of the stored procedure used in the SQL query. Please let me know if you need any other information.

PlaceHolders? <dbname> = Name of Database <user> = MySQL user <password> = MySQL password <host> = MySQL server

ogr2ogr -overwrite -f "ESRI Shapefile" geocodes_nytruckfall2008.shp MySQL:<dbname>,user=<user>,password=<password>,host=<host> -t_srs epsg:4326 -s_srs epsg:4326 -sql "SELECT x.machine, x.samplenumber, x.qnumber, x.success AS geocodeSuccess, x.latitude AS geocodeLat, x.longitude AS geocodeLong, b.locationid, b.locationdetails, b.latitude AS siteLat, b.longitude AS siteLong, b.complete, b.final_weight, b.routesuspect, ROUND(GEO_DISTANCE(x.latitude, x.longitude, b.longitude, b.latitude) / 1000, 2) AS distanceKm, GeomFromText?(CONVERT(CONCAT('POINT(',x.longitude,' ', x.latitude, ')'),CHAR)) as the_geom, CONVERT(CONCAT(x.samplenumber,x.qnumber), SIGNED) as GID FROM xlocategeocodes AS x LEFT JOIN (SELECT p.machine, p.samplenumber, p.complete, p.final_weight, p.site_id, l.locationid, l.locationdetails, l.latitude, l.longitude, p.routesuspect FROM participants p LEFT JOIN surveylocations AS l On p.site_id = l.locationid) AS b ON x.machine = b.machine AND x.samplenumber = b.samplenumber WHERE final_weight is not null;"

xlocategeocodes CREATE TABLE xlocategeocodes (

machine varchar(50) NOT NULL default 'NONE', samplenumber int(11) NOT NULL default '0', qnumber int(11) NOT NULL default '0', timestamp datetime default NULL, success smallint(6) default '0', intersection varchar(255) default NULL, city varchar(100) default NULL, state varchar(45) default NULL, zip varchar(45) default NULL, country varchar(45) default NULL, x double default '0', y double default '0', streetnbr varchar(45) default NULL, street varchar(255) default NULL, poiname varchar(100) default NULL, poitype varchar(45) default NULL, latitude double default NULL, longitude double default NULL, PRIMARY KEY USING BTREE (machine,samplenumber,qnumber)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO xlocategeocodes (machine,samplenumber,qnumber,timestamp,success,intersection,city,state,zip,country,x,y,streetnbr,street,poiname,poitype,latitude,longitude) VALUES

('T30455',9006,180,'2008-09-06 12:45:16',1,'','Miami','FL','331**','USA',-8921996.25494718,2962235.33650257,'','','','',25.72899,-80.23744), ('T30455',9006,181,'2008-09-06 12:46:06',0,'','kingston','nc','','USA',0,0,'','','','',0,0), ('T30455',9006,182,'2008-09-06 12:47:40',1,'','Drummondville','QC','J**','CDN',-8059866.40629539,5754824.32755902,'','','','',45.88082,-72.48412), ('T30455',9006,183,'2008-09-06 12:47:45',0,'','','','','USA',0,0,'','','','',0,0);

participants CREATE TABLE participants (

MACHINE varchar(25) NOT NULL default 'NONE', USERNAME varchar(25) NOT NULL, SAMPLENUMBER int(11) NOT NULL default '0', TIMESTAMP datetime default NULL, PARTID int(11) default '0', COMPLETE int(11) default '-1', SITE_TYPE int(11) default NULL, SITE_ID int(11) default NULL, FINAL_WEIGHT double default NULL, ROUTESUSPECT int(11) default '0', PRIMARY KEY USING BTREE (MACHINE,SAMPLENUMBER,USERNAME)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO participants (MACHINE,USERNAME,SAMPLENUMBER,TIMESTAMP,PARTID,COMPLETE,SITE_TYPE,SITE_ID,FINAL_WEIGHT,ROUTESUSPECT) VALUES

('T30455','Robert P',9006,'2008-09-06 12:51:59',0,1,1,2,534.1032,1);

surveylocations CREATE TABLE participants (

MACHINE varchar(25) NOT NULL default 'NONE', USERNAME varchar(25) NOT NULL, SAMPLENUMBER int(11) NOT NULL default '0', TIMESTAMP datetime default NULL, PARTID int(11) default '0', COMPLETE int(11) default '-1', SITE_TYPE int(11) default NULL, SITE_ID int(11) default NULL, FINAL_WEIGHT double default NULL, ROUTESUSPECT int(11) default '0', PRIMARY KEY USING BTREE (MACHINE,SAMPLENUMBER,USERNAME)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO surveylocations (locationid,locationtypeid,locationdetails,latitude,longitude) VALUES

(2,2,'Glens Falls Rest Area',43.272686,-73.673996);

CREATE FUNCTION GEO_DISTANCE(sLong DOUBLE, sLat DOUBLE, eLong DOUBLE, eLat DOUBLE) RETURNS double BEGIN

DECLARE DEG_TO_RAD DOUBLE; DECLARE EARTH_RADIUS DOUBLE; DECLARE rSLong DOUBLE; DECLARE rSLat DOUBLE; DECLARE rELong DOUBLE; DECLARE rELat DOUBLE; DECLARE dLong, dLat DOUBLE; DECLARE arcLength, greatCircle DOUBLE; DECLARE geoDist DOUBLE; SET DEG_TO_RAD = PI() / 180; SET EARTH_RADIUS = 6300000; SET rSLong = sLong * DEG_TO_RAD; SET rSLat = sLat * DEG_TO_RAD; SET rELong = eLong * DEG_TO_RAD; SET rELat = eLat * DEG_TO_RAD; SET dLong = rELong -rSLong; SET dLat = rELat -rSLat; SET arcLength = SIN(dLat / 2) * SIN(dLat / 2) + COS(rSLat) * COS(rELat) * (SIN(dLong / 2) * SIN(dLong / 2)); SET greatCircle = 2 * ATAN(SQRT(arcLength) / SQRT(1 - arcLength)); SET geoDist = EARTH_RADIUS * greatCircle; RETURN geoDist;

END

[chaitanya]

Replying to mthompson: Could you send the code for surveylocations table definition.

[chaitanya]

mthompson, you missed the table definition for 'surveylocations' instead showing the definition for 'participants'. Can you provide the correct code?

surveylocations CREATE TABLE participants ( MACHINE varchar(25) NOT NULL default 'NONE',

...

FINAL_WEIGHT double default NULL, ROUTESUSPECT int(11) default '0', PRIMARY KEY USING BTREE (MACHINE,SAMPLENUMBER,USERNAME) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO surveylocations (locationid,locationtypeid,locationdetails,latitude,longitude) VALUES (2,2,'Glens Falls Rest Area',43.272686,-73.673996);

[mthompson]

chaitanya,

Here is the create statement for surveylocations.

CREATE TABLE  `surveylocations` (
  `locationid` int(11) NOT NULL COMMENT 'This is the answer number ',
  `locationtypeid` int(11) NOT NULL,
  `locationdetails` varchar(100) default NULL,
  `latitude` double default '0',
  `longitude` double default '0',
  PRIMARY KEY  (`locationid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contains information on survey locations';

Replying to chaitanya:

mthompson, you missed the table definition for 'surveylocations' instead showing the definition for 'participants'. Can you provide the correct code?

surveylocations CREATE TABLE participants ( MACHINE varchar(25) NOT NULL default 'NONE',

...

FINAL_WEIGHT double default NULL, ROUTESUSPECT int(11) default '0', PRIMARY KEY USING BTREE (MACHINE,SAMPLENUMBER,USERNAME) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO surveylocations (locationid,locationtypeid,locationdetails,latitude,longitude) VALUES (2,2,'Glens Falls Rest Area',43.272686,-73.673996);

[chaitanya]

Resolved in trunk in r17323.

Right now the precision (number of decimal places) for double is usually translated to 31, which is what is usually returned by MySQL's MYSQL_FIELD::decimals.

I will backport it to 1.6 after I look deeper into the MySQL docs about this behaviour.

[chaitanya]

As per EvenR's suggestion I changed the precision handling for double data types which seems to be better. (r17328)

Will add a test shortly.

[chaitanya]

Added a test in trunk. (r20109)