0

I am trying to run a mysql query below:

select *
from geo
where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609

where geometry is of type Polygon.

and get an error: Error Code: 3548. There's no spatial reference system with SRID 1498173264.

MySQL Engine version
8.0.27

My table structure is like:

CREATE TABLE `geo` (`id` int, 
                    `geometry` text,
                    spatialdata GEOMETRY);

INSERT INTO `geo` (id, geometry)

VALUES 

(2, 'POLYGON ((-118.282067 34.067679, -118.281555 34.067458, -118.281467 34.067419, -118.280624 34.067055, -118.279672 34.066689, -118.279376 34.066558, -118.279233 34.066489, -118.279098 34.066409, -118.278879 34.066251, -118.278047 34.065631, -118.276573 34.067943, -118.276505 34.06805, -118.275848 34.069081, -118.275294 34.069984, -118.274929 34.070554, -118.273876 34.072198, -118.27485 34.072519, -118.2753 34.072711, -118.275707 34.072889, -118.276615 34.073286, -118.27752 34.073681, -118.277735 34.073343, -118.277791 34.073256, -118.278056 34.072844, -118.278522 34.072117, -118.278988 34.071389, -118.279463 34.070648, -118.280081 34.070915, -118.280249 34.070772, -118.281158 34.06934, -118.282183 34.06773, -118.282067 34.067679))') 

