I created a postgresql function that helps in reverse resolution of addresses, which is widely used in applications to help customers know the address of the location based only on the latitude and longitude of the point, this function is based on the reverse.php file from the Nominatim project.
the function uses the functions "lookupPoint" and "lookupPolygon" as a base, I haven't applied it to the other functions yet, since I didn't see much need, but if anyone is interested in updating this function, you can comment that I'll be available to help, :)
To create and use the function you need to follow the steps below:
CREATE TYPE public.custom_address AS
(
distance double precision,
pl1_place_id bigint,
pl1_parent_place_id bigint,
lat double precision,
lon double precision,
pl1_country_code character varying(2),
up1 text,
up2 text,
up3 text,
up4 text,
up5 text,
up6 text,
up7 text
);
CREATE OR REPLACE FUNCTION public.get_custom_reverse_address(
lat_param double precision,
lon_param double precision)
RETURNS SETOF custom_address
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
search_point geometry := (select ST_SetSRID(ST_Point(lon_param, lat_param), 4326));
point_custom_address custom_address;
poly_record record;
poly_custom_address custom_address;
p_max_rank SMALLINT := 30;
BEGIN
IF lat_param is not null and lon_param is not null THEN
select * into point_custom_address from (
select
ST_distance(
search_point,
pl1.geometry
) as distance,
pl1.place_id as pl1_place_id,
pl1.parent_place_id as pl1_parent_place_id,
lat_param::double precision as lat,
lon_param::double precision as lon,
pl1.country_code as pl1_country_code,
coalesce(pl1.name->'name', pl1.name->'ref') as up1,
coalesce(pl2.name->'name', pl2.name->'ref') as up2,
coalesce(pl3.name->'name', pl3.name->'ref') as up3,
coalesce(pl4.name->'name', pl4.name->'ref') as up4,
coalesce(pl5.name->'name', pl5.name->'ref') as up5,
coalesce(pl6.name->'name', pl6.name->'ref') as up6,
coalesce(pl7.name->'name', pl7.name->'ref') as up7
FROM placex pl1
left join placex pl2 on pl2.place_id=pl1.parent_place_id
left join placex pl3 on pl3.place_id=pl2.parent_place_id
left join placex pl4 on pl4.place_id=pl3.parent_place_id
left join placex pl5 on pl5.place_id=pl4.parent_place_id
left join placex pl6 on pl6.place_id=pl5.parent_place_id
left join placex pl7 on pl7.place_id=pl6.parent_place_id
WHERE ST_DWithin(
search_point,
pl1.geometry,
0.006
)
AND pl1.rank_address between 26 and 27
and (
pl1.name is not null
or pl1.housenumber is not null
or pl1.rank_address between 26 and 27
)
and (
pl1.rank_address between 26 and 27
or ST_GeometryType(pl1.geometry) != 'ST_LineString'
)
and pl1.class not in ('boundary')
and pl1.indexed_status = 0
and pl1.linked_place_id is null
and (
ST_GeometryType(pl1.geometry) not in ('ST_Polygon', 'ST_MultiPolygon')
OR ST_DWithin(
search_point,
pl1.centroid,
0.006
)
)
ORDER BY distance ASC
limit 1
) rs;
IF FOUND THEN
return query select (point_custom_address).*;
ELSE
p_max_rank = 25;
SELECT * into poly_record from (
select
place_id,
parent_place_id,
rank_address,
rank_search
FROM
(
SELECT
pl1.place_id,
pl1.parent_place_id,
pl1.rank_address,
pl1.rank_search,
pl1.country_code,
pl1.geometry
FROM
placex pl1
WHERE
ST_GeometryType(geometry) IN ('ST_Polygon', 'ST_MultiPolygon')
AND rank_address BETWEEN 5 AND p_max_rank
AND geometry && search_point
AND type != 'postcode'
AND name IS NOT NULL
AND indexed_status = 0
AND linked_place_id IS NULL
ORDER BY
rank_address DESC
LIMIT
50
) AS a
WHERE
ST_CONTAINS(
geometry,
search_point
)
ORDER BY
rank_address DESC
LIMIT
1)
rs;
-- query poly
SELECT
* INTO poly_custom_address
FROM
(
WITH poly_record AS (
SELECT
poly_record.place_id AS p_place_id,
poly_record.rank_address AS p_rank_address,
poly_record.rank_search AS p_rank_search
)
SELECT
ST_distance(search_point, pl1.geometry) AS distance,
pl1.place_id AS pl1_place_id,
pl1.parent_place_id AS pl1_parent_place_id,
lat_param:: DOUBLE PRECISION AS lat,
lon_param:: DOUBLE PRECISION AS lon,
pl1.country_code AS pl1_country_code,
COALESCE(pl1.name -> 'name', pl1.name -> 'ref') AS up1,
COALESCE(pl2.name -> 'name', pl2.name -> 'ref') AS up2,
COALESCE(pl3.name -> 'name', pl3.name -> 'ref') AS up3,
COALESCE(pl4.name -> 'name', pl4.name -> 'ref') AS up4,
COALESCE(pl5.name -> 'name', pl5.name -> 'ref') AS up5,
COALESCE(pl6.name -> 'name', pl6.name -> 'ref') AS up6,
COALESCE(pl7.name -> 'name', pl7.name -> 'ref') AS up7
FROM
placex pl1
LEFT JOIN placex pl2 ON pl2.place_id = pl1.parent_place_id
LEFT JOIN placex pl3 ON pl3.place_id = pl2.parent_place_id
LEFT JOIN placex pl4 ON pl4.place_id = pl3.parent_place_id
LEFT JOIN placex pl5 ON pl5.place_id = pl4.parent_place_id
LEFT JOIN placex pl6 ON pl6.place_id = pl5.parent_place_id
LEFT JOIN placex pl7 ON pl7.place_id = pl6.parent_place_id
WHERE
pl1.place_id = (
SELECT
place_id
FROM
(
SELECT
plx.place_id,
plx.rank_search,
plx.country_code,
plx.geometry,
ST_distance(search_point, plx.geometry) AS distance
FROM
placex plx
JOIN poly_record pol ON 1 = 1
WHERE
plx.osm_type = 'N'
AND plx.rank_search > pol.p_rank_search
AND plx.rank_search <= p_max_rank
AND plx.rank_search < 26
AND plx.rank_address > 0
AND plx.class = 'place'
AND plx.type != 'postcode'
AND plx.name IS NOT NULL
AND plx.indexed_status = 0
AND plx.linked_place_id IS NULL
AND ST_DWithin(
search_point,
plx.geometry,
reverse_place_diameter(pol.p_rank_search)
)
ORDER BY
distance ASC,
plx.rank_address DESC
LIMIT
500
) AS a
JOIN poly_record pol ON 1 = 1
WHERE
ST_CONTAINS(
(
SELECT
geometry
FROM
placex
WHERE
place_id = pol.p_place_id
),
geometry
)
AND distance <= reverse_place_diameter(rank_search)
ORDER BY
distance ASC,
rank_search DESC
LIMIT
1
)
ORDER BY
distance ASC
LIMIT
1
)
rs;
IF FOUND THEN
return query select (poly_custom_address).*;
ELSE
return query WITH poly_record AS (
SELECT
poly_record.place_id AS p_place_id,
poly_record.rank_address AS p_rank_address,
poly_record.rank_search AS p_rank_search
)
SELECT
ST_distance(search_point, pl1.geometry) AS distance,
pl1.place_id AS pl1_place_id,
pl1.parent_place_id AS pl1_parent_place_id,
lat_param:: DOUBLE PRECISION AS lat,
lon_param:: DOUBLE PRECISION AS lon,
pl1.country_code AS pl1_country_code,
COALESCE(pl1.name -> 'name', pl1.name -> 'ref') AS up1,
COALESCE(pl2.name -> 'name', pl2.name -> 'ref') AS up2,
COALESCE(pl3.name -> 'name', pl3.name -> 'ref') AS up3,
COALESCE(pl4.name -> 'name', pl4.name -> 'ref') AS up4,
COALESCE(pl5.name -> 'name', pl5.name -> 'ref') AS up5,
COALESCE(pl6.name -> 'name', pl6.name -> 'ref') AS up6,
COALESCE(pl7.name -> 'name', pl7.name -> 'ref') AS up7
FROM
placex pl1
LEFT JOIN placex pl2 ON pl2.place_id = pl1.parent_place_id
LEFT JOIN placex pl3 ON pl3.place_id = pl2.parent_place_id
LEFT JOIN placex pl4 ON pl4.place_id = pl3.parent_place_id
LEFT JOIN placex pl5 ON pl5.place_id = pl4.parent_place_id
LEFT JOIN placex pl6 ON pl6.place_id = pl5.parent_place_id
LEFT JOIN placex pl7 ON pl7.place_id = pl6.parent_place_id
join poly_record pol on 1=1
WHERE
pl1.place_id = pol.p_place_id;
END IF;
END IF;
ELSE
RAISE EXCEPTION 'required latitude and longitude';
END IF;
END;
$BODY$;
select * from public.zzz_get_custom_address_v2(lat_double,lon_double);
the "up1" to the "up7" can be used in a programming function to type the address on the screen, as in the example below in java:
private static String buildAddress(TreeMap<String, String> address) {
String before = "";
StringBuilder finalAddress = new StringBuilder();
for (int i = 1; i < 8; i++) {
String key = "up" + i;
if (address.containsKey(key)) {
String addressPart = address.get(key);
if (!before.equals(addressPart)) {
finalAddress.append(addressPart);
before = addressPart;
finalAddress.append(", ");
}
}
}
String result = finalAddress.toString().length() > 0
? finalAddress.toString().substring(0, finalAddress.toString().length() - 2)
: "without address";
return result;
}