-
Notifications
You must be signed in to change notification settings - Fork 5
/
pgReverseGeocoder.sql
185 lines (168 loc) · 6.37 KB
/
pgReverseGeocoder.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
--
-- pgReverseGeocoder.ja : Japanese Reverse Geocoder for PostgreSQL
-- Copyright (C) 2018 Mario Basa
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2
-- of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
-- このプログラムはフリーソフトウェアです。あなたはこれを、フリーソフトウェ
-- ア財団によって発行された GNU 一般公衆利用許諾契約書(バージョン2か、希
-- 望によってはそれ以降のバージョンのうちどれか)の定める条件の下で再頒布
-- または改変することができます。
--
-- このプログラムは有用であることを願って頒布されますが、*全くの無保証*
-- です。商業可能性の保証や特定の目的への適合性は、言外に示されたものも含
-- め全く存在しません。詳しくはGNU 一般公衆利用許諾契約書をご覧ください。
--
-- あなたはこのプログラムと共に、GNU 一般公衆利用許諾契約書の複製物を一部
-- 受け取ったはずです。もし受け取っていなければ、フリーソフトウェア財団ま
-- で請求してください(宛先は the Free Software Foundation, Inc., 59
-- Temple Place, Suite 330, Boston, MA 02111-1307 USA)。
-- DROP TYPE geores CASCADE;
CREATE TYPE geores AS (
code integer,
x double precision,
y double precision,
address character varying,
todofuken character varying,
shikuchoson character varying,
ooaza character varying,
chiban character varying,
go character varying
);
--
-- NOTE: The Address Table must have a column named "geog"
-- of type Geography
--
CREATE OR REPLACE FUNCTION reverse_geocoder(
mLon numeric,
mLat numeric,
mDist numeric default 50)
RETURNS geores AS $$
DECLARE
point geometry;
o_bdry RECORD;
record RECORD;
output geores;
s_flag boolean;
s_bdry RECORD;
matching_nomatch integer;
matching_todofuken integer;
matching_shikuchoson integer;
matching_ooaza integer;
matching_chiban integer;
matching_pinpnt integer;
BEGIN
s_flag := FALSE;
matching_nomatch := 0;
matching_todofuken := 1;
matching_shikuchoson := 2;
matching_ooaza := 3;
matching_chiban := 4;
matching_pinpnt := 5;
output.code := matching_nomatch;
output.x := -999;
output.y := -999;
output.address := 'なし';
SELECT INTO point st_setsrid(st_makepoint(mLon,mLat),4326);
--
-- Searching ABR data for Pinpoint search. Logic might
-- change, depending on the ABR dataset.
--
SELECT INTO record todofuken, shikuchoson, ooaza, chiban, go,
lon, lat,
todofuken||shikuchoson||ooaza||chiban||'-'||go AS address
FROM pggeocoder.address_g
WHERE st_dwithin(point, geog,mDist)
ORDER BY st_distance(point,geog) LIMIT 1;
IF FOUND THEN
output.code := matching_pinpnt;
output.x := record.lon;
output.y := record.lat;
output.address := record.address;
output.todofuken := record.todofuken;
output.shikuchoson:= record.shikuchoson;
output.ooaza := record.ooaza;
output.chiban := record.chiban;
output.go := record.go;
RETURN output;
END IF;
SELECT INTO o_bdry geom FROM pggeocoder.boundary_o WHERE st_intersects(point,geom);
IF FOUND THEN
SELECT INTO record todofuken, shikuchoson, ooaza, chiban,
lon, lat,
todofuken||shikuchoson||ooaza||chiban AS address,
st_distance(point::geography,geog) AS dist
FROM pggeocoder.address_c
WHERE st_intersects(geog,o_bdry.geom::geography) AND st_dwithin(point::geography,geog,mDist)
ORDER BY dist LIMIT 1;
IF FOUND THEN
output.code := matching_chiban;
output.x := record.lon;
output.y := record.lat;
output.address := record.address;
output.todofuken := record.todofuken;
output.shikuchoson:= record.shikuchoson;
output.ooaza := record.ooaza;
output.chiban := record.chiban;
RETURN output;
ELSE
SELECT INTO record todofuken, shikuchoson, ooaza, NULL as chiban,
lon, lat,
todofuken||shikuchoson||ooaza AS address,
st_distance(point::geography,geog) AS dist
FROM pggeocoder.address_o
WHERE st_intersects(geog,o_bdry.geom::geography)
ORDER BY dist LIMIT 1;
IF FOUND THEN
output.code := matching_ooaza;
output.x := record.lon;
output.y := record.lat;
output.address := record.address;
output.todofuken := record.todofuken;
output.shikuchoson:= record.shikuchoson;
output.ooaza := record.ooaza;
output.chiban := record.chiban;
RETURN output;
ELSE
s_flag := TRUE;
END IF;
END IF;
ELSE
s_flag := TRUE;
END IF;
IF s_flag THEN
SELECT INTO s_bdry geom FROM pggeocoder.boundary_s WHERE st_intersects(point,geom);
IF FOUND THEN
SELECT INTO record todofuken, shikuchoson, NULL as ooaza, NULL as chiban,
lon, lat,
todofuken||shikuchoson AS address, 0 AS dist
FROM pggeocoder.address_s AS a
WHERE st_intersects(a.geog, s_bdry.geom::geography);
IF FOUND THEN
output.code := matching_shikuchoson;
output.x := record.lon;
output.y := record.lat;
output.address := record.address;
output.todofuken := record.todofuken;
output.shikuchoson:= record.shikuchoson;
output.ooaza := record.ooaza;
output.chiban := record.chiban;
RETURN output;
END IF;
END IF;
END IF;
RETURN output;
END;
$$ LANGUAGE plpgsql;