(3, 'POLYGON ((-118.555285 34.362201, -118.555219 34.361998, -118.554778 34.360634, -118.554424 34.359603, -118.553965 34.358659, -118.553921 34.358567, -118.553439 34.357795, -118.552339 34.356615, -118.55166 34.356, -118.551612 34.355999, -118.551194 34.355994, -118.549745 34.354685, -118.548745 34.353768, -118.547909 34.353045, -118.547195 34.352502, -118.546862 34.3523, -118.545641 34.351656, -118.543184 34.350706, -118.542851 34.350577, -118.542381 34.350396, -118.542073 34.350288, -118.542008 34.350252, -118.539649 34.349332, -118.53605 34.347959, -118.535769 34.34785, -118.535125 34.347599, -118.534338 34.347228, -118.533434 34.346728, -118.532705 34.346214, -118.532041 34.345665, -118.531035 34.344554, -118.530628 34.344145, -118.530238 34.343806, -118.529486 34.343184, -118.528729 34.342756, -118.528053 34.342392, -118.527135 34.342008, -118.526355 34.341728, -118.52598 34.341644, -118.524945 34.341434, -118.523048 34.341227, -118.522788 34.341195, -118.521972 34.341063, -118.520987 34.340846, -118.52024 34.340662, -118.519549 34.340425, -118.518682 34.340007, -118.517922 34.33964, -118.517862 34.339612, -118.517141 34.339279, -118.516271 34.338841, -118.514968 34.33821, -118.514134 34.337806, -118.513566 34.337526, -118.512981 34.337237, -118.512383 34.336832, -118.512047 34.336584, -118.511511 34.33623, -118.511235 34.336052, -118.510177 34.335397, -118.509312 34.33482, -118.509297 34.334807, -118.508874 34.334455, -118.508688 34.334258, -118.508467 34.334022, -118.508341 34.33389, -118.508001 34.334146, -118.507788 34.334306, -118.507766 34.334322, -118.507725 34.334368, -118.507716 34.334377, -118.507672 34.334405, -118.507629 34.334435, -118.507525 34.334504, -118.507444 34.334564, -118.507364 34.334625, -118.507274 34.334693, -118.50723 34.334726, -118.507098 34.334825, -118.506869 34.334998, -118.506832 34.335026, -118.506637 34.335173, -118.506612 34.335191, -118.506514 34.335262, -118.506455 34.335308, -118.506396 34.335355, -118.506346 34.335393, -118.506004 34.335651, -118.505766 34.335831, -118.504007 34.337157, -118.50408 34.337281, -118.504095 34.337306, -118.504293 34.337511, -118.504534 34.337705, -118.504802 34.337886, -118.505113 34.338061, -118.50549 34.338208, -118.50577 34.338306, -118.506451 34.338544, -118.50662 34.338603, -118.506795 34.338661, -118.506944 34.338711, -118.507221 34.338802, -118.507541 34.338908, -118.507584 34.338922, -118.507655 34.338946, -118.508014 34.339116, -118.508238 34.339267, -118.50835 34.339342, -118.508671 34.33959, -118.508848 34.339785, -118.508998 34.340005, -118.509112 34.340239, -118.50921 34.340505, -118.50941 34.341288, -118.509444 34.341422, -118.509452 34.341455, -118.50973 34.342602, -118.509865 34.343126, -118.510004 34.343667, -118.510189 34.344455, -118.510262 34.34481, -118.510276 34.34504, -118.51026 34.345371, -118.510211 34.345662, -118.510098 34.346009, -118.509945 34.346321, -118.509799 34.346554, -118.509298 34.34722, -118.509253 34.347281, -118.508975 34.34766, -118.508934 34.347715, -118.508673 34.348174, -118.508438 34.348649, -118.508233 34.349267, -118.508156 34.349578, -118.508118 34.349733, -118.50806 34.350197, -118.50806 34.350747, -118.508099 34.351257, -118.508288 34.352505, -118.508287 34.353582, -118.508201 34.354367, -118.507916 34.356055, -118.507822 34.356613, -118.507796 34.356747, -118.507702 34.357234, -118.50762 34.357593, -118.507411 34.358367, -118.507276 34.358785, -118.507189 34.358874, -118.507008 34.359371, -118.506958 34.359648, -118.506924 34.359985, -118.506928 34.360195, -118.506964 34.360888, -118.506977 34.361142, -118.507002 34.361509, -118.507007 34.361581, -118.507097 34.361847, -118.507132 34.362394, -118.507133 34.362472, -118.507135 34.362584, -118.50714 34.362752, -118.507146 34.362964, -118.507073 34.36319, -118.507053 34.363521, -118.507029 34.363792, -118.506999 34.363962, -118.506927 34.364264, -118.506852 34.364572, -118.506794 34.364785, -118.506782 34.364968, -118.506705 34.365148, -118.506528 34.365481, -118.506325 34.365771, -118.506268 34.365851, -118.506795 34.366019, -118.507274 34.366207, -118.507576 34.366365, -118.507943 34.366558, -118.508006 34.366596, -118.508271 34.366758, -118.508796 34.367078, -118.508906 34.367147, -118.509405 34.367455, -118.509957 34.367795, -118.510023 34.367835, -118.510495 34.368121, -118.510658 34.368226, -118.510686 34.368244, -118.510906 34.368385, -118.511282 34.368627, -118.511373 34.368685, -118.511539 34.368788, -118.512044 34.369102, -118.512099 34.369137, -118.512221 34.369213, -118.512529 34.3694, -118.513119 34.369759, -118.51331 34.369879, -118.514054 34.370345, -118.514073 34.370356, -118.514201 34.370431, -118.514405 34.370557, -118.514744 34.370766, -118.514942 34.370887, -118.515141 34.371011, -118.515721 34.371375, -118.516361 34.371762, -118.516555 34.371885, -118.516796 34.372031, -118.517262 34.372313, -118.517487 34.37245, -118.51772 34.372594, -118.517957 34.372742, -118.518128 34.372848, -118.518184 34.372881, -118.518582 34.373121, -118.518847 34.373239, -118.518881 34.373254, -118.519202 34.373396, -118.519553 34.373552, -118.520225 34.373845, -118.520735 34.374067, -118.521184 34.374259, -118.521522 34.374403, -118.521871 34.374553, -118.521996 34.374607, -118.522891 34.374994, -118.523268 34.375161, -118.523397 34.375218, -118.523683 34.375338, -118.523938 34.375445, -118.524107 34.375515, -118.524534 34.375691, -118.524708 34.375763, -118.525182 34.375979, -118.525717 34.376223, -118.525988 34.376392, -118.526089 34.376472, -118.526295 34.376497, -118.526425 34.376527, -118.52666 34.376626, -118.526869 34.376681, -118.527547 34.376969, -118.52777 34.377063, -118.528044 34.37718, -118.529011 34.377589, -118.529427 34.377766, -118.529979 34.378014, -118.530416 34.37821, -118.530488 34.378241, -118.530966 34.378445, -118.53115 34.378524, -118.531275 34.378579, -118.531646 34.378743, -118.531935 34.378868, -118.532104 34.379027, -118.532158 34.379094, -118.532357 34.379368, -118.532552 34.379644, -118.532768 34.37995, -118.53279 34.379981, -118.532949 34.380186, -118.53316 34.380163, -118.53392 34.380114, -118.534117 34.380105, -118.534723 34.380077, -118.534871 34.38007, -118.535799 34.38001, -118.536569 34.379962, -118.537387 34.379909, -118.537611 34.379894, -118.538054 34.379867, -118.538373 34.379848, -118.538877 34.37982, -118.539027 34.379812, -118.539138 34.379804, -118.539239 34.379797, -118.539475 34.379779, -118.539811 34.379754, -118.540602 34.379708, -118.540775 34.379698, -118.540878 34.379692, -118.541868 34.379625, -118.542443 34.379595, -118.542935 34.37957, -118.543308 34.379549, -118.543654 34.379529, -118.544072 34.379506, -118.54425 34.379495, -118.545853 34.379401, -118.545999 34.379392, -118.546854 34.379343, -118.546937 34.379338, -118.546908 34.378931, -118.546899 34.37881, -118.546863 34.378387, -118.546818 34.377853, -118.546808 34.377745, -118.54672 34.376693, -118.546441 34.376683, -118.545898 34.376701, -118.545604 34.37671, -118.544716 34.376767, -118.543927 34.376816, -118.543348 34.376852, -118.54303 34.376871, -118.542202 34.376923, -118.542159 34.376414, -118.542123 34.376003, -118.542102 34.375756, -118.542072 34.375405, -118.542064 34.375313, -118.542037 34.374991, -118.54202 34.374748, -118.541918 34.373244, -118.541903 34.373018, -118.541882 34.372882, -118.541839 34.372597, -118.541828 34.372474, -118.541793 34.372086, -118.541772 34.371818, -118.541768 34.371779, -118.541737 34.371384, -118.541717 34.371136, -118.541698 34.370912, -118.541683 34.370712, -118.541627 34.37, -118.541586 34.369472, -118.541216 34.36949, -118.540862 34.369516, -118.54053 34.369539, -118.539582 34.369607, -118.539536 34.369252, -118.539514 34.369081, -118.539416 34.368151, -118.539402 34.368015, -118.539376 34.36767, -118.539325 34.366997, -118.539411 34.36638, -118.539512 34.366032, -118.539652 34.365769, -118.539888 34.365403, -118.540355 34.364918, -118.540463 34.36483, -118.540978 34.364411, -118.541279 34.364274, -118.541541 34.364154, -118.542173 34.363957, -118.542619 34.363867, -118.543167 34.36384, -118.543837 34.363827, -118.544191 34.363809, -118.544366 34.363801, -118.544637 34.363749, -118.545047 34.363635, -118.545407 34.363523, -118.54568 34.363438, -118.546353 34.363341, -118.546751 34.363336, -118.54717 34.363379, -118.547452 34.363447, -118.547804 34.36354, -118.548195 34.363684, -118.548368 34.363775, -118.548788 34.363998, -118.549112 34.364163, -118.549475 34.364348, -118.54973 34.364436, -118.550081 34.364558, -118.55067 34.364655, -118.551088 34.364666, -118.551612 34.364639, -118.552072 34.364557, -118.552728 34.364315, -118.552845 34.36423, -118.553457 34.36379, -118.553665 34.36364, -118.554027 34.36338, -118.554169 34.363247, -118.554228 34.363192, -118.554695 34.362754, -118.555039 34.362431, -118.555241 34.362243, -118.555285 34.362201))')

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=12ba2b0bfcb6e04fee7361cb85496569

