0

I am trying to determine the metric system for MySQL Spatial 5.6.12.

For example, I have the following table that created to store point geometry for multiple records.

CREATE TABLE POINTS_DATA(
    RECORD_ID         INT(15), 
    STREET_ADDRESS      VARCHAR(50), 
    CITY                VARCHAR(50), 
    STATE               VARCHAR(25), 
    ZIPCODE             VARCHAR(11), 
    LOCATION_GEO_CODES_SDO  POINT NOT NULL, 
    SPATIAL INDEX(LOCATION_GEO_CODES_SDO),
    PRIMARY KEY(RECORD_ID)
) ENGINE=MyISAM;

After the table was created, I inserted some records into the table successfully.

Now I have constructed the following query to fetch all the records that are within one mile from a specified LAT/LONG. Here is the query I run for that:

SELECT RECORD_ID, STREET_ADDRESS, CITY, STATE, ZIPCODE
       , GLength(LineStringFromWKB(LineString(LOCATION_GEO_CODES_SDO
       , POINT(-85.123,39.113))) AS DISTANCE 
FROM   POINTS_DATA  
HAVING DISTANCE < 1 ORDER BY DISTANCE;

After running this query, I do get some records, but the distance does not seem to be in Miles or meters. It’s some fractional value like 0.0123, 0.0145, etc…

I could not find any documentation on this anywhere in MySQL? Does anyone know what metric system is in use in MySQL? And if there is, how can I convert it into miles?

That means, if I need to run the query above to fetch all records within one mile, how do I reconstruct it?

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
user2052129
  • 231
  • 4
  • 11

1 Answers1

0

MySQL Spatial uses the spatial reference system of the geometry for GLength. You haven't defined what (if any) SRID you are using for your data, but guessing from the values you provide, the SRID is the default one. As a result, the values you are getting are just the Cartesian distance between the two points, using degrees as units.

Since you're only calculating distance of a line, you could drop GLength and calculate the distance yourself using the Haversine formula. See MySQL Great Circle Distance (Haversine formula).

Community
  • 1
  • 1
lreeder
  • 12,047
  • 2
  • 56
  • 65
  • How do i use the Geodetic Co-ordinate system for Latitude and Longitude instead of the Cartesian Co-ordinate system?. is there a way to do that in MYSQL Spatial Extensions? – user2052129 Sep 10 '13 at 15:57
  • MySQL doesn't support geodetic calculations. You could use a geodetic CRS (and you are with lat-lng), but MySQL will still continue to use Cartesian calculations when returning distance. If you need geodetic calculations, some databases that support that are PostGis, Oracle Spatial, or SQL Server. – lreeder Sep 10 '13 at 17:03
  • Thanks. I have used Oracle Spatial in the past and i do know they support the geodetic coordinate system. Was just exploring MySQL Spatial as a cost-effective solution in comparison to all features of Oracle Spatial. If the geodetic co-ordinate system is not supported, is there a way to convert it?.. hoe can we check for point geometries on an actual map , load actual merchant locations that are identified by LAT/LONGS?...is there a work around to support this in MYSQL? – user2052129 Sep 11 '13 at 15:00
  • Most of the point-in-poly and intersection methods will work the way you want, if you are using the most recent version of MySQL, regardless of coordinate system. However, area and distance calculations in a geodetic coordinate system will contain inaccuracies, and will get worse the further north/south you go. FYI, most people find PostGIS a low-cost (and perhaps more functional) alternative to Oracle Spatial. – lreeder Sep 14 '13 at 01:42