forked from mbasa/pgGeocoder
-
Notifications
You must be signed in to change notification settings - Fork 0
/
pgReverseGeocoder.sql
218 lines (195 loc) · 6.87 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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
--
-- 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)。
CREATE OR REPLACE FUNCTION mk_geores(
record RECORD,
code integer default 1)
RETURNS geores AS $$
DECLARE
output geores;
BEGIN
output.x := record.lon;
output.y := record.lat;
output.code := code;
output.address := record.address;
output.todofuken := record.todofuken;
output.shikuchoson:= record.shikuchoson;
output.ooaza := record.ooaza;
output.chiban := record.chiban;
RETURN output;
END;
$$ LANGUAGE plpgsql;
--
-- 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;
BEGIN
s_flag := FALSE;
SELECT INTO point st_setsrid(st_makepoint(mLon,mLat),4326);
SELECT INTO o_bdry geom FROM 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 address
WHERE st_intersects(geog,o_bdry.geom::geography) AND st_dwithin(point::geography,geog,mDist)
ORDER BY dist LIMIT 1;
IF FOUND THEN
RETURN mk_geores(record, 1);
ELSE
SELECT INTO record todofuken, shikuchoson, ooaza, NULL::varchar as chiban,
lon, lat,
todofuken||shikuchoson||ooaza AS address,
st_distance(point::geography,geog) AS dist
FROM address_o
WHERE st_intersects(geog,o_bdry.geom::geography)
ORDER BY dist LIMIT 1;
IF FOUND THEN
RETURN mk_geores(record, 2);
ELSE
s_flag := TRUE;
END IF;
END IF;
ELSE
s_flag := TRUE;
END IF;
IF s_flag THEN
SELECT INTO s_bdry geom FROM boundary_s WHERE st_intersects(point,geom);
IF FOUND THEN
SELECT INTO record todofuken, shikuchoson, NULL::varchar as ooaza, NULL::varchar as chiban,
lon, lat,
todofuken||shikuchoson AS address, 0 AS dist
FROM address_s AS a
WHERE st_intersects(a.geog, s_bdry.geom::geography);
IF FOUND THEN
RETURN mk_geores(record, 3);
ELSE
RETURN NULL;
END IF;
ELSE
RETURN NULL;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION reverse_geocoder(
mLon numeric,
mLat numeric,
mDist numeric,
mAddr boolean,
mCat varchar default NULL,
mOwnr varchar default NULL,
mLimit numeric default 1)
RETURNS setof geores AS $$
DECLARE
mAddress varchar;
record RECORD;
temp_rec RECORD;
output geores;
BEGIN
IF mAddr = TRUE THEN
--
-- Address Table Search
--
output := reverse_geocoder(mLon,mLat,mDist);
RETURN NEXT output;
RETURN;
ELSE
--
-- Places Table Search
--
IF mCat IS NOT NULL AND mOwnr IS NOT NULL THEN
FOR record IN
SELECT '' AS todofuken, '' AS shikuchoson,
'' AS ooaza, '' AS chiban,
lon, lat,
name::text AS address,
st_distance(st_setsrid(st_makepoint( mLon,mLat),4326)::geography,geog) AS dist
FROM places
WHERE owner = mOwnr AND category = mCat AND
st_dwithin(st_setsrid(st_makepoint(mLon,mLat),4326)::geography,geog,mDist)
ORDER BY dist LIMIT mLimit LOOP
RETURN NEXT mk_geores( record );
END LOOP;
ELSIF mCat IS NOT NULL AND mOwnr IS NULL THEN
FOR record IN
SELECT '' AS todofuken, '' AS shikuchoson,
'' AS ooaza, '' AS chiban,
lon, lat,
name::text AS address,
st_distance(st_setsrid(st_makepoint( mLon,mLat),4326)::geography,geog) AS dist
FROM places
WHERE category = mCat AND
st_dwithin(st_setsrid(st_makepoint(mLon,mLat),4326)::geography,geog,mDist)
ORDER BY dist LIMIT mLimit LOOP
RETURN NEXT mk_geores( record );
END LOOP;
ELSIF mCat IS NULL AND mOwnr IS NOT NULL THEN
FOR record IN
SELECT '' AS todofuken, '' AS shikuchoson,
'' AS ooaza, '' AS chiban,
lon, lat,
name::text AS address,
st_distance(st_setsrid(st_makepoint( mLon,mLat),4326)::geography,geog) AS dist
FROM places
WHERE owner = mOwnr AND
st_dwithin(st_setsrid(st_makepoint(mLon,mLat),4326)::geography,geog,mDist)
ORDER BY dist LIMIT mLimit LOOP
RETURN NEXT mk_geores( record );
END LOOP;
ELSE
FOR record IN
SELECT '' AS todofuken, '' AS shikuchoson,
'' AS ooaza, '' AS chiban,
lon, lat,
name::text AS address,
st_distance(st_setsrid(st_makepoint( mLon,mLat),4326)::geography,geog) AS dist
FROM places
WHERE st_dwithin(st_setsrid(st_makepoint(mLon,mLat),4326)::geography,geog,mDist)
ORDER BY dist LIMIT mLimit LOOP
RETURN NEXT mk_geores( record );
END LOOP;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;