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 .

Step one 、 Download provincial and urban boundary data

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_*.csv file （90MB+ Compressed package After export 300M+）.

Step two 、 analysis CSV File import database

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 ：

1. Click on choice ok_geo.csv file Button , Choose the extracted CSV file ;
2. Select the database type to import in database settings , This is MySQL, Then fill in the database connection , Include ： port 、 Database name 、 Account and password ;
3. Click on 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 

Step three 、 In the program, the city is obtained according to the coordinate analysis

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】