Opened 15 years ago
Closed 14 years ago
#2852 closed defect (fixed)
OGR2OGR MySQL to Shapefile Truncating Decimal Places
Reported by: | mthompson | Owned by: | chaitanya |
---|---|---|---|
Priority: | normal | Milestone: | |
Component: | OGR_SF | Version: | 1.6.0 |
Severity: | normal | Keywords: | mysql |
Cc: | warmerdam |
Description
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.
Change History (9)
comment:1 by , 15 years ago
Cc: | added |
---|---|
Component: | default → OGR_SF |
Keywords: | mysql added |
Owner: | changed from | to
follow-ups: 3 4 comment:2 by , 15 years ago
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
comment:3 by , 15 years ago
Status: | new → assigned |
---|
Replying to mthompson: Could you send the code for surveylocations table definition.
follow-up: 6 comment:4 by , 15 years ago
Version: | unspecified |
---|
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);
comment:5 by , 15 years ago
Version: | → 1.6.0 |
---|
comment:6 by , 15 years ago
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);
comment:7 by , 15 years ago
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.
comment:8 by , 15 years ago
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.
comment:9 by , 14 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Added a test in trunk. (r20109)
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.