Shulou(Shulou.com)06/02 Report--

The main content of this article is to explain "what is the recent location query statement of SQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the most recent location query statement in SQL"?

Preface

I've wasted too much time looking for location software, so it's worth writing down how to do it. Of course, calculating the distance on the earth's surface means calculating the distance of a large circle, which can be calculated by the semi-positive vector formula, also known as the spherical cosine law formula. The question is:

Give a location table with longitude and latitude, which position is closest to the given location?

Location data sheet

Do you want to ask where I can find a location table with latitude and longitude? You can search for "free zip code download" or "free zip code download" on the Internet. Then load it into the MySQL table. There are many different types of geographic data that can be downloaded, with latitude and longitude locations.

This is a US ZIP code packet for SQL Server data, if you just need it.

The logic in this article applies to MySQL,MariaDB, PostgreSQL, and Microsoft SQL Server. Oracle works a little differently; here is an article on how to implement it in Oracle.

Please use postcode data as a method of location determination carefully. Postal codes are only designed to help optimize postal delivery. The use of their data is limited and may lead to wrong results. For example, this is an article written by a geographer about the water crisis in Flint, Michigan. For a long time, children in Flint seemed to be free from lead poisoning because researchers only looked at their zip codes to find out where they lived. But they have lead poisoning. Don't make the same mistake as the Michigan government.

Annoying but necessary geography

Latitude and longitude are expressed in degrees. Latitude describes the distance of a point north or south of the equator. The latitude of the point on the equator is zero. The positive (northern) latitude of the North Pole is 90 degrees, and the South Pole is negative (south) latitude-90 degrees. Accordingly, the position of the northern hemisphere has positive latitudes and the position of the southern hemisphere has negative latitudes.

Longitude describes the distance of a point eastward from the prime meridian: any straight line on the surface of the earth from one point to another. The longitude of the Empire State Building in New York City is negative (west), specifically-73.9857. The longitude of the Taj Mahal in Agra, India is positive (east longitude), specifically 78.0422. The Greenwich Observatory near London, England, is defined as zero longitude.

Therefore, latitude is the value in the range [- 90pcr 90]. Longitude is a value in the range (- 180180). These values are sometimes expressed in degrees, minutes, and seconds, rather than degrees and decimals. If you are going to do the calculation, convert the minutes and seconds into decimals first.

In Napoleon's time, rice was first defined, so it was 10 million meters from the equator to the poles. The original number of meters at latitude is 100,000,000 pounds 90 or 111.111 kilometers. But the earth is a little bulging, so 111.045 km / degree is considered a better approximation.

Here we assume that the earth is a sphere for the convenience of calculation. Even though it's not true. It's a little protruding at the equator, but the positioning problem, we assume that the sphere is enough.

This formula (111.045 km / degree) works well when you move north or south. If you are changing your latitude instead of longitude. If you are moving east or west, changing your longitude, at the equator, it can also work. But on the north and south side of the equator, the longitude line is getting closer and closer, so if you move one scale to the west or east, you will move less than 111.045 kilometers. When you go east or west for one degree, the distance you actually move is actually mileage.

111.045 * cos (latitude)

We use miles in some British colonies. Nautical miles refer to one minute of latitude (1x60 degrees). So there are 69 legal miles per degree or 60 nautical miles per degree. If you are dealing with applications such as GPS controlling the Bull team, you may find it helpful to know that there are 552 waves (unit of length, equivalent to 220yds, 201m or ⅛ miles) per degree. Some U.S.-centric applications disrupt longitude. For the position of the western hemisphere, they are positive rather than negative. If you're debugging something, pay attention to this.

Great circle distance formula

What is the distance between any two points along the surface of the earth? Expressed in degrees, expressed in their latitude and longitude? This is determined by the spherical cosine theorem, or the semi-positive vector formula. This is in MySQL syntax:

