stay LBS Application , It is a common function to analyze and obtain the corresponding city according to the coordinates , such as App It automatically selects the city through mobile phone positioning ; This paper introduces the database built by ourselves , utilize SQL Spatial query for coordinate analysis to obtain the corresponding provinces and cities , Most support spatial data types (Spatial) All databases support , Including but not limited to ：MySQL、SQL Server、Oracle、PostgreSQL etc. ; The development language is not limited to , As long as you can query the database, you can support , use Java、Python、PHP、C# Can be easily realized .
Online preview of provincial and urban boundary data 、 download ：https://xiangyuecn.gitee.io/areacity-jsspider-statsgov/
GitHub Address ：https://github.com/xiangyuecn/AreaCity-JsSpider-StatsGov
Get the address by coordinates ： The interface provided by Baidu map is called “ Address inversion ”, The interface provided by Gaode map is called “ Geographic inverse coding ”, Both open platforms provide front and rear interfaces , Send out http Request can get the data , Please read the platform development document for relevant interface calls , It's easy to use .
Of course, this article will not introduce how to get the complete address of street number , I don't talk about how to call other people's interfaces , It only introduces the acquisition of the names of provinces and cities corresponding to the coordinates , Self built database write SQL Make a spatial query , Complete self realization , Comparing and adjusting the open platform interface will be relatively complicated .
Because there are many changes in districts and counties all over the country every year , Therefore, the provincial and urban boundary data need to be maintained synchronously , Fortunately, the open source library is under continuous long-term maintenance , It is relatively easy to update the new data after it is released . Due to the timely update and maintenance of open source database , So as long as the open source library is not closed , The extraction method introduced in this paper has always been applicable ; It is much better than the data uploaded to the download platform and not updated for ten thousand years .
Query effect display ：
A random coordinate from here ：
The intuitive effect is shown in the figure above , Just click on Baidu map （ or App Coordinates obtained by positioning ） Get a coordinate , Then go to the database and use space to query SQL You can find the city where the coordinates are located .
Download the latest from the open source library ok_geo.csv.7z file （13M size , decompression 130M+）, Download this . After downloading, unzip and get ok_geo.csv, This file contains the latest coordinate boundary vector data of all provinces, cities, districts and counties in China .
notes ： This file contains only three levels （ Provincial city ） The data does not include level 4 （ Town streets ）, If you need Township coordinate boundary data , Yes, please Download this
ok_geo4_*.csvfile （90MB+ Compressed package After export 300M+）.
Download good files ok_geo.csv It's a plain text file , You can write your own script to parse , Then import into the database , Self analysis and processing is more complex , Please refer to the documentation in the open source library ; A format conversion tool is provided in the open source library , This tool supports CSV Data import database , So we download the tools directly when downloading the data , Download this .
In addition to supporting ok_geo.csv Import data outside the database , Export is also supported ：sql、shp、geojson, And coordinate system conversion ; You can also perform custom JavaScript Script , Expand rich functions ; The software is Windows edition , If you need to MacOs of use , You can use virtual machines .
The conversion tool performs the import database operation ：
choice ok_geo.csv file Button , Choose the extracted CSV file ;MySQL, Then fill in the database connection , Include ： port 、 Database name 、 Account and password ; Import database Button , Wait a moment , about 3 About minutes , All the data will be imported into the new table created by today's date in the database . notes ：csv The boundary data in the file is Gaode map by default GCJ-02 Mars coordinate system , If you need another coordinate system , For example, Baidu's BD-09 or GPS Of WGS-84, It can be converted through the coordinate system conversion plug-in in the advanced script , After selecting the corresponding plug-in , Click on the application , Coordinate system conversion will be performed automatically when importing the database .
notes ： This tool limits that only one city and its next level data can be exported at a time , Exporting a small amount of data is easy , So we can operate more times , Import all the required city data into the database ; For example, we need the data of all districts and counties in Shenzhen ： Import all provinces in the country for the first time , Fill in the prefix of city name for the second time Guangdong province, （ End with a space ） Import all cities in Guangdong , Fill in the prefix of city name for the third time Guangdong province, shenzhen （ End with a space ） Import all districts in Shenzhen . If the key is filled in the key input box , This tool does not have these limitations , The open source library will issue keys from time to time for welfare , Click once to export the three-level data of all provinces and cities in the country .
Table structure and spatial fields （MySQL edition , Other databases are similar ）：
CREATE TABLE Areacity_Geo_20220216 (
id int NOT NULL, -- City id
pid int NOT NULL, -- Superior City id
deep int NOT NULL, -- Hierarchy ：0 province 、1 City 、2 District
name varchar(250) NOT NULL, -- The city name ：` shenzhen `
ext_path varchar(255) NOT NULL, -- Complete name of provincial and urban level III ： Guangdong province, shenzhen Luohu district
geo geometry NOT NULL, -- City center coordinates , Spatial data format
--,ST_AsText Turn into WKT After text ：`POINT EMPTY`、`POINT (123.456 34.567)`
polygon geometry NOT NULL -- City boundary range graphics , Spatial data format
--,ST_AsText Turn into WKT After text ：`POLYGON EMPTY`、`POLYGON ((123.456 34.567,...))`、`MULTIPOLYGON (((123.456 34.567,...)),...)`
)
Query of spatial fields , Need to use `ST_AsText()` Method to query the string text (WKT: Well Known Text), Otherwise, binary data will be found
-- MySQL edition ：
SELECT id, name, ST_AsText(geo) AS geo, ST_AsText(polygon) AS polygon FROM Table name
-- SQL Server edition ：
SELECT id, name, geo.STAsText() AS geo, polygon.STAsText() AS polygon FROM Table name
After the provincial and urban boundaries are imported into the database , We can be in Java、Python、PHP、C# And other programs to query the database , adopt SQL Spatial computing function of ST_Intersects To query the boundary range of a coordinate , You can get the corresponding provincial and urban information .
Spatial query SQL sentence
For example, to query the coordinates `lng:113.929976 lat:22.529497` In which city
-- MySQL edition ：
SELECT id,deep,name FROM Table name WHERE ST_Intersects(polygon, ST_GeomFromText('POINT(113.929976 22.529497)',0))=1
-- SQL Server edition ：
SELECT id,deep,name FROM Table name WHERE polygon.STIntersects(geometry::STGeomFromText('POINT(113.929976 22.529497)',0))=1
Query result example （MySQL edition , Other databases are similar ）
The program code connects to the database , Through the above SQL After querying the database data , Get the provincial and urban information , Can pass deep Field to distinguish which data is saved (deep=0)、 City (deep=1)、 District and county (deep=2).
Pass the above three steps , We have completely realized the function of analyzing and obtaining the corresponding city according to the longitude and latitude coordinates .
【END】
This code is used in the monitoring system , Through ip Get the city , And the longitude and latitude coordinates of the city , Finally, it can be used echarts Realize the effect of simulated migration api The official introduction : http://lbsyun.baidu.com/inde ...
This article updates ( To look up ): 19-04-15 New collection 2018 Provincial and urban three-level coordinates and administrative regional boundaries 19-03-22 Collected 2018 City data for 18-11-28 Collected 2017 City data for Data download GitHub ...
First , Take a look at the renderings Online experience address :https://hxkj.vip/demo/echartsMap/, And provide real-time geoJson Data file download echarts Official community link address ( Can be edited Online ):https:/ ...
Map of China . The current number of cities will be displayed after clicking the scatter chart Do not display longitude and latitude coordinates echarts.appMap = function (id, opt) { // example var chart = this.init(doc ...
In general ,GPS Coordinates provided directly (B,L,H) yes 1984 The world geodetic coordinate system (WordGeodetic System 1984 namely WGS-84) Coordinates of , among B For latitude ,L For longitude ,H To be high for the earth is to reach WGS-84 ellipsoid ...
stay Qt in , QComboBox The method window component allows the user to select... From a list , stay web The middle is select label , Drop down options . The linkage between provinces, cities, districts and counties is currentIndexChanged Get current Index, Through this ...
In many applications , It often involves recording the user's province . City . District, county or street information , Generally, we can through linkage Select Or similar interface components , Or use Element Medium el-cascader Display the interface components , and ...
public class Distance{ private static final double EARTH_RADIUS = 6378137; private static double ...
Use Baidu map API, Get the latitude and longitude coordinates The code is simple , But there is no ready-made page to get the longitude and latitude of the map on the Internet . Just want to , Pass a latitude and longitude to the current page , Automatically locate this latitude and longitude . Then you can choose again , Select to return to latitude and longitude . The effect is as follows : Original generation ...
ArcGIS Engine Mutual transformation between lower projection coordinates and longitude and latitude coordinates Projection to latitude and longitude ); pPoint.Project(pSRF.CreateGeographicCoordinateSystem((int)e ...
Learn a kind of knowledge , I like to see how the source code makes the relationship between their classes and method calls , How did it happen . That's how I feel . Now that we're talking about HandlerMapping, We know first HandlerMapping The role of :Hand ...
C# in ,Thread Class has one. IsBackground Properties of .MSDN The explanation for it is : Gets or sets a value , This value indicates whether a thread is a background thread . I feel that such an explanation is tantamount to no explanation . .Net Thread in , It can be divided into backstage ...
Parametric definition : Replace parameters in statements generated by script recording with values from the specified data source . Yes Vuser The benefits of script parameterization : 1. Reduce the size of the script 2. Provides the ability to execute scripts using different script values Parameterization involves two tasks : 1. use ...
public delegate TResult Func<in T, out TResult>( T arg ) http://msdn.microsoft.com/en-us/libra ...
QPixmap originalPixmap = QPixmap::grabWindow(QApplication::desktop()->winId()); QString format = ...
Number Sequence Time Limit: 2000/1000 MS (Java/Others) Memory Limit: 65536/32768 K (Java/Others)Tota ...
Problem description : ArcMap unexpectedly exits during the "Loading Document..." stage on startup. No erro ...
In this paper, struts2 stay eclipse Configuration below , Realize a system with CRUD Function of the library management system . 1 Development environment configuration 1.1 stay Eclipse Middle configuration Struts2 1.1.1 ...
When getting location specific information in the applet , Do not configure... In Tencent map WebServiceAPI White list of domain names in , The domain name configuration is directly configured in the applet background ( That's it https://apis.map.qq.com), must ...
Use the following two methods , Redraw the image by size first , And then lower the quality and upload pictures data #pragma mark Crop photo -(UIImage *)scaleToSize:(UIImage *)image size:(C ...