What SRID should I use? and how do I set it in MySQL? I am not explicitly setting SRID.

When I try to set -

ALTER TABLE geo ADD geom Polygon;

UPDATE geo SET geom = Polygon(geometry)

I get an error,

Error Code: 1367. Illegal non geometric '`geo`' value found during parsing
Improve this question

1 Answer 1

Reset to default
1

The SRS is 4326 -- https://dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html

st_distance_sphere us expecting a radius of 6370986 (or it can be left off). -- https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html

(Answer) The main changes are Noted:

CREATE TABLE `geo` (`id` int, 
                `geometry` text,
                spatialdata GEOMETRY SRID 4326 NOT NULL,   -- Note
  SPATIAL INDEX (spatialdata));

INSERT INTO `geo` (id, spatialdata)

VALUES 

(2, 
    ST_GeomFromText(  -- Note
'POLYGON ((-118.282067 34.067679, -118.281555 34.067458, -118.281467 34.067419, -118.280624 34.067055, -118.279672 34.066689, -118.279376 34.066558, -118.279233 34.066489, -118.279098 34.066409, -118.278879 34.066251, -118.278047 34.065631, -118.276573 34.067943, -118.276505 34.06805, -118.275848 34.069081, -118.275294 34.069984, -118.274929 34.070554, -118.273876 34.072198, -118.27485 34.072519, -118.2753 34.072711, -118.275707 34.072889, -118.276615 34.073286, -118.27752 34.073681, -118.277735 34.073343, -118.277791 34.073256, -118.278056 34.072844, -118.278522 34.072117, -118.278988 34.071389, -118.279463 34.070648, -118.280081 34.070915, -118.280249 34.070772, -118.281158 34.06934, -118.282183 34.06773, -118.282067 34.067679))'
 , 4326) -- Note
 );

select *
from geo
where st_distance_sphere(Point(-118.2842557, 34.0676834),
  `spatialdata`  -- Note
  ) <= 1609;

However, that leads to this error message:

ERROR 3617 (22S03): Latitude -118.282067 is out of range in function
       st_geomfromtext. It must be within [-90.000000, 90.000000].

Note: That error is caused by POINT wanting the numbers to be lng lat. (I did not fix it further to see if the SELECT works.)

Improve this answer
2
  • can you explain what do you mean leaving off geometry? I am attempting to calculate the distance between a Point and Polygon geometries. I am specifying the radius to 1609 meters or 1 mile.
    – kms
    Aug 1 at 5:49
  • I rewrote my Answer, noting the changes I made.
    – Rick James
    Aug 1 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.