{"id":44718,"date":"2019-04-21T03:01:50","date_gmt":"2019-04-20T17:01:50","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/ITblog\/?p=44718"},"modified":"2019-04-21T17:29:54","modified_gmt":"2019-04-21T07:29:54","slug":"mysql-polygon-spatial-relations-via-image-map-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/mysql-polygon-spatial-relations-via-image-map-tutorial\/","title":{"rendered":"MySql Polygon Spatial Relations via Image Map Tutorial"},"content":{"rendered":"<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/areapolygonmysql.php\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Polygon Spatial Relations  via Image Map Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/polygon_within.jpg\" title=\"MySql Polygon Spatial Relations  via Image Map Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Polygon Spatial Relations  via Image Map Tutorial<\/p><\/div>\n<p>It&#8217;s &#8220;tomorrow&#8221; as per &#8230;<\/p>\n<blockquote cite='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-polygon-insert-via-image-map-tutorial\/'>\n<p>Continuing the theme of PHP using MySql databases and dealing with Spatial functionality of <a title='MySql Stored Procedures Geometry Tutorial' href='#msspgt'>MySql Stored Procedures Geometry Tutorial<\/a>, today we establish some PHP to add into a MySql database table as POLYGON data type data, the HTML <a target=_blank title='HTML area information from w3schools' href='http:\/\/www.w3schools.com\/tags\/tag_area.asp'>area<\/a> <a target=_blank title='HTML area tag coords attribute information from w3schools' href='https:\/\/www.w3schools.com\/tags\/att_area_coords.asp'>coords<\/a> attribute.  Pourquoi?  You&#8217;ll see more on this tomorrow.<\/p>\n<\/blockquote>\n<p> &#8230; of yesterday&#8217;s <a title='MySql Polygon Insert via Image Map Tutorial' href='#mspiimt'>MySql Polygon Insert via Image Map Tutorial<\/a>, and today, we wanted to show you some really great MySql <a target=_blank title='MySql ... Spatial Relation Functions That Use Object Shapes' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/spatial-relation-functions-object-shapes.html'>Spatial Relation Functions That Use Object Shapes<\/a>.  Think polygons and thinking geometry and thinking GIS you would want SQL functionality we show today as per &#8230;<\/p>\n<ul>\n<li><font size=1>tell whether one polygon<\/font> CROSSES <font size=1>another polygon<\/font><\/li>\n<li><font size=1>tell whether one polygon is<\/font> DISJOINT <font size=1>relative to another polygon<\/font><\/li>\n<li><font size=1>tell whether one polygon<\/font> EQUALS <font size=1>another polygon in a spatial sense<\/font><\/li>\n<li><font size=1>tell whether one polygon<\/font> INTERSECTS <font size=1>another polygon<\/font><\/li>\n<li><font size=1>tell whether one polygon<\/font> OVERLAPS <font size=1>another polygon<\/font><\/li>\n<li><font size=1>tell whether one polygon<\/font> TOUCHES <font size=1>another polygon<\/font><\/li>\n<li><font size=1>tell whether one polygon is<\/font> WITHIN <font size=1>another polygon<\/font><\/li>\n<\/ul>\n<p>Here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made for POLYGON data type INSERT MySql SQL statements and can be renamed to <a target=_blank title='map_area_polygon_mysql.php' href='http:\/\/www.rjmprogramming.com.au\/PHP\/Geographicals\/diff.php?one=http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/map_area_polygon_mysql.php-GETME'>the changed<\/a> <a target=_blank title='map_area_polygon_mysql.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/map_area_polygon_mysql.php-GETME'>map_area_polygon_mysql.php<\/a> for your perusal, and which we use incorporated with our local <a target=_blank title='MAMP' href='https:\/\/mamp.info'>MAMP<\/a> Apache\/PHP\/MySql web server, or via this <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/areapolygonmysql.php\" title=\"Click picture\">live run<\/a> link.<\/p>\n<p>Now with the supervised PHP programming source code which gathers the MySql SQL requests made for POLYGON data type INSERT MySql SQL statements and can be renamed to <a target=_blank title='ourmysqlstoredprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/PHP\/Geographicals\/diff.php?one=http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysqlstoredprocedure.php--GETME'>the changed<\/a> <a target=_blank title='ourmysqlstoredprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysqlstoredprocedure.php--GETME'>ourmysqlstoredprocedure.php<\/a> for your perusal.<\/p>\n<p>You can also see this play out at WordPress 4.1.1&#8217;s <a target=_blank  href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-polygon-insert-via-image-map-tutorial\/'>MySql Polygon Insert via Image Map Tutorial<\/a>.<\/p>\n<hr>\n<p id='mspiimt'>Previous relevant <a target=_blank title='MySql Polygon Insert via Image Map Tutorial' href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-polygon-insert-via-image-map-tutorial\/'>MySql Polygon Insert via Image Map Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/area_polygon_mysql.php\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Polygon Insert via Image Map Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/polygon_insert.jpg\" title=\"MySql Polygon Insert via Image Map Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Polygon Insert via Image Map Tutorial<\/p><\/div>\n<p>Continuing the theme of PHP using MySql databases and dealing with Spatial functionality of <a title='MySql Stored Procedures Geometry Tutorial' href='#msspgt'>MySql Stored Procedures Geometry Tutorial<\/a>, today we establish some PHP to add into a MySql database table as POLYGON data type data, the HTML <a target=_blank title='HTML area information from w3schools' href='http:\/\/www.w3schools.com\/tags\/tag_area.asp'>area<\/a> <a target=_blank title='HTML area tag coords attribute information from w3schools' href='https:\/\/www.w3schools.com\/tags\/att_area_coords.asp'>coords<\/a> attribute.  Pourquoi?  You&#8217;ll see more on this tomorrow.  Let&#8217;s today, just get to insert records, and that looks like, in MySql SQL &#8230;<\/p>\n<p>Table Creation<\/p>\n<div>DROP TABLE IF EXISTS POLYGONS;<br \/>\nCREATE TABLE POLYGONS (<br \/>\n polyid INT NOT NULL AUTO_INCREMENT,<br \/>\n mapid VARCHAR(20) NOT NULL,<br \/>\n onepolygon POLYGON NOT NULL,<br \/>\n PRIMARY KEY (polyid));\n<\/div>\n<p><\/p>\n<p>Insert Statement Examples<\/p>\n<div>INSERT INTO POLYGONS (`mapid`,`onepolygon`) values (&#8216;sh80879&#8217;,ST_POLYGONFROMTEXT(&#8216;POLYGON ((190 653, 193 645, 192 641, 191 635, 193 629, 195 623, 192 622, 194 610, 192 606, 186 607, 183 597, 185 593, 181 590, 178 586, 176 580, 172 576, 181 576, 189 574, 198 570, 204 565, 198 562, 202 559, 202 551, 207 550, 214 553, 219 553, 226 546, 232 544, 239 543, 241 539, 248 539, 255 534, 259 535, 259 545, 259 551, 255 558, 250 567, 246 575, 249 580, 249 590, 250 598, 250 604, 242 604, 234 610, 232 619, 229 627, 221 631, 214 637, 211 646, 204 651, 197 656, 191 655, 190 653))&#8217;));<br \/>\nINSERT INTO POLYGONS (`mapid`,`onepolygon`) values (&#8216;sh80879&#8217;,ST_POLYGONFROMTEXT(&#8216;POLYGON ((190 651, 193 644, 190 638, 193 633, 195 627, 196 622, 191 621, 194 617, 192 611, 193 607, 188 607, 185 606, 184 604, 184 599, 185 594, 181 592, 183 587, 181 585, 176 585, 177 582, 173 580, 172 576, 174 575, 170 569, 164 570, 157 564, 153 556, 149 554, 141 549, 148 564, 152 569, 158 580, 165 590, 167 600, 173 610, 173 619, 175 622, 171 622, 175 632, 180 638, 184 644, 191 652, 190 651))&#8217;));\n<\/div>\n<p> &#8230; featuring MySql&#8217;s <a target=_blank title='MySql ST_POLYGONFROMTEXT function' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-wkt-functions.html'>ST_POLYGONFROMTEXT<\/a> function usage to INSERT POLYGON data into a MySql database table.<\/p>\n<p>Here is some downloadable (so far only) supervisory PHP programming source code which gathers the MySql SQL requests made for POLYGON data type INSERT MySql SQL statements and can be renamed to <a target=_blank title='map_area_polygon_mysql.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/map_area_polygon_mysql.php_GETME'>map_area_polygon_mysql.php<\/a> for your initial perusal, and which we use incorporated with our local <a target=_blank title='MAMP' href='https:\/\/mamp.info'>MAMP<\/a> Apache\/PHP\/MySql web server, or via this <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/area_polygon_mysql.php\" title=\"Click picture\">live run<\/a> link.<\/p>\n<hr>\n<p id='msspgt'>Previous relevant <a target=_blank title='MySql Stored Procedures Geometry Tutorial' href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-stored-procedures-geometry-tutorial\/'>MySql Stored Procedures Geometry Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_storedprocedure.php\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Stored Procedures Geometry Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_geometry.jpg\" title=\"MySql Stored Procedures Geometry Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Stored Procedures Geometry Tutorial<\/p><\/div>\n<p>We&#8217;re back from rjmprogramming.com.au domain thoughts at <a title='MySql Stored Procedures User Grant Tutorial' href='#msspugt'>MySql Stored Procedures User Grant Tutorial<\/a> back to thinking more about where MySql is at these days regarding <a target=_blank title='General Geometry Property Functions' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-general-property-functions.html'>General Geometry Property Functions<\/a>.<\/p>\n<p>This series of functions applicable with MySql SQL <a target=_blank title='Data manipulation language information from Wikipedia ... thanks' href='https:\/\/en.wikipedia.org\/wiki\/Data_manipulation_language'>DML<\/a> or &#8220;Stored Procedures&#8221; (given you have a recent enough version of MySql) may be a more efficient approach for an application you write, perhaps avoiding &#8220;double handling&#8221; &#8230; maybe even &#8220;triple handling&#8221; in the hierarchy&#8230;<\/p>\n<ul>\n<li>MySql called from PHP (server side code) as a SELECT statement query result set &#8230; down to &#8230;<\/li>\n<li>PHP (calling the MySql) could achieve this too &#8230; down to &#8230;<\/li>\n<li>Javascript (at the client side) could, perhaps, achieve this too<\/li>\n<\/ul>\n<p> &#8230; depending on what, geometry wise, you want to achieve.  On our <a target=_blank title='MAMP' href='https:\/\/mamp.info'>MAMP<\/a> Apache\/PHP\/MySql web server incarnation of our MySql series, as you can see with our <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_geometry.jpg\" title=\"Tutorial picture\">tutorial picture<\/a> today using &#8230;<\/p>\n<ul>\n<li>PHP 7 &#8230; and &#8230;<\/li>\n<li>mysqli_connect() via 5.7.23 &#8211; MySQL<\/li>\n<\/ul>\n<p> &#8230; we show &#8230;<\/p>\n<ul>\n<li><a target=_blank title='MySql ST_Envelope' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-general-property-functions.html#function_st-envelope'>ST_Envelope<\/a><br \/>\n<blockquote cite='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-general-property-functions.html#function_st-envelope'><p>\nReturns the minimum bounding rectangle (MBR) for the geometry value\n<\/p><\/blockquote>\n<p> &#8230; via pretty obsessive application of MySql <a target=_blank title='MySql; CONCAT' href='https:\/\/www.w3schools.com\/sql\/func_mysql_concat.asp'>CONCAT<\/a> function (as well as below)\n<\/li>\n<li><a target=_blank title='MySql ST_Length' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-linestring-property-functions.html#function_st-length'>ST_Length<\/a><br \/>\n<blockquote cite='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/gis-linestring-property-functions.html#function_st-length'><p>\nReturns a double-precision number indicating the length of the LineString or MultiLineString value\n<\/p><\/blockquote>\n<\/li>\n<\/ul>\n<p>Generally speaking, we totally recommend finding out more here, because the closer you keep the data to the functionality, the faster will be the applications you write. You&#8217;re here because you have decided on MySql as the data source (database) engine.  Being as you&#8217;ve gone to all that organizational trouble, it would be a shame to not use MySql in a savvy way interfacing to what your data represents, as soon as integrated as possible.<\/p>\n<p>And here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to <a target=_blank title='ourmysql_storeprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/PHP\/Geographicals\/diff.php?one=http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysql_storedprocedure.php--GETME'>the changed<\/a> <a target=_blank title='ourmysql_storeprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysql_storedprocedure.php--GETME'>ourmysql_storedprocedure.php<\/a> as required.<\/p>\n<hr>\n<p id='msspugt'>Previous relevant <a target=_blank title='MySql Stored Procedures User Grant Tutorial' href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-stored-procedures-user-grant-tutorial\/'>MySql Stored Procedures User Grant Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_storedprocedure.php\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Stored Procedures User Grant Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_grant.jpg\" title=\"MySql Stored Procedures User Grant Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Stored Procedures User Grant Tutorial<\/p><\/div>\n<p>Yesterday&#8217;s <a title='MySql Stored Procedures Spatial Tutorial' href='#msspst'>MySql Stored Procedures Spatial Tutorial<\/a> was fully functional for what we wanted to cover on our local <a target=_blank title='MAMP' href='https:\/\/mamp.info'>MAMP<\/a> Apache\/PHP\/MySql web server featuring &#8230;<\/p>\n<ul>\n<li>PHP 7 &#8230; and &#8230;<\/li>\n<li>mysqli_connect() via 5.7.23 &#8211; MySQL<\/li>\n<\/ul>\n<p> &#8230; but to offer any version of it &#8220;live&#8221; on rjmprogramming.com.au domain (which we generally try to do for your &#8220;reality of purpose&#8221; here at this blog) we came across many issues &#8230;<\/p>\n<ul>\n<li>PHP 5 &#8230; and &#8230;<\/li>\n<li>mysqli_connect() (ie. now the same, ditching the old mysql_connect() ideas) via 5.1.70-cll &#8211; MySQL &#8230; as well as &#8230;<\/li>\n<li>security concerns<\/li>\n<\/ul>\n<p>Hence yesterday&#8217;s trepidation, huh?!  But what makes databases like MySql, especially with its brilliant <a target=_blank title='phpMyAdmin' href='https:\/\/www.phpmyadmin.net\/'>phpMyAdmin<\/a> GUI interface, that &#8220;step up&#8221; from other data storage methodologies such as HTTP Cookies or Local Storage or Flat Files is the concept of a &#8220;user&#8221; <font size=1>&#8230; yoo, hoo!<\/font><\/p>\n<p>The concept of a &#8220;user&#8221; with &#8220;databases&#8221; can then team up with concepts such as &#8230;<\/p>\n<ul>\n<li>privileges<\/li>\n<li>role<\/li>\n<\/ul>\n<p> &#8230; in order to add so much more nuance and organization regarding who handles what computing task.  Think blogs, for example, and you can have &#8230;<\/p>\n<ul>\n<li>administration users (you&#8217;ll hear &#8220;superuser&#8221; references) who can achieve all of the functionalities below, and more &#8230; versus &#8230;<\/li>\n<li>users who just read and never intend to write or comment<\/li>\n<li>users who want to write blog postings but need administrator help to delete them<\/li>\n<li>users who want to comment<\/li>\n<\/ul>\n<p> &#8230; and various other &#8220;roles&#8221;.   &#8220;Privileges&#8221; can help the &#8220;database&#8221; decide on what is allowed by a &#8220;user&#8221; to happen on the &#8220;database&#8221;.<\/p>\n<p>Very good and secure &#8220;database&#8221; brands will start very restrictively, maybe on any database starting out with the one administrator &#8220;user&#8221;.   To nuance from that position we need the &#8220;MySql&#8221; SQL GRANT command (for allowing, and REVOKE is for the other way around), and we&#8217;d like to thank and direct you to the excellent <a target=_blank title='MySql GRANT command information, thanks' href='https:\/\/www.liquidweb.com\/kb\/grant-permissions-to-a-mysql-user-on-linux-via-command-line\/'>MySql GRANT advice<\/a> as per &#8230;<\/p>\n<blockquote cite='https:\/\/www.liquidweb.com\/kb\/grant-permissions-to-a-mysql-user-on-linux-via-command-line\/'><p>\nGrant Permissions to MySQL User<br \/>\nThe basic syntax for granting permissions is as follows:<\/p>\n<p>GRANT permission ON database.table TO &#8216;user&#8217;@&#8217;localhost&#8217;;<\/p>\n<p>Here is a short list of commonly used permissions :<\/p>\n<p>ALL \u2013 Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.<br \/>\nCREATE \u2013 Allow a user to create databases and tables.<br \/>\nDELETE \u2013 Allow a user to delete rows from a table.<br \/>\nDROP \u2013 Allow a user to drop databases and tables.<br \/>\nEXECUTE \u2013 Allow a user to execute stored routines.<br \/>\nGRANT OPTION \u2013 Allow a user to grant or remove another user\u2019s privileges.<br \/>\nINSERT \u2013 Allow a user to insert rows from a table.<br \/>\nSELECT \u2013 Allow a user to select data from a database.<br \/>\nSHOW DATABASES- Allow a user to view a list of all databases.<br \/>\nUPDATE \u2013 Allow a user to update rows in a table.\n<\/p><\/blockquote>\n<p>Coming back to our MySql &#8220;Stored Procedure&#8221; thoughts, it&#8217;s that &#8220;EXECUTE&#8221; GRANT above that is needed, and yet on our rjmprogramming.com.au domain web server, that idea was restricted.  Hence, behind the scenes we detect this with our PHP, and that we MySql GRANT all the <a target=_blank title='Data manipulation language information from Wikipedia ... thanks' href='https:\/\/en.wikipedia.org\/wiki\/Data_manipulation_language'>DML<\/a> SQL (the day to day database populating and updating functionalities) of INSERT and UPDATE and SELECT and DELETE, and break the &#8220;Stored Procedures&#8221; up into (less cute) sets of DML SQL to achieve the same ends.  However, we still present &#8220;Stored Procedure&#8221; thoughts because &#8230;<\/p>\n<ul>\n<li>phpMyAdmin (off cPanel (logged in administratively)) will allow you to create the &#8220;Stored Procedures&#8221;<\/li>\n<li>phpMyAdmin (off cPanel (logged in administratively)) will allow you to EXECUTE the copy and pasted &#8220;Stored Procedures&#8221; CALL (too)<\/li>\n<\/ul>\n<p>As you can see here, the idea of a database (MySql) &#8220;user&#8221; can be a friend to your database &#8220;security&#8221; concerns, wouldn&#8217;t you say?<\/p>\n<hr>\n<p id='msspst'>Previous relevant <a target=_blank title='MySql Stored Procedures Spatial Tutorial' href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-stored-procedures-spatial-tutorial\/'>MySql Stored Procedures Spatial Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_storedprocedure.php\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Stored Procedures Spatial Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_sp.jpg\" title=\"MySql Stored Procedures Spatial Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Stored Procedures Spatial Tutorial<\/p><\/div>\n<p>Back when we wrote <a title='MySql Stored Procedures Primer Tutorial' href='#mssppt'>MySql Stored Procedures Primer Tutorial<\/a> we wrote it more or less to use &#8230;<\/p>\n<ul>\n<li>PHP 5 and its &#8230;<\/li>\n<li>mysql_connect()<\/li>\n<\/ul>\n<p> &#8230; MySql database connection logic, but today we have a dual (even <i>tri<\/i>al) purpose in mind (<font size=1>&#8230; or &#8220;out of mind&#8221; if you were naughty and picked some of those mushrooms out and about at the moment (ie. please don&#8217;t)<\/font>) for today&#8217;s revisit.<\/p>\n<ul>\n<li>get with the plan of using PHP <a target=_blank title='PHP 7 new things' href='https:\/\/www.php.net\/manual\/en\/migration70.new-features.php'>7<\/a> (as we do with our local <a target=_blank title='MAMP' href='https:\/\/mamp.info'>MAMP<\/a> Apache\/PHP\/MySql web server here) along with mysqli_connect &#8230; and &#8230;<\/li>\n<li>start you thinking about MySql&#8217;s excellent <a target=_blank title='Spatial Convenience Functions' href='https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/spatial-convenience-functions.html'>Spatial Convenience Functions<\/a>, such as <a target=_blank title='st_distance_sphere' href='https:\/\/postgis.net\/docs\/manual-1.4\/ST_Distance_Sphere.html'>st_distance_sphere<\/a>() function to determine (in metres) crow fly distances between points (longitude, latitude) on Earth &#8230; and &#8230;<\/li>\n<li>verify that MySql Stored Procedures haven&#8217;t changed as far as arrangements go with all this<\/li>\n<\/ul>\n<p>So, here is some downloadable PHP programming source code which shows the results of the MySql SQL requests made and can be renamed to <a target=_blank title='ourmysqlstoreprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/PHP\/Geographicals\/diff.php?one=http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysqlstoredprocedure.php-GETME'>the changed<\/a> <a target=_blank title='ourmysqlstoreprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysqlstoredprocedure.php-GETME'>ourmysqlstoredprocedure.php<\/a> as required.<\/p>\n<p>And here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to <a target=_blank title='ourmysql_storeprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/PHP\/Geographicals\/diff.php?one=http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysql_storedprocedure.php-GETME'>the changed<\/a> <a target=_blank title='ourmysql_storeprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysql_storedprocedure.php-GETME'>ourmysql_storedprocedure.php<\/a> as required.<\/p>\n<p>A tad trepidatiously we also offer you today&#8217;s <a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/mysql_storedprocedure.php\" title=\"Click picture\">live run<\/a> link that we explain more about regarding restrictiveness and its allowances, tomorrow.<\/p>\n<p>You can also see this play out at WordPress 4.1.1&#8217;s <a target=_blank  href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-stored-procedures-primer-tutorial\/'>MySql Stored Procedures Primer Tutorial<\/a>.<\/p>\n<hr>\n<p id='mssppt'>Previous relevant <a target=_blank title='MySql Stored Procedures Primer Tutorial' href='\/\/www.rjmprogramming.com.au\/ITblog\/mysql-stored-procedures-primer-tutorial\/'>MySql Stored Procedures Primer Tutorial<\/a> is shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/MySql_StoredProcedure_Primer.jpg\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"MySql Stored Procedures Primer Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/MySql_StoredProcedure_Primer.jpg\" title=\"MySql Stored Procedures Primer Tutorial\"   \/><\/a><p class=\"wp-caption-text\">MySql Stored Procedures Primer Tutorial<\/p><\/div>\n<p>Today&#8217;s tutorial follows up on <a href='#pitmappt' title='phpMyAdmin interface to MySql and PHP Primer Tutorial'>phpMyAdmin interface to MySql and PHP Primer Tutorial<\/a> in that we again use the brilliant phpMyAdmin to oversee the results of some PHP code which uses MySql calls to create tables called POINT and CIRCLE used to store information defining a circle, and then it creates three MySql stored procedures in the database to help add circle data with the single MySql statement that goes <em>CALL AddCircle(x, y, radius);<\/em> via the use of those stored procedures.   Let&#8217;s see below what <a target=_blank title='Stored Procedures information from Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Stored_procedure'>Wikipedia<\/a> says about Stored Procedures generally.<\/p>\n<blockquote><p>A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.<\/p>\n<p>Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.<\/p>\n<p>Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.[1]<\/p><\/blockquote>\n<p>Here is some downloadable PHP programming source code which shows the results of the MySql SQL requests made and can be renamed to <a target=_blank title='ourmysqlstoreprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysqlstoredprocedure.php_GETME'>ourmysqlstoredprocedure.php<\/a> as required.<\/p>\n<p>Here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to <a target=_blank title='ourmysql_storeprocedure.php' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/ourmysql_storedprocedure.php_GETME'>ourmysql_storedprocedure.php<\/a> as required.<\/p>\n<p>If you want to develop your own live usage (have provided a live usage link <a target=_blank title='live usage or to see the raw MySql SQL involved in this tutorail' href='http:\/\/www.rjmprogramming.com.au\/MySql\/StoredProcedure\/'>here<\/a> which will not work but you can use if you are a beginner, to get used to mysql errors, which will occur after you hit either button of the link (these errors indicate bad MySql connection details which are the same reason the database dropdown is not full of options), or you can use this link to see the raw MySql SQL involved in piecing this tutorial together) of these two PHP source codes then you could fix up the hard codings for MySql host\/username\/password up the top of ourmysql_storeprocedure.php (where you may notice that the default host in the code is localhost:8889 which is the default host string for MySql (ie. <a target=_blank title='port explained by Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Port_%28computer_networking%29'>port<\/a> 8889 is used) when using a <a target=_blank title='PHP and MySql via MAMP Primer Tutorial' href='http:\/\/www.rjmprogramming.com.au\/wordpress\/?p=933'>MAMP<\/a> (Mac laptop) local web server (which uses localhost:8888 as its local &#8220;domain name&#8221; for http usage)) or you can keep the same code and use a URL like:<\/p>\n<p>[your-domain-name-plus-a-bit-maybe]\/ourmysql_storeprocedure.php?host=[your MySql host address]&#038;username=[your MySql username]&#038;password=[your MySql password]&#038;database=[your optional MySql default database name within the looked up list presented]<\/p>\n<hr \/>\n<p id='pitmappt'>Previous <a href='http:\/\/www.rjmprogramming.com.au\/wordpress\/?p=492' title='phpMyAdmin interface to MySql and PHP Primer Tutorial'>phpMyAdmin interface to MySql and PHP Primer Tutorial<\/a> is relevant and shown below.<\/p>\n<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/phpMyAdmin\/iFrame.html\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"phpMyAdmin interface to MySql and PHP Primer Tutorial\" src=\"http:\/\/www.rjmprogramming.com.au\/phpMyAdmin\/phpMyAdmin.jpg\" title=\"phpMyAdmin interface to MySql and PHP Primer Tutorial\"   \/><\/a><p class=\"wp-caption-text\">phpMyAdmin interface to MySql and PHP Primer Tutorial<\/p><\/div>\n<p><strong><\/p>\n<p>Transcript:<\/p>\n<p><\/strong><\/p>\n<p>You never hear much about the data when you hear about great PHP products, but we<br \/>\n   all know it is the data that differentiates the quality of the end result.<\/p>\n<p>That is probably because database products like MySql, SqlServer, Oracle SQL, Advantage and Access<br \/>\n   are pretty good at what they do, and emphasise reliability rather than flashiness.<\/p>\n<p>MySql and PHP have a great open source interface with phpMyAdmin, which is so good,<br \/>\n  you forget that it is not the default MySql administrator&#8217;s interface product.<\/p>\n<p>Let&#8217;s have a look at this WordPress database and a bit of how it looks, looking<br \/>\n  through the prism of  <a target=_blank title='click picture' href='http:\/\/www.rjmprogramming.com.au\/phpMyAdmin\/iFrame.html'>phpMyAdmin<\/a> &#8230;\n<\/p>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d492' onclick='var dv=document.getElementById(\"d492\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"http:\/\/www.rjmprogramming.com.au\/wordpress\/?cat=47\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d492' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr \/>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d3849' onclick='var dv=document.getElementById(\"d3849\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"http:\/\/www.rjmprogramming.com.au\/wordpress\/?cat=47\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d3849' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d44652' onclick='var dv=document.getElementById(\"d44652\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/geometry\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d44652' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d44673' onclick='var dv=document.getElementById(\"d44673\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/security\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d44673' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d44684' onclick='var dv=document.getElementById(\"d44684\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/geographicals\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d44684' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d44709' onclick='var dv=document.getElementById(\"d44709\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/polygon\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d44709' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n<hr>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d44718' onclick='var dv=document.getElementById(\"d44718\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"https:\/\/www.rjmprogramming.com.au\/ITblog\/tag\/function\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d44718' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s &#8220;tomorrow&#8221; as per &#8230; Continuing the theme of PHP using MySql databases and dealing with Spatial functionality of MySql Stored Procedures Geometry Tutorial, today we establish some PHP to add into a MySql database table as POLYGON data type &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/mysql-polygon-spatial-relations-via-image-map-tutorial\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12,37],"tags":[2936,270,1744,299,1576,355,471,481,484,2653,2933,744,827,932,2930,938,962,2935,997,1018,2934,2932,2931,1186,1201,1238,1319,1891],"class_list":["post-44718","post","type-post","status-publish","format-standard","hentry","category-database","category-elearning","category-tutorials","tag-boundary","tag-cpanel","tag-cyber-security","tag-database-2","tag-dml","tag-domain","tag-function","tag-geographicals","tag-geometry","tag-gis","tag-grant","tag-mamp","tag-mysql","tag-php","tag-php-7","tag-phpmyadmin","tag-polygon","tag-privilege","tag-programming","tag-query","tag-revoke","tag-spatial","tag-spatial-convenience-functions","tag-sql","tag-stored-procedure","tag-table","tag-tutorial","tag-user"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/44718"}],"collection":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/comments?post=44718"}],"version-history":[{"count":4,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/44718\/revisions"}],"predecessor-version":[{"id":44753,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/44718\/revisions\/44753"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=44718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=44718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=44718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}