DEGREES (ACOS (COS (RADIANS (lat1)) * COS (RADIANS (lat2)) * COS (RADIANS (long1)-RADIANS (long2)) + SIN (RADIANS (lat1)) * SIN (RADIANS (lat2)

It is the distance from the surface of the earth. It also applies when these places are your apartment and local supermarket, or the airport in Sydney, Australia and Reykjavik, Iceland. Note that this result is in degrees. This means that if we want the distance in kilometers, we have to multiply it by 111.045, that is, the value per kilometer.

Note that MS SQL Server needs to use a float or double to represent RADIANS. RADIANS (30) returns a problematic value, but RADIANS (30) works properly. In general, MS SQL Server does not reliably force values of the entire integer type to be converted to float or double types, so be careful not to use the integer type when you need to use float. In addition, keep in mind that although the United States ZIP code looks like a number, it is actually a string. The zip code of the place where I live is' 01950, which is different from 1950.

Query the nearest location

In order to find the nearest point to a given point in the database, we can write the query like this. Let's use a point with longitude of-70.81 and latitude of 42.81. This MySQL query finds the 15 closest points to a given point in order of distance. You can test it here: http://sqlfiddle.com/#!9/21e06/1

SELECT zip, primary_city, latitude, longitude, 111.045 * DEGREES (ACOS (COS (RADIANS (latpoint)) * COS (RADIANS (latitude)) * COS (RADIANS (longpoint)-RADIANS (longitude)) + SIN (RADIANS (latpoint)) * SIN (RADIANS (latitude)) AS distance_in_km FROM zip JOIN (SELECT 42.81 AS latpoint -70.81 AS longpoint) AS p ON 1 / 1 ORDER BY distance_in_km LIMIT 15

Note the use of joins to put latpoint and longpoint into the query. It is convenient to write queries this way because latpoint and longpoint are referenced multiple times in the formula. (MySQL does not need to use ON 1, but PostgreSQL does) (in SQL Server, use SELECT TOP (15) zip... To replace LIMIT 15.)

Very good. We did it, didn't we? Take it easy! Although the query is correct, it is very slow.

Optimize

The query is slow because it must calculate a semi-positive vector formula for every possible pair of points. Therefore, it makes your MySQL server do a lot of math and forces it to scan the entire location table. How to optimize? It would be great if we could use indexes on the latitude and longitude columns of the table. To do this, we introduce a constraint. Suppose we only care about points within a distance of 50 kilometers (latpoint,longpoint) in the zip code table. Let's find out how to use indexing to eliminate further points.

Remember, according to the background information earlier in this article, the latitude is 111.045 kilometers. So, if there is an index on the latitude column, we can use a SQL clause like this to eliminate points that are too north or too south, which may not be within 50 kilometers.

Latitude BETWEEN latpoint-(50.0 / 111.045) AND latpoint + (50.0 / 111.045)

This WHERE statement allows MySQL to omit many latitudes using the index before calculating the semi-positive distance formula. It allows MySQL to perform range scans on latitude indexes.

Finally, we can use a similar but more complex SQL clause to eliminate dots that are too east or too west. This clause is more complicated because the farther the longitude is from the equator we move, the smaller the distance. Please look at the following formula:

Longitude BETWEEN longpoint-(111.045 * COS (RADIANS (latpoint) AND longpoint + (111.045 * COS (RADIANS (latpoint)

So, putting all this together, this query will find the 15 easternmost points within a range of 50 kilometers (latpoint,longpoint).

Although this query is a bit complex, it takes advantage of latitude and longitude indexes and is very efficient.

Note that we have added this subquery as part of the overall query.

SELECT 42.81 AS latpoint,-70.81 AS longpoint, 50.0 AS radius, 111.045 AS distance_unit

The goal is to make it easier for the application to provide the parameters needed for the query. Latpoint and Longpoint are specific locations where you need a nearby location. Radius specifies how far the search should go. Finally, if you want to express distance in kilometers, the unit of distance should be 111.045. If you want to express distance in miles, it should be 69.0.

Limit diagonal distance

However, it is possible for this boundary query to return points where the latpoint,longpoint diagonal exceeds the 50km: it only checks a boundary rectangle, not the diagonal distance. Let's enhance the query to eliminate dots over 50 kilometers.

Use miles instead of kilometers

Finally, many people need to calculate their distance in miles rather than kilometers. There's nothing to it. Simply change the value of the distance unit to 69.0.

This is a typical store finder or location finder query based on latitude and longitude. Should be able to adapt to your use, without too much trouble.

Adapt this query to other location table definitions

Of course, this query is written with a specific ZIP table definition (a United States Postal Code table). The zip table contains fields named zip, primary_city, latitude, and longitude. Notice that the table is referenced by FROM zip AS z in the query. So its alias is z.

Your location table probably has different columns. It should be easy to rewrite this query to suit your query. Look for fields called z.something in the query and replace them with the field names in the table. For example, if your table is named shop and has shopname, shoplat, and shoplong fields, you replace z.shopname with z.primary_city, and so on. You will reference the table by including FROM SHOP as z in the query.

At this point, I believe you have a deeper understanding of "what is the most recent location query statement in SQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.