St_buffer() giving inaccurate results

Question

I have the following code:

set @lon = 121.4732134;
set @lat =  31.2304321;
set @point = point(@lon, @lat);
set @radius = .5;
set @polygon = ST_Buffer(@point, @radius);

select l.city,l.latitude,l.longitude, 
st_distance_sphere(l.latlngindex, point(@lon, @lat)) as distance
from table_locations l
where st_within(l.latlngindex, @polygon)
order by distance 
;

It runs fine, gives result, but only first 5 or 6 are accurate wrt distance between them. Rest of them are inaccurate, I verified this on a couple of sites.

Table structure-

 `locationid`,
 `latitude`,
 `longitude`,
`latlngindex` point not null,
 spatial index `latlngindex` (`latlngindex`)

Sample insertions :

insert into `table_locations` values(2001,31.2372705, 121.4705291, Point(121.2372705, 31.4705291));
insert into `table_locations` values(2002,31.2328741, 121.4741493, Point(121.2328741, 31.4741493));
insert into `table_locations` values(2003,31.2300200, 121.4749245, Point(121.2300200, 31.4749245));
insert into `table_locations` values(2004,31.2302308, 121.4705508, Point(121.2302308, 31.4705508));
insert into `table_locations` values(2005,31.2391562, 121.4771425, Point(121.2391562, 31.4771425));
insert into `table_locations` values(2006,31.2331857, 121.4779539, Point(121.2331857, 31.4779539));

Sample result row :

   Lat          Long             distance
  31.2397267', '121.4742061', '35019.00977766075'

Distance between (31.2397267, 121.4742061) and (31.2304321, 121.4732134) should be 1004m, while it gives it to be 35019m.

Yes Sir. I inserted about 1000 co-ordinates. When I run above, with `r=0.1` 11 rows are returned correctly. While rest are returning wrong measurements. For eg 107,820 m in place of 1700m(right value). I verfified some of the distances here. — lmao, Mar 20 '19 at 23:14
Show all the column values for the row that's giving the wrong result. — Barmar, Mar 20 '19 at 23:16
The query uses `latlngindex`, not `latitude` and `longitude`. — Barmar, Mar 21 '19 at 00:27
@Barmar - I have spatial index as `latlngindex` in my location table. Updated everything, Sir. — lmao, Mar 21 '19 at 00:36
What is Point(79.2355396, 23.4724829) in your insert statement? It is not matched to lon/lat you are inserting. — fifonik, Mar 21 '19 at 00:58
@fifonik - Sorry Sir, it was a typo while copy/pasting, rectified it. — lmao, Mar 21 '19 at 01:09
You have @lat = 121.4732134, but inserting lat (the second argument) 31.2396691. Looks like lat/lon swapped for me... — fifonik, Mar 21 '19 at 01:09

fifonik · Accepted Answer · 2019-03-21T01:57:43.267

1

Looks like you mixed up lat and lon

DROP TABLE IF EXISTS `table_locations`;

CREATE TABLE `table_locations` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `latitude` DOUBLE NOT NULL,
    `longitude` DOUBLE NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `table_locations` VALUES (1, 31.2396691, 121.4798393);
INSERT INTO `table_locations` VALUES (2001, 31.2372705, 121.4705291);
INSERT INTO `table_locations` VALUES (2002, 31.2328741, 121.4741493);
INSERT INTO `table_locations` VALUES (2003, 31.2300200, 121.4749245);
INSERT INTO `table_locations` VALUES (2004, 31.2302308, 121.4705508);
INSERT INTO `table_locations` VALUES (2005, 31.2391562, 121.4771425);
INSERT INTO `table_locations` VALUES (2006, 31.2331857, 121.4779539);
INSERT INTO `table_locations` VALUES (2007, 31.2397267, 121.4742061);


SET @lat = 31.2304321;
SET @lon = 121.4732134;
SET @p = point(@lon, @lat);
SET @r = 43.0;
SET @POLY = ST_Buffer(@p, @r);

SELECT
      id
    , latitude
    , longitude
    , ST_Distance_Sphere(POINT(longitude, latitude), @p) as dist
FROM
    table_locations
WHERE st_within(POINT(longitude, latitude), @POLY)
ORDER BY
    dist
;

Returns values and shows distance 1204.9090584034252 for the first location

edited Mar 21 '19 at 01:57

answered Mar 21 '19 at 01:26

fifonik

1,556
1
10
18

I mixed up data from different countries. I have re-arranged everything, tested it, and now have posted everything. Do have a look at it. – lmao Mar 21 '19 at 01:34
I've added `insert into `table_locations` values(2007,31.2397267, 121.4742061); ` into my code above, changed @r to .05 and it gives me result 1037.810 for the location. Neither of your points from updated post returns more than 1040m. I think you should check if your latlonindex generated correctly (it is incorrect right now you are punnint in the index values that not matched to your columns). As you see for simplicity I'm using lat and lob values directly. – fifonik Mar 21 '19 at 01:42
1

Right now you are putting wrong values in the index (values not matched with the values you are putting in separate columns). `insert into table_locations values(2004,**31.2302308**, 121.4705508, Point(121.2302308, **31.4705508**));`. You should use trigger to populate the index, imho. – fifonik Mar 21 '19 at 01:48
As I already explained, you have wrong data in the latlngindex. You've made mistake in every insert. Then you are doing your calculation to the wrong value. – fifonik Mar 21 '19 at 01:51
Now, I got it where is the mistake. Thanks. I made a wrong generator for insertion when Barmar asked for samples, and everything got messed up. Must go to sleep now. Since you are getting ight values, I guess things are fine. – lmao Mar 21 '19 at 01:56
One more thing sir, and the last one - How should we decide the value of radius for a table having entries around 10k. In my case, with `entries = 1k`, `radius = 1.9` returns 510 rows. `Radius = 2` returns all `1000 rows` – lmao Mar 21 '19 at 02:22
1

Sorry, I cannot help you with that. Never used the ST_Buffer. Guessing, it is in degrees – fifonik Mar 21 '19 at 02:44
No problem. And, yes, it's in degrees. Thanks again. – lmao Mar 21 '19 at 02:47

St_buffer() giving inaccurate results

1 Answers1