| Bug #7640 | Distance function | ||
|---|---|---|---|
| Submitted: | 3 Jan 2005 16:52 | Modified: | 4 Jan 2005 15:25 |
| Reporter: | [ name withheld ] (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.8-nt-max | OS: | Windows (Windows 2000 server) |
| Assigned to: | CPU Architecture: | Any | |
[4 Jan 2005 9:47]
MySQL Verification Team
Hi, Thank you for ther report, but MySQL currently doesn't support function DISTANCE(). It will be implemented later.
[4 Jan 2005 10:01]
[ name withheld ]
If Distance() is not implemented, why do I get this?
mysql> help distance
Name: 'DISTANCE'
Description:
Distance(@var{g1},@var{g2})
Returns as a double-precision number
the shortest distance between any two points in the two geometries.
Thanks for your help.
[4 Jan 2005 15:25]
MySQL Verification Team
Thank you for feedback. Yes, it's not implemented yet, though it's described in the MySQL manual. We removed description of DISTANCE() from the 'help' command as it confuses our users.
Description: Hi, using this table: CREATE TABLE `zip_codes` ( `STATE_FIPS_CODE` char(2) NOT NULL default '', `ZIP_CODE` varchar(5) NOT NULL default '', `STATE_ABBREVIATION` char(2) default NULL, `ZIP_CODE_NAME` varchar(50) default NULL, `ZIP_LOC` point NOT NULL default '', `1990_POPULATION` int(10) unsigned default NULL, `ALLOCATION_FACTOR` float default NULL, PRIMARY KEY (`STATE_FIPS_CODE`,`ZIP_CODE`), SPATIAL KEY `ZIP_LOC` (`ZIP_LOC`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Loaded with spatial data, while running this query: mysql> select state_fips_code, zip_code, distance(zip_loc, zip_loc) from zip_cod es; I get: ERROR 1064 (42000): 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 '(zip_ loc, zip_loc) from zip_codes' at line 1 Instead of a list on state_fips_code, zip_code and 0's for distance. A similar query in PostGIS leads to the expected result. Note that the query described in the article "GIS and Spatial Extensions with MySQL": SELECT z.state_fips_code, z.zip_code, ROUND(GLength(LineStringFromWKB(LineString(AsBinary(z.zip_loc), AsBinary(z.zip_loc))))) as distance from zip_codes z leads to the expected results. How to repeat: Follow the steps described above, data can be taken from http://spatialnews.geocomm.com/newsletter/2000/jan/cenzuszipcodes.zip see bug 6516's description for loading procedure.