How to use MySQL and Java to implement a simple geographical location query function

How to use MySQL and Java to implement a simple geographical location query function

Overview:
The geographical location query function allows users to find nearby locations based on specified longitude and latitude Location or query the latitude and longitude information of a specific location. In this article, we will discuss how to implement a simple geographical location query function using MySQL and Java, and provide specific code examples.

Steps:

1. Create database table:
   First, we need to create a MySQL database table to store geographical location information. The table needs to contain the following fields: id (unique identifier), name (name of place), latitude (latitude), longitude (longitude).
   You can use the following SQL command to create a table:

   CREATE TABLE locations (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(255),
     latitude DOUBLE(10, 6),
     longitude DOUBLE(10, 6)
   );

   Copy after login

2. Insert geographical location data:
   Insert the geographical location data to be queried into the created database table. You can use the following SQL commands to insert data:

   INSERT INTO locations(name, latitude, longitude) VALUES 
   ('北京', 39.9042, 116.4074),
   ('上海', 31.2304, 121.4737),
   ('广州', 23.1291, 113.2644),
   ('深圳', 22.5431, 114.0579),
   ('成都', 30.5728, 104.0668);

   Copy after login

3. Use Java to connect to the MySQL database:
   Create a Java project and use JDBC to connect to the MySQL database. You can use the following code snippet to connect to the database:

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class DatabaseConnection {
     private static final String URL = "jdbc:mysql://localhost:3306/mydatabase";
     private static final String USERNAME = "username";
     private static final String PASSWORD = "password";
   
     public static Connection getConnection() throws SQLException {
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    return connection;
     }
   }

   Copy after login

4. Implement the query function:
   Create a Java class to implement the function of querying nearby locations based on specified longitude and latitude. You can use the following code as a reference:

   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class LocationQuery {
     public static void main(String[] args) {
    try {
      Connection connection = DatabaseConnection.getConnection();
      Statement statement = connection.createStatement();
   
      double latitude = 39.9042; // 用户指定的纬度
      double longitude = 116.4074; // 用户指定的经度
      double distance = 50.0; // 用户指定的查询半径
   
      // 根据用户指定的经纬度和查询半径，计算查询范围内的经纬度边界
      double maxLatitude = latitude + distance / 111.0;
      double minLatitude = latitude - distance / 111.0;
      double maxLongitude = longitude + distance / (111.0 * Math.cos(Math.toRadians(latitude)));
      double minLongitude = longitude - distance / (111.0 * Math.cos(Math.toRadians(latitude)));
   
      String query = "SELECT * FROM locations WHERE latitude BETWEEN " +
          minLatitude + " AND " + maxLatitude + " AND longitude BETWEEN " +
          minLongitude + " AND " + maxLongitude;
   
      ResultSet resultSet = statement.executeQuery(query);
   
      while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        double resultLatitude = resultSet.getDouble("latitude");
        double resultLongitude = resultSet.getDouble("longitude");
   
        System.out.println("ID: " + id + ", Name: " + name + ", Latitude: " +
            resultLatitude + ", Longitude: " + resultLongitude);
      }
   
      resultSet.close();
      statement.close();
      connection.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
     }
   }

   Copy after login

In the above example code, we first calculate the latitude and longitude boundaries within the query range, and then construct a SQL query statement to query the geography within this range. position data and print out the results.

Summary:
This article introduces how to use MySQL and Java to implement a simple geographical location query function. By creating database tables, inserting geographical location data, establishing MySQL connections, and using Java to implement query functions, we can query nearby locations based on specified longitude and latitude. Hope the above content is helpful to you!

The above is the detailed content of How to use MySQL and Java to implement a simple geographical location query function. For more information, please follow other related articles on the PHP Chinese website!