Find data from table using radius check in geomatry column

2022-01-25 14:02 divakar imported from Stackoverflow

I have table "vehicle_location" and "coordinates" column in table datatype is geomatry

and in my controller i am getting lat and long and radius in request so i want to find vehicle location data using radius

1 answer

answered 2022-01-25 14:30 Patrick Janser
Have a look at the formula explained on Wikipedia: https://en.wikipedia.org/wiki/Great-circle_distance

You'll find someone asking about the same question here: Measuring the distance between two coordinates in PHP

Ideally, it would be good to be able to reduce the calculation of distances to only cars that are not to far from your location. So typically, I would start by an SQL query that only returns the vehicules which have latitude and longitude values in the nearby, according to the given radius.

Then, in a second step, you can calculate all the distances between these cars and your position, with the algorithm (which takes some calculation time) and sort them after.

The ideal thing to do is to try and do the calculation directly in SQL if possible, so that you can sort them and filter them with the radius. If it gets to complicated then do the calculation and sorting in PHP.

See also questions close to this topic

How to upload a video using ACF in WordPress?
I am still at learning phase in WordPress. I cannot figure out in ACF which field type should I choose to upload a video file and which code should I write to show it at the front-end. Please look into the below code. I am using the_field('') but it isn't working. Can anyone help
```
                <video width="954" height="535" controls class="tm-mb-40">

                    <source src="video/wheat-field.mp4" type="video/mp4">   
                      
                    Your browser does not support the video tag.

                </video>
```
delete a table form a database using laravel command
i need to delete a database table using laravel artisan command . not like this command php artisan migrate:rollback --step=5

i need to create like this route or controller code .

Route::get('/clear/database', function () {
```
    Artisan::call('cache:clear');
    return redirect('/');
```
});

. i also try public function dd()
```
{
    Schema::drop('table_name');
}
```
but it not working . gives me error like this SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table table_name)

no foreign key for the table . what should i do ?

thanks in advance!

Creating Sticky Navbar with Drop Down Menu HTML

I am creating a HTML web page which contains a sticky navbar with drop down menu. However, when I created one, the dropdown menu does not works in the sticky navbar and so goes vise versa. below is the screenshot of both the result of the two codes.

*image with dropdown menu but without sticky navbar

*image with sticky navbar but without dropdown menu

below is the code for "image with dropdown menu but without sticky navbar"

    <!DOCTYPE html>
    <html>
    <head>
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-    awesome/4.7.0/css/font-awesome.min.css">
    <style>
    body {margin:0;font-family:Arial}

   .topnav {
    overflow: hidden;
    background-color: #333;
  }

  .topnav a {
    list-style-type: none;
    float: left;
    display: block;
    color: #f2f2f2;
    text-align: center;
    padding: 14px 16px;
    text-decoration: none;
    font-size: 17px;
    position: sticky;
  }



  .active {
    background-color: #04AA6D;
    color: white;
  }

  .topnav .icon {
    display: none;
  }

  .dropdown {
    float: left;
    overflow: hidden;
  }

  .dropdown .dropbtn {
    font-size: 17px;    
    border: none;
    outline: none;
    color: white;
    padding: 14px 16px;
    background-color: inherit;
    font-family: inherit;
    margin: 0;
  }

  .dropdown-content {
    display: none;
    position: absolute;
    background-color: #f9f9f9;
    min-width: 160px;
    box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
    z-index: 1;
  }

  .dropdown-content a {
    float: none;
    color: black;
    padding: 12px 16px;
    text-decoration: none;
    display: block;
    text-align: left;
  }

  .topnav a:hover, .dropdown:hover .dropbtn {
    background-color: #555;
    color: white;
  }

  .dropdown-content a:hover {
    background-color: #ddd;
    color: black;
  }

  .dropdown:hover .dropdown-content {
    display: block;
  }

  @media screen and (max-width: 600px) {
    .topnav a:not(:first-child), .dropdown .dropbtn {
    display: none;
  }
    .topnav a.icon {
      float: right;
      display: block;
    }
  }

  @media screen and (max-width: 600px) {
    .topnav.responsive {position: relative;}
    .topnav.responsive .icon {
      position: absolute;
      right: 0;
      top: 0;
    }
    .topnav.responsive a {
      float: none;
      display: block;
      text-align: left;
    }
    .topnav.responsive .dropdown {float: none;}
    .topnav.responsive .dropdown-content {position: relative;}
    .topnav.responsive .dropdown .dropbtn {
      display: block;
      width: 100%;
      text-align: left;
    }
  }
  </style>
  </head>
  <body>
        <div class="header">
        <h2>Scroll Down</h2>
    <p>Scroll down to see the sticky effect.</p>
  </div>

  <div class="topnav" id="myTopnav">
    <a href="#home" class="active">Home</a>
    <a href="#news">News</a>
    <a href="#contact">Contact</a>
    <div class="dropdown">
      <button class="dropbtn">Dropdown 
        <i class="fa fa-caret-down"></i>
      </button>
      <div class="dropdown-content">
        <a href="#">Link 1</a>
        <a href="#">Link 2</a>
        <a href="#">Link 3</a>
      </div>
    </div> 
    <a href="#about">About</a>
    <a href="javascript:void(0);" style="font-size:15px;" class="icon"     onclick="myFunction()">&#9776;</a>
  </div>

  <div style="padding-left:16px">
    <h2>Responsive Topnav with Dropdown</h2>
    <p>Resize the browser window to see how it works.</p>
    <p>Hover over the dropdown button to open the dropdown menu.</p>
  </div>

  <h3>Sticky Navigation Bar Example</h3>
  <p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p>
  <p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari         requires a -webkit- prefix.</p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>
  <p>Some text to enable scrolling. </p>


  <script>
  function myFunction() {
    var x = document.getElementById("myTopnav");
    if (x.className === "topnav") {
      x.className += " responsive";
    } else {
      x.className = "topnav";
    }
  }
  </script>

  </body>
  </html>

