Linear reference and geocoding of Introduction to postgis

Today I learned two useful methods in postgis:

• St_line_interpolate_point

Geometry st_line_interpolate_point (geometry a_linestring, float a_fraction): returns a point in online interpolation.

 

Back Online20%Get a point

• St_line_locate_point

Float st_line_locate_point (geometry a_linestring, geometry a_point): returns the closest point position of a_point to a_linestring (this position indicates a 0-1 floating point number ). We can useSt_line_interpolate_point.

 

 

Returns the closest point of a given point to a line. 

These two methods are very useful for geocoding. I will use the data imported in the previous article for the following experiment:

First, I used the qgis software to visualize the query results (openjump can also query visualization)

1. add layers first

2. After adding the layer, we start to execute our query. Here we use a plug-in tool in qgis.Rt SQL Layer

This plug-in tool needs to be downloaded and installed separately: 1) Plug-in-> fetch Python plugins ..

2) Click fetch Python plugins and findRt SQL layer Installation

3. After installing the plug-in, clickRt SQL LayerConnect to the database Island, click Query Builder, and enter the following query statement in the pop-up form:

SELECT ST_Line_Interpolate_Point(
        ln.the_geom,
        ST_Line_Locate_Point(
            ln.the_geom,
            pt.the_geom
        )
    )
FROM road as ln, features as pt;

 

4. the following result is displayed:

From the figure, we can see that there are many more points on the road, but this result is not what I want, because this result is to pull the features on both sides of the road. Therefore, to limit the number of points that are a certain distance from the road, run the following SQL statement:

SELECT DISTINCT ON (pt.gid)

pt.name AS pt_name,
pt.gid AS pt_id,
ST_Line_Interpolate_Point(
ln.the_geom,
ST_Line_Locate_Point(ln.the_geom, pt.the_geom)
) As snapped_point
FROM
features AS pt INNER JOIN
road AS ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 0.004)
ORDER BY
pt.gid;

This is an ideal result. The st_dwithin method is used here. This method is also very common and will be used when performing some object search.

Note:If postgis is 1.5 +, st_closestpoint is a better method:

Geometry st_closestpoint (geometry G1, geometry G2), which is much simpler. G1 is not limited to lines, but G2 is not limited to points.

You can use st_closestpoint to replace st_line_interpolate (st_line_locate_point... the effect is the same.