1 Background
As the wireless communication technology is developing faster and faster, the Long Term Evolution networks are widely installed all over the word. Since the Long Term Evolution network is based on wide-band radio access technology, there is only one radio carrier available for most of the operator. Since the frequency resource is limited, many telecom operators use same frequency TD-LTE networks. In the same frequency TD-LTE networks, overlapping coverage seriously impact download speed. RF optimization is a conventional method of controlling overlapping coverage. However, the network optimization resources are always limited, we must evaluate the wireless network coverage and quality as a whole, and identify the problems in priority.
Drive test data is widely used to evaluate the wireless network coverage and quality. And we will future associate the Drive test data with the map. Traditionally, we use a desktop software MapInfo to analysis the Drive test data point with the street and road, which is a very heavy labor task. Recently, we find a new approach the PostGIS middleware, we could transfer the city maps into Raster data, we can we could future associate the Drive test data point with the city map raster data , just like we associate the Drive test data point with the polygon object.
In this article, we will describe how to associate the map points to Raster. and my question is how can we improve the speed if the function st_worldtorastercoordx(rast,geom) while there are more than 10000*10000 pixels in the raster object. This question is associated with the middle-ware PostGIS Raster object.
2 Creat Drive-test data point object
2.1 creat point object field
In previous object, we use the kettle middle ware to up-load the Drive-test data file into the Postgres-SQL database. In the database, the destination table is atu_sample, now we will update the table to add a geometry object “point “. Here comes the command:
alter table atu_sample add column geom geometry(point,4326)
2.2 assign value to point
In the Drive-test data file, we have the longtitude and latitude to represent the spatial position, now we will convert them to a geometry object “point “ Here comes the command：
update atu_sample set geom = st_geomfromtext('POINT('||longtitude||' '||latitude||')', 4326);
3 Creat Raster data
In order to associate the point object to Raster data, we must first set up a Raster Data. In this article, we will convert the city map which is a vector data into a Raster data.
3.1 Import Wuhan city Boundary polygon
3.1.1 Creat shape file In this project, we will import the Wuhan city Boundary polygon into the PostGIS database, and Wuhan city is the capital city of Hubei Province, China. It depends on the MAPinfo format transfer function，the GUI entry can be seen in the following diagram： From the picture we can see, the format of the target file is the ESRI Shape file , which is the default format which the PostGIS could support.
3.1.2 Creat PG scripts Then we will transfer the Wuhan city map which is in the ESRI shape file into the PostGIS scripts. It depends on the tool shp2pgsql,which is a plug-in component of PostGIS. Here comes the command :
shp2pgsql –W “GBK” -s 4326 wh_region.shp > 123.txt here -s 4326 represent the coordinate system parameter, which will be valuable in future spatial object。
3.1.3 Script execution Now, we will open the PGADMIN, and transfer the Script into the PostGIS database finally we get a talbe with the name wuhan。When the script is executed, we have the spatial data table in the postGIS。
3.2 Measuring the height and width of Wuhan boundary
3.2.1 Drawing ConvexHull of Wuhan city enter image description here Figure 2-4: ConvexHull of Wuhan city In the figure 2-4, we draw the ConvexHull of Wuhan city, which will surround the Wuhan City boundary.
3.2.2 To measure height and width via ruler使用ruler工具
In the Mapinfo, we have a tool with the name Ruler, which can be used to measure the height and width of the ConvexHull of Wuhan city.
enter image description here
Figure 2-5 to measure height and width
Finally we get the data：132.7KM and 155.5KM.
3.3 Creat the Wuhan Raster data In our project, we will creat the pixel of 100*100 meters. Therefore we will first calculate the parameter to divide the height and width of the ConvexHull of Wuhan city. According to the data which we have measured previously, the height 132.7KM and width 155.5KM, therefore we could calculate the equally divide parameter are 1327 and 1555.
3.3.1 Creat Raster Persistence table Now we will creat a table which could storage the Raster data. Here comes the SQL command： CREATE TABLE myrasters ( rid integer NOT NULL, rast raster, CONSTRAINT myrasters_pkey PRIMARY KEY (rid) );
3.3.2 Insert RASTER record In the previous steps, we have the geomtry object form the table wh_region and the qually divide parameter, we could creat the raster instance with the function ST_AsRaster. Here comes the command： insert into myrasters SELECT gid,ST_AsRaster( geom ,1327, 1555, '8BUI') from wh_region;
4 Associa te the point to Raster 4.1 Add Drive test point RASTER coordinate data field We will find out the relation of the point and Raster, that is to say we should find out which pixel a certain point is located in. in order to record this relation we first add two data field x integer and y integer to the table atu_sample. Here comes the command: alter table atu_sample add column x integer; alter table atu_sample add column y integer;
4.2 Raster mapping
With the function st_worldtorastercoordx(rast,geom) and st_worldtorastercoordy(rast,geom), we could easily find out which pixel a certain point is located in and get the pixel coordinate data. Then we will update the tabe Here comes the command:
update atu_sample set x =st_worldtorastercoordx(rast,geom) from myrasters;
update atu_sample set y =st_worldtorastercoordy(rast,geom) from myrasters;
Geom：
4.3 Performance problem of Raster mapping In the above example , we have 12000 points and a 1327* 1555 raster data . it takes more than 160 Seconds to finish the task. In the future project, we will have more than 1200000 points , how could we fix the Performance problem?