below is the code for "image with sticky navbar but without dropdown menu"

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-    awesome/4.7.0/css/font-awesome.min.css">
<style>


body {
  font-size: 20px;
}
body {margin:0;}

ul {
  list-style-type: none;
  margin: 0;
  padding: 0;
  overflow: hidden;
  background-color: #333;
  position: -webkit-sticky; /* Safari */
  position: sticky;
  top: 0;
}

li {
  float: left;
}

li a {
  display: block;
  color: white;
  text-align: center;
  padding: 16px 20px;
  text-decoration: none;
}

li a:hover {
  background-color: #111;
}
    
/*======================================================================*/

body {
  background-color:white;
}
ul {
  list-style-type: none;
  margin: 0;
  padding: 0;
  overflow: hidden;
  background-color: #38444d;
}

li {
  float: left;
}

li a, .dropbtn {
  display: inline-block;
  color: white;
  text-align: center;
  padding: 14px 16px;
  text-decoration: none;
}

li a:hover, .dropdown:hover .dropbtn {
  background-color: red;
}

li.dropdown {
  display: inline-block;
}

.dropdown-content {
  display: none;
  position: absolute;
  background-color: #f9f9f9;
  min-width: 160px;
  box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
  z-index: 1;
}
    
.dropdown-content a {
  color: black;
  padding: 12px 16px;
  text-decoration: none;
  display: block;
  text-align: left;
}

.dropdown-content a:hover {background-color: #f1f1f1;}

.dropdown:hover .dropdown-content {
  display: block;
}


footer {
  text-align: center;
  padding: 3px;
  background-color: DarkSalmon;
  color: white;
}

</style>
</head>
<body>

<div class="header">
  <h2>Scroll Down</h2>
  <p>Scroll down to see the sticky effect.</p>
</div>

<ul>
  <li><a href="#home">Home</a></li>
    <li><a href="#news">News</a></li>
    <li class="dropdown">
      <a href="javascript:void(1)" class="dropbtn">Dropdown</a>
      <div class="dropdown-content">
        <a href="#">Link 1</a>
        <a href="#">Link 2</a>
        <a href="#">Link 3</a>
      </div>
    </li>
</ul>

<h3>Sticky Navigation Bar Example</h3>
<p>The navbar will <strong>stick</strong> to the top when you reach its scroll position.</p>
<p><strong>Note:</strong> Internet Explorer do not support sticky positioning and Safari requires a -webkit- prefix.</p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>
<p>Some text to enable scrolling. </p>


<footer>
  <p>Author: Hege Refsnes<br>
  <a href="mailto:hege@example.com">hege@example.com</a></p>
</footer>


</body>
</html>

Please i need some help with this as i am new to html and css.

Why does this Sql query shows the possible changes but does not implement it?

So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

how to get weeklytotal and yesterday record in mysql in one table

Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

Table name-dailydata-

Sample data

employee_id	date	total
20	2022-04-25	10
20	2022-04-26	20
20	2022-04-27	20
20	2022-04-28	10
20	2022-04-29	20
20	2022-04-30	30
20	2022-04-31	40
20	2022-05-01	50
40	2022-04-26	20

expected output

employee_id	weekly_total	yesterday_record
20	200	40
40	20	0

mysql query to get weekly data

select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'

Procedure to display the Employees of a specific Department
Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

Name of the argument as Input : DeptNo

Write the code to create the procedure

Schema

I wrote the Query like this but i didnt get expected output
```
CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end
```
Expected output:

Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

Foreach for Categories

i have this data, I want to loop with categories for each question. Right now all I can do is loop by displaying sequentially for each question (as in the image).what I want to do is loop and group them based on categories_id , if the categories_id changes it will be given a new line.

 // 20220507095412
// http://127.0.0.1:8000/user/exams

[
  {
    "id": 16,
    "ujians_id": 11,
    "questions_id": 3,
    "mulai": null,
    "berakhir": null,
    "durasi": 60,
    "isAnswer": 0,
    "created_at": "2022-05-07T02:53:56.000000Z",
    "updated_at": "2022-05-07T02:53:56.000000Z",
    "questions": {
      "id": 3,
      "title": "2",
      "question": "<p>Sarana yang sangat masih dalam menyebabkan oposisi dunia yang sering berdampak pada intervensi asing terhadap Indonesia pada era globalisasi sekarang ini adalah...</p>",
      "img": null,
      "categories_id": 1,
      "created_at": "2022-05-07T02:11:41.000000Z",
      "updated_at": "2022-05-07T02:11:41.000000Z"
    }
  },
  {
    "id": 17,
    "ujians_id": 11,
    "questions_id": 1,
    "mulai": null,
    "berakhir": null,
    "durasi": 60,
    "isAnswer": 0,
    "created_at": "2022-05-07T02:53:56.000000Z",
    "updated_at": "2022-05-07T02:53:56.000000Z",
    "questions": {
      "id": 1,
      "title": "13",
      "question": "<p>Wahidin soedirohoesodo memilik peran penting dalam pergerakan kemerdekaan. Antara lain&hellip;22234232</p>",
      "img": null,
      "categories_id": 1,
      "created_at": "2022-05-02T10:39:13.000000Z",
      "updated_at": "2022-05-03T13:28:24.000000Z"
    }
  }
]

@php $no=1 @endphp
                            @foreach($dataUjian as $row)
                                @if($row->isAnswer == 0)
                                    <button class="btn btn-primary btn-sm" onclick="getUjian({{$row->questions_id}}, {{$row->id}},{{$row->ujians->users_id}})" >{{$no++}}</button>
                                @else
                                    <button class="btn btn-danger btn-sm"  disabled>{{$no++}}</button>
                                    @endif
                            @endforeach

Unit testing with get request

I am trying to create a unit test for one of my api.

In the frontend, I send it this way...

params = {
    participants: JSON.stringify(participants),
    section: JSON.stringify(section),
};
axios.get('/api/list', params)

while in the controller, it receives the params this way...

public function list(Request $request)
{
    $participants = json_decode($request->participants);
    $section = json_decode($request->section);

}

Now, I tried making a unit test out of this. by doing...

$params = [
    'participants' => ['id', 'name', 'rating'],
    'section' => ['id', 'code'],
];

$this->get('/api/list'.http_build_query($params))->assertStatus(200)
// $this->json('/api/list', $params)->assertStatus(200) // -> also tried this one
// $this->getJson('/api/list', $params)->assertStatus(200) // -> also tried this one
// $this->call('GET', '/api/list', $params)->assertStatus(200) // -> also tried this one

But none of them works, it always says TypeError: json_decode(): Argument #1 ($json) must be of type string, array given.

So, the way I built the url and the params must be all wrong,

so my question here is that, what's the correct way of building the url so that it provides a correct url string format and the controller will json_decode the params?

Related questions

Find data from table using radius check in geomatry column

1 answer

See also questions close to this